Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / partition1.out
blob: d1f7ddd0a08c5b806955efc1e5ee194fc2e1db9f [file] [log] [blame]
drop table if exists d;
drop table if exists c;
drop table if exists b;
drop table if exists a;
--ERROR: Missing boundary specification in partition 'aa' of type LIST
create table fff (a char(1), b char(2), d char(3)) distributed by
(a) partition by list (b) (partition aa );
ERROR: missing boundary specification in partition "aa" of type LIST
LINE 2: (a) partition by list (b) (partition aa );
^
-- ERROR: Invalid use of RANGE boundary specification in partition
-- number 1 of type LIST
create table fff (a char(1), b char(2), d char(3)) distributed by (a)
partition by list (b) (start ('a') );
ERROR: invalid boundary specification for LIST partition
LINE 2: partition by list (b) (start ('a') );
^
-- should work
create table fff (a char(1), b char(2), d char(3)) distributed by (a)
partition by list (b) (partition aa values ('2'));
drop table fff cascade;
-- this is subtly wrong -- it defines 4 partitions
-- the problem is the comma before "end", which causes us to
-- generate 2 anonymous partitions.
-- This is an error:
-- ERROR: invalid use of mixed named and unnamed RANGE boundary specifications
create table ggg (a char(1), b int, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2007'), end ('2008'),
partition bb start ('2008'), end ('2009')
);
ERROR: cannot derive ending value of partition based upon starting of next partition
LINE 5: partition aa start ('2007'), end ('2008'),
^
create table ggg (a char(1), b int)
distributed by (a)
partition by range(b)
(
partition aa start ('2007'), end ('2008')
);
ERROR: cannot derive ending value of partition based upon starting of next partition
LINE 5: partition aa start ('2007'), end ('2008')
^
drop table ggg cascade;
ERROR: table "ggg" does not exist
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2007-01-01') end (date '2008-01-01'),
partition bb start (date '2008-01-01') end (date '2009-01-01')
);
drop table ggg cascade;
-- Expressions are allowed
create table ggg (a char(1), b numeric, d numeric)
distributed by (a)
partition by range (b)
(
partition aa start (2007) end (2007+1),
partition bb start (2008) end (2009)
);
drop table ggg cascade;
-- Even volatile expressions are OK. They are evaluted immediately.
create table ggg (a char(1), b numeric, d numeric)
distributed by (a)
partition by range (b)
(
partition aa start (2007) end (2008+(random()*9)::integer),
partition bb start (2018) end (2019)
);
drop table ggg cascade;
-- too many columns for RANGE partition
create table ggg (a char(1), b numeric, d numeric)
distributed by (a)
partition by range (b,d)
(
partition aa start (2007,1) end (2008,2),
partition bb start (2008,2) end (2009,3)
);
ERROR: too many columns for RANGE partition -- only one column is allowed
drop table ggg cascade;
ERROR: table "ggg" does not exist
-- Mismatch between number of columns in PARTITION BY and in the START/END clauses
create table pby_mismatch (a char(1), b numeric, d numeric)
distributed by (a)
partition by range (b)
(
partition aa start (2007,1) end (2008),
partition bb start (2008,2) end (2009)
);
ERROR: number of START values should cover all partition key columns
LINE 5: partition aa start (2007,1) end (2008),
^
create table pby_mismatch (a char(1), b numeric, d numeric)
distributed by (a)
partition by range (b)
(
partition aa start (2007) end (2008,1),
partition bb start (2008) end (2009,1)
);
ERROR: number of END values should cover all partition key columns
LINE 5: partition aa start (2007) end (2008,1),
^
-- basic list partition
create table ggg (a char(1), b char(2), d char(3))
distributed by (a)
partition by LIST (b)
(
partition aa values ('a', 'b', 'c', 'd'),
partition bb values ('e', 'f', 'g')
);
insert into ggg values ('x', 'a');
insert into ggg values ('x', 'b');
insert into ggg values ('x', 'c');
insert into ggg values ('x', 'd');
insert into ggg values ('x', 'e');
insert into ggg values ('x', 'f');
insert into ggg values ('x', 'g');
insert into ggg values ('x', 'a');
insert into ggg values ('x', 'b');
insert into ggg values ('x', 'c');
insert into ggg values ('x', 'd');
insert into ggg values ('x', 'e');
insert into ggg values ('x', 'f');
insert into ggg values ('x', 'g');
select * from ggg order by 1, 2;
a | b | d
---+----+---
x | a |
x | a |
x | b |
x | b |
x | c |
x | c |
x | d |
x | d |
x | e |
x | e |
x | f |
x | f |
x | g |
x | g |
(14 rows)
-- ok
select * from ggg_1_prt_aa order by 1, 2;
a | b | d
---+----+---
x | a |
x | a |
x | b |
x | b |
x | c |
x | c |
x | d |
x | d |
(8 rows)
select * from ggg_1_prt_bb order by 1, 2;
a | b | d
---+----+---
x | e |
x | e |
x | f |
x | f |
x | g |
x | g |
(6 rows)
drop table ggg cascade;
-- documentation example - partition by list and range
CREATE TABLE rank (id int, rank int, year date, gender
char(1)) DISTRIBUTED BY (id, gender, year)
partition by list (gender)
subpartition by range (year)
subpartition template (
start (date '2001-01-01'),
start (date '2002-01-01'),
start (date '2003-01-01'),
start (date '2004-01-01'),
start (date '2005-01-01')
)
(
partition boys values ('M'),
partition girls values ('F')
);
insert into rank values (1, 1, date '2001-01-15', 'M');
insert into rank values (2, 1, date '2002-02-15', 'M');
insert into rank values (3, 1, date '2003-03-15', 'M');
insert into rank values (4, 1, date '2004-04-15', 'M');
insert into rank values (5, 1, date '2005-05-15', 'M');
insert into rank values (6, 1, date '2001-01-15', 'F');
insert into rank values (7, 1, date '2002-02-15', 'F');
insert into rank values (8, 1, date '2003-03-15', 'F');
insert into rank values (9, 1, date '2004-04-15', 'F');
insert into rank values (10, 1, date '2005-05-15', 'F');
select * from rank order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
1 | 1 | 01-15-2001 | M
2 | 1 | 02-15-2002 | M
3 | 1 | 03-15-2003 | M
4 | 1 | 04-15-2004 | M
5 | 1 | 05-15-2005 | M
6 | 1 | 01-15-2001 | F
7 | 1 | 02-15-2002 | F
8 | 1 | 03-15-2003 | F
9 | 1 | 04-15-2004 | F
10 | 1 | 05-15-2005 | F
(10 rows)
select * from rank_1_prt_boys order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
1 | 1 | 01-15-2001 | M
2 | 1 | 02-15-2002 | M
3 | 1 | 03-15-2003 | M
4 | 1 | 04-15-2004 | M
5 | 1 | 05-15-2005 | M
(5 rows)
select * from rank_1_prt_girls order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
6 | 1 | 01-15-2001 | F
7 | 1 | 02-15-2002 | F
8 | 1 | 03-15-2003 | F
9 | 1 | 04-15-2004 | F
10 | 1 | 05-15-2005 | F
(5 rows)
select * from rank_1_prt_girls_2_prt_1 order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
6 | 1 | 01-15-2001 | F
(1 row)
select * from rank_1_prt_girls_2_prt_2 order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
7 | 1 | 02-15-2002 | F
(1 row)
drop table rank cascade;
-- range list combo
create table ggg (a char(1), b date, d char(3), e numeric)
distributed by (a)
partition by range (b)
subpartition by list(d)
(
partition aa
start (date '2007-01-01')
end (date '2008-01-01')
(subpartition dd values ('1', '2', '3'),
subpartition ee values ('4', '5', '6')),
partition bb
start (date '2008-01-01')
end (date '2009-01-01')
(subpartition dd values ('1', '2', '3'),
subpartition ee values ('4', '5', '6'))
);
drop table ggg cascade;
-- demo ends here
-- LIST validation
-- duplicate partition name
CREATE TABLE rank (id int, rank int, year date, gender
char(1)) DISTRIBUTED BY (id, gender, year)
partition by list (gender)
(
partition boys values ('M'),
partition girls values ('a'),
partition girls values ('b'),
partition girls values ('c'),
partition girls values ('d'),
partition girls values ('e'),
partition bob values ('M')
);
ERROR: relation "rank_1_prt_girls" already exists
-- duplicate values
CREATE TYPE rank_partkey AS (rank int, gender char(1));
CREATE TABLE rank (id int, rankgender rank_partkey, year date)
DISTRIBUTED BY (id, year)
partition by list (rankgender)
(
values (CAST ('(1,M)' AS rank_partkey)),
values (CAST ('(2,M)' AS rank_partkey)),
values (CAST ('(3,M)' AS rank_partkey)),
values (CAST ('(1,F)' AS rank_partkey)),
partition ff values (CAST ('(4,M)' AS rank_partkey)),
partition bb values (CAST ('(1,M)' AS rank_partkey))
);
ERROR: partition "rank_1_prt_bb" would overlap partition "rank_1_prt_1"
LINE 10: partition bb values (CAST ('(1,M)' AS rank_partkey))
^
-- RANGE validation
-- legal if end of aa not inclusive
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2007-08-01') end (date '2008-01-01'),
partition bb start (date '2008-01-01') end (date '2008-03-01')
every (interval '10 days'));
drop table ggg cascade;
-- bad - legal if end of aa not inclusive
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive,
partition bb start (date '2008-01-01') end (date '2009-01-01')
every (interval '10 days'));
ERROR: partition "ggg_1_prt_bb_1" would overlap partition "ggg_1_prt_aa"
drop table ggg cascade;
ERROR: table "ggg" does not exist
-- legal because start of bb not inclusive
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2007-08-01') end (date '2008-01-01') inclusive,
partition bb start (date '2008-01-01') exclusive end (date '2008-03-01')
every (interval '10 days'));
drop table ggg cascade;
-- legal if end of aa not inclusive
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition bb start (date '2008-01-01') end (date '2009-01-01'),
partition aa start (date '2007-01-01') end (date '2008-01-01')
);
drop table ggg cascade;
-- bad - legal if end of aa not inclusive
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition bb start (date '2008-01-01') end (date '2009-01-01'),
partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive
);
ERROR: partition "ggg_1_prt_aa" would overlap partition "ggg_1_prt_bb"
drop table ggg cascade;
ERROR: table "ggg" does not exist
-- legal because start of bb not inclusive
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition bb start (date '2008-01-01') exclusive end (date '2009-01-01'),
partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive
);
drop table ggg cascade;
-- validate aa - start greater than end
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition bb start (date '2008-01-01') end (date '2009-01-01'),
partition aa start (date '2007-01-01') end (date '2006-01-01')
);
ERROR: empty range bound specified for partition "ggg_1_prt_aa"
DETAIL: Specified lower bound ('01-01-2007') is greater than or equal to upper bound ('01-01-2006').
drop table ggg cascade;
ERROR: table "ggg" does not exist
-- formerly we could not set end of first partition because next is before
-- but we can sort them now so this is legal.
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition bb start (date '2008-01-01') ,
partition aa start (date '2007-01-01')
);
drop table ggg cascade;
-- test cross type coercion
-- int -> char(N)
create table ggg (i int, a char(1))
distributed by (i)
partition by list(a)
(partition aa values(1, 2));
drop table ggg cascade;
-- int -> numeric
create table ggg (i int, n numeric(20, 2))
distributed by (i)
partition by list(n)
(partition aa values(1.22, 4.1));
drop table ggg cascade;
-- EVERY
-- the documentation example, rewritten with EVERY in a template
CREATE TABLE rank (id int,
rank int, year date, gender char(1))
DISTRIBUTED BY (id, gender, year)
partition by list (gender)
subpartition by range (year)
subpartition template (
start (date '2001-01-01')
end (date '2006-01-01') every (interval '1 year')) (
partition boys values ('M'),
partition girls values ('F')
);
insert into rank values (1, 1, date '2001-01-15', 'M');
insert into rank values (2, 1, date '2002-02-15', 'M');
insert into rank values (3, 1, date '2003-03-15', 'M');
insert into rank values (4, 1, date '2004-04-15', 'M');
insert into rank values (5, 1, date '2005-05-15', 'M');
insert into rank values (6, 1, date '2001-01-15', 'F');
insert into rank values (7, 1, date '2002-02-15', 'F');
insert into rank values (8, 1, date '2003-03-15', 'F');
insert into rank values (9, 1, date '2004-04-15', 'F');
insert into rank values (10, 1, date '2005-05-15', 'F');
select * from rank order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
1 | 1 | 01-15-2001 | M
2 | 1 | 02-15-2002 | M
3 | 1 | 03-15-2003 | M
4 | 1 | 04-15-2004 | M
5 | 1 | 05-15-2005 | M
6 | 1 | 01-15-2001 | F
7 | 1 | 02-15-2002 | F
8 | 1 | 03-15-2003 | F
9 | 1 | 04-15-2004 | F
10 | 1 | 05-15-2005 | F
(10 rows)
select * from rank_1_prt_boys order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
1 | 1 | 01-15-2001 | M
2 | 1 | 02-15-2002 | M
3 | 1 | 03-15-2003 | M
4 | 1 | 04-15-2004 | M
5 | 1 | 05-15-2005 | M
(5 rows)
select * from rank_1_prt_girls order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
6 | 1 | 01-15-2001 | F
7 | 1 | 02-15-2002 | F
8 | 1 | 03-15-2003 | F
9 | 1 | 04-15-2004 | F
10 | 1 | 05-15-2005 | F
(5 rows)
select * from rank_1_prt_girls_2_prt_1 order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
6 | 1 | 01-15-2001 | F
(1 row)
select * from rank_1_prt_girls_2_prt_2 order by 1, 2, 3, 4;
id | rank | year | gender
----+------+------------+--------
7 | 1 | 02-15-2002 | F
(1 row)
drop table rank cascade;
-- integer ranges work too
create table ggg (id integer, a integer)
distributed by (id)
partition by range (a)
(start (1) end (10) every (1));
insert into ggg values (1, 1);
insert into ggg values (2, 2);
insert into ggg values (3, 3);
insert into ggg values (4, 4);
insert into ggg values (5, 5);
insert into ggg values (6, 6);
insert into ggg values (7, 7);
insert into ggg values (8, 8);
insert into ggg values (9, 9);
insert into ggg values (10, 10);
ERROR: no partition of relation "ggg" found for row (seg2 127.0.1.1:7004 pid=6503)
DETAIL: Partition key of the failing row contains (a) = (10).
select * from ggg order by 1, 2;
id | a
----+---
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
(9 rows)
select * from ggg_1_prt_1 order by 1, 2;
id | a
----+---
1 | 1
(1 row)
select * from ggg_1_prt_2 order by 1, 2;
id | a
----+---
2 | 2
(1 row)
select * from ggg_1_prt_3 order by 1, 2;
id | a
----+---
3 | 3
(1 row)
select * from ggg_1_prt_4 order by 1, 2;
id | a
----+---
4 | 4
(1 row)
drop table ggg cascade;
-- EVERY works by invoking the + operator. We haven't explictly documented
-- that user can create his own, but better still test it.
create domain funnytext as text;
create function funnytext_plus (funnytext, integer) returns funnytext
as $$ select (chr(ascii($1) + $2))::funnytext $$ language sql;
create operator pg_catalog.+ (function=funnytext_plus, leftarg=funnytext, rightarg=integer);
create table ggg (a char(1), t funnytext)
distributed by (a)
partition by range (t)
(
start ('aaa') end ('foobar') every (1)
);
\d+ ggg
Partitioned table "public.ggg"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------+-----------+----------+---------+----------+--------------+-------------
a | character(1) | | | | extended | |
t | funnytext | | | | extended | |
Partition key: RANGE (t)
Partitions: ggg_1_prt_1 FOR VALUES FROM ('aaa') TO ('b'),
ggg_1_prt_2 FOR VALUES FROM ('b') TO ('c'),
ggg_1_prt_3 FOR VALUES FROM ('c') TO ('d'),
ggg_1_prt_4 FOR VALUES FROM ('d') TO ('e'),
ggg_1_prt_5 FOR VALUES FROM ('e') TO ('f'),
ggg_1_prt_6 FOR VALUES FROM ('f') TO ('foobar')
Distributed by: (a)
drop table ggg cascade;
-- What if the + operator returns NULL?
create or replace function funnytext_plus (funnytext, integer) returns funnytext
as $$ select NULL::funnytext $$ language sql;
create table ggg (a char(1), t funnytext)
distributed by (a)
partition by range (t)
(
start ('aaa') end ('foobar') every (1)
);
ERROR: could not compute next partition boundary with EVERY, plus-operator returned NULL
LINE 5: start ('aaa') end ('foobar') every (1)
^
create table fff (a char(1), b char(2), d char(3)) distributed by (a)
partition by list (b) (partition aa values ('2'));
drop table fff cascade;
create table ggg (a char(1), b numeric, d numeric)
distributed by (a)
partition by range (b,d)
(
partition aa start (2007,1) end (2008,2),
partition bb start (2008,2) end (2009,3)
);
ERROR: too many columns for RANGE partition -- only one column is allowed
drop table ggg cascade;
ERROR: table "ggg" does not exist
create table ggg (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition bb start (date '2008-01-01') end (date '2009-01-01'),
partition aa start (date '2007-01-01') end (date '2006-01-01')
);
ERROR: empty range bound specified for partition "ggg_1_prt_aa"
DETAIL: Specified lower bound ('01-01-2007') is greater than or equal to upper bound ('01-01-2006').
drop table ggg cascade;
ERROR: table "ggg" does not exist
-- append only tests
create table foz (i int, d date) with (appendonly = true) distributed by (i)
partition by range (d) (start (date '2001-01-01') end (date '2005-01-01')
every(interval '1 year'));
insert into foz select i, '2001-01-01'::date + ('1 day'::interval * i) from
generate_series(1, 1000) i;
select count(*) from foz;
count
-------
1000
(1 row)
select count(*) from foz_1_prt_1;
count
-------
364
(1 row)
select min(d), max(d) from foz;
min | max
------------+------------
01-02-2001 | 09-28-2003
(1 row)
select min(d), max(d) from foz_1_prt_1;
min | max
------------+------------
01-02-2001 | 12-31-2001
(1 row)
select min(d), max(d) from foz_1_prt_2;
min | max
------------+------------
01-01-2002 | 12-31-2002
(1 row)
select min(d), max(d) from foz_1_prt_3;
min | max
------------+------------
01-01-2003 | 09-28-2003
(1 row)
select min(d), max(d) from foz_1_prt_4;
min | max
-----+-----
|
(1 row)
drop table foz cascade;
-- copy test
create table foz (i int, d date) distributed by (i)
partition by range (d) (start (date '2001-01-01') end (date '2005-01-01')
every(interval '1 year'));
COPY foz FROM stdin DELIMITER '|';
select * from foz_1_prt_1;
i | d
---+------------
1 | 01-02-2001
2 | 10-10-2001
(2 rows)
select * from foz_1_prt_2;
i | d
---+------------
3 | 10-30-2002
(1 row)
select * from foz_1_prt_3;
i | d
---+------------
4 | 01-01-2003
(1 row)
select * from foz_1_prt_4;
i | d
---+------------
5 | 05-05-2004
(1 row)
-- Check behaviour of key for which there is no partition
COPY foz FROM stdin DELIMITER '|';
ERROR: no partition of relation "foz" found for row
DETAIL: Partition key of the failing row contains (d) = (01-01-2010).
CONTEXT: COPY foz, line 1: "6|2010-01-01"
drop table foz cascade;
-- Same test with append only
create table foz (i int, d date) with (appendonly = true) distributed by (i)
partition by range (d) (start (date '2001-01-01') end (date '2005-01-01')
every(interval '1 year'));
COPY foz FROM stdin DELIMITER '|';
select * from foz_1_prt_1;
i | d
---+------------
1 | 01-02-2001
2 | 10-10-2001
(2 rows)
select * from foz_1_prt_2;
i | d
---+------------
3 | 10-30-2002
(1 row)
select * from foz_1_prt_3;
i | d
---+------------
4 | 01-01-2003
(1 row)
select * from foz_1_prt_4;
i | d
---+------------
5 | 05-05-2004
(1 row)
-- Check behaviour of key for which there is no partition
COPY foz FROM stdin DELIMITER '|';
ERROR: no partition of relation "foz" found for row
DETAIL: Partition key of the failing row contains (d) = (01-01-2010).
CONTEXT: COPY foz, line 1: "6|2010-01-01"
drop table foz cascade;
-- complain if create table as select (CTAS)
CREATE TABLE rank1 (id int,
rank int, year date, gender char(1));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table rank2 as select * from rank1
DISTRIBUTED BY (id, gender, year)
partition by list (gender)
subpartition by range (year)
subpartition template (
start (date '2001-01-01')
end (date '2006-01-01') every (interval '1 year')) (
partition boys values ('M'),
partition girls values ('F')
);
ERROR: cannot create a partitioned table using CREATE TABLE AS SELECT
HINT: Use CREATE TABLE...LIKE (followed by INSERT...SELECT) instead.
-- like is ok
create table rank2 (like rank1)
DISTRIBUTED BY (id, gender, year)
partition by list (gender)
subpartition by range (year)
subpartition template (
start (date '2001-01-01')
end (date '2003-01-01') every (interval '1 year')) (
partition boys values ('M'),
partition girls values ('F')
);
drop table rank1 cascade;
drop table rank2 cascade;
-- alter table testing
create table hhh (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2007-01-01') end (date '2008-01-01')
with (appendonly=true),
partition bb start (date '2008-01-01') end (date '2009-01-01')
with (appendonly=false)
);
-- already exists
alter table hhh add partition aa start ('2010-01-01') end ('2011-01-01');
ERROR: relation "hhh_1_prt_aa" already exists
-- no partition spec
alter table hhh add partition cc;
ERROR: missing boundary specification in partition "cc" of type RANGE
-- overlaps
alter table hhh add partition cc start ('2008-01-01') end ('2010-01-01');
ERROR: partition "hhh_1_prt_cc" would overlap partition "hhh_1_prt_bb"
alter table hhh add partition cc end ('2008-01-01');
ERROR: partition "hhh_1_prt_cc" would overlap partition "hhh_1_prt_aa"
-- reversed (start > end)
alter table hhh add partition cc start ('2010-01-01') end ('2009-01-01');
ERROR: empty range bound specified for partition "hhh_1_prt_cc"
DETAIL: Specified lower bound ('01-01-2010') is greater than or equal to upper bound ('01-01-2009').
-- works
--alter table hhh add partition cc start ('2009-01-01') end ('2010-01-01');
alter table hhh add partition cc end ('2010-01-01');
-- works - anonymous partition MPP-3350
alter table hhh add partition end ('2010-02-01');
-- MPP-3607 - ADD PARTITION with open intervals
create table no_end1 (aa int, bb int) partition by range (bb)
(partition foo start(3));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- fail overlap
alter table no_end1 add partition baz end (4);
ERROR: partition "no_end1_1_prt_baz" would overlap partition "no_end1_1_prt_foo"
-- fail overlap (because prior partition has no end)
alter table no_end1 add partition baz start (5);
ERROR: partition "no_end1_1_prt_baz" would overlap partition "no_end1_1_prt_foo"
-- ok (terminates on foo start)
alter table no_end1 add partition baz start (2);
-- ok (because ends before baz start)
alter table no_end1 add partition baz2 end (1);
create table no_start1 (aa int, bb int) partition by range (bb)
(partition foo end(3));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- fail overlap (because next partition has no start)
alter table no_start1 add partition baz start (2);
ERROR: partition "no_start1_1_prt_baz" would overlap partition "no_start1_1_prt_foo"
-- fail overlap (because next partition has no start)
alter table no_start1 add partition baz end (1);
ERROR: partition "no_start1_1_prt_baz" would overlap partition "no_start1_1_prt_foo"
-- ok (starts on foo end)
alter table no_start1 add partition baz end (4);
-- ok (because starts after baz end)
alter table no_start1 add partition baz2 start (5);
select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'no_start%' or relname like 'no_end%';
relname | pg_get_expr
----------------------+-----------------------------------
no_end1 |
no_end1_1_prt_foo | FOR VALUES FROM (3) TO (MAXVALUE)
no_end1_1_prt_baz | FOR VALUES FROM (2) TO (3)
no_end1_1_prt_baz2 | FOR VALUES FROM (MINVALUE) TO (1)
no_start1 |
no_start1_1_prt_foo | FOR VALUES FROM (MINVALUE) TO (3)
no_start1_1_prt_baz | FOR VALUES FROM (3) TO (4)
no_start1_1_prt_baz2 | FOR VALUES FROM (5) TO (MAXVALUE)
(8 rows)
drop table no_end1;
drop table no_start1;
-- default partitions cannot have boundary specifications
create table jjj (aa int, bb date)
partition by range(bb)
(partition j1 end (date '2008-01-01'),
partition j2 end (date '2009-01-01'));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- must have a name
alter table jjj add default partition;
ERROR: syntax error at or near ";"
LINE 1: alter table jjj add default partition;
^
alter table jjj add default partition for ('2008-01-01');
ERROR: can only ADD a partition by name
-- cannot have boundary spec
alter table jjj add default partition j3 end (date '2010-01-01');
ERROR: syntax error at or near "end"
LINE 1: alter table jjj add default partition j3 end (date '2010-01-...
^
drop table jjj cascade;
-- only one default partition
create table jjj (aa int, bb date)
partition by range(bb)
(partition j1 end (date '2008-01-01'),
partition j2 end (date '2009-01-01'),
default partition j3);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
alter table jjj add default partition j3 ;
ERROR: relation "jjj_1_prt_j3" already exists
alter table jjj add default partition j4 ;
ERROR: partition "jjj_1_prt_j4" conflicts with existing default partition "jjj_1_prt_j3"
alter table jjj add partition j5 end (date '2010-01-01');
drop table jjj cascade;
alter table hhh alter partition cc set tablespace foo_p;
ERROR: tablespace "foo_p" does not exist
alter table hhh alter partition aa set tablespace foo_p;
ERROR: tablespace "foo_p" does not exist
alter table hhh drop partition cc;
alter table hhh drop partition cc cascade;
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh drop partition cc restrict;
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh drop partition if exists cc;
-- fail (mpp-3265)
alter table hhh drop partition for ('2001-01-01');
ERROR: partition for specified value of hhh does not exist
create table hhh_r1 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2007-01-01') end (date '2008-01-01')
every (interval '1 month')
);
create table hhh_l1 (a char(1), b date, d char(3))
distributed by (a)
partition by list (b)
(
partition aa values ('2007-01-01'),
partition bb values ('2008-01-01'),
partition cc values ('2009-01-01')
);
-- must have name or value for partition
alter table hhh_l1 drop partition;
ERROR: syntax error at or near ";"
LINE 1: alter table hhh_l1 drop partition;
^
alter table hhh_l1 drop partition aa;
alter table hhh_l1 drop partition for ('2008-01-01');
-- same with range partitioning
alter table hhh_r1 drop partition;
ERROR: syntax error at or near ";"
LINE 1: alter table hhh_r1 drop partition;
^
alter table hhh_r1 drop partition for ('2007-04-01');
alter table hhh_r1 drop partition for ('2007-01-01');
alter table hhh_r1 drop partition aa_2;
alter table hhh_r1 drop partition aa_3;
alter table hhh_r1 drop partition aa_5;
alter table hhh_r1 drop partition aa_6;
-- more add partition tests
-- start before first partition (fail because start equal end)
alter table hhh_r1 add partition zaa start ('2007-07-01');
ERROR: partition "hhh_r1_1_prt_zaa" would overlap partition "hhh_r1_1_prt_aa_7"
-- start before first partition (ok)
alter table hhh_r1 add partition zaa start ('2007-06-01');
-- start > last (fail because start equal end)
alter table hhh_r1 add partition bb start ('2008-01-01') end ('2008-01-01') ;
ERROR: empty range bound specified for partition "hhh_r1_1_prt_bb"
DETAIL: Specified lower bound ('01-01-2008') is greater than or equal to upper bound ('01-01-2008').
-- start > last (ok)
alter table hhh_r1 add partition bb start ('2008-01-01')
end ('2008-02-01') inclusive;
-- start > last (fail because start == last end inclusive)
alter table hhh_r1 add partition cc start ('2008-02-01') end ('2008-03-01') ;
ERROR: partition "hhh_r1_1_prt_cc" would overlap partition "hhh_r1_1_prt_bb"
-- start > last (ok [and leave a gap])
alter table hhh_r1 add partition cc start ('2008-04-01') end ('2008-05-01') ;
-- overlap (fail)
alter table hhh_r1 add partition dd start ('2008-01-01') end ('2008-05-01') ;
ERROR: partition "hhh_r1_1_prt_dd" would overlap partition "hhh_r1_1_prt_bb"
-- new partition in "gap" (ok)
alter table hhh_r1 add partition dd start ('2008-03-01') end ('2008-04-01') ;
-- overlap all partitions (fail)
alter table hhh_r1 add partition ee start ('2006-01-01') end ('2009-01-01') ;
ERROR: partition "hhh_r1_1_prt_ee" would overlap partition "hhh_r1_1_prt_zaa"
-- start before first partition (fail because end in "gap" [and overlaps])
alter table hhh_r1 add partition yaa start ('2007-05-01') end ('2007-07-01');
ERROR: partition "hhh_r1_1_prt_yaa" would overlap partition "hhh_r1_1_prt_zaa"
-- start before first partition (fail )
alter table hhh_r1 add partition yaa start ('2007-05-01')
end ('2007-10-01') inclusive;
ERROR: partition "hhh_r1_1_prt_yaa" would overlap partition "hhh_r1_1_prt_zaa"
-- start before first partition (fail because end overlaps)
alter table hhh_r1 add partition yaa start ('2007-05-01')
end ('2007-10-01') exclusive;
ERROR: partition "hhh_r1_1_prt_yaa" would overlap partition "hhh_r1_1_prt_zaa"
drop table hhh_r1 cascade;
drop table hhh_l1 cascade;
-- the documentation example, rewritten with EVERY in a template
-- and also with a default partition
CREATE TABLE rank (id int,
rank int, year date, gender char(1))
DISTRIBUTED BY (id, gender, year)
partition by list (gender)
subpartition by range (year)
subpartition template (
start (date '2001-01-01')
end (date '2006-01-01') every (interval '1 year')) (
partition boys values ('M'),
partition girls values ('F'),
default partition neuter
);
insert into rank values (1, 1, date '2001-01-15', 'M');
insert into rank values (2, 1, date '2002-02-15', 'M');
insert into rank values (3, 1, date '2003-03-15', 'M');
insert into rank values (4, 1, date '2004-04-15', 'M');
insert into rank values (5, 1, date '2005-05-15', 'M');
insert into rank values (6, 1, date '2001-01-15', 'F');
insert into rank values (7, 1, date '2002-02-15', 'F');
insert into rank values (8, 1, date '2003-03-15', 'F');
insert into rank values (9, 1, date '2004-04-15', 'F');
insert into rank values (10, 1, date '2005-05-15', 'F');
select * from rank ;
id | rank | year | gender
----+------+------------+--------
3 | 1 | 03-15-2003 | M
5 | 1 | 05-15-2005 | M
6 | 1 | 01-15-2001 | F
7 | 1 | 02-15-2002 | F
8 | 1 | 03-15-2003 | F
9 | 1 | 04-15-2004 | F
10 | 1 | 05-15-2005 | F
2 | 1 | 02-15-2002 | M
1 | 1 | 01-15-2001 | M
4 | 1 | 04-15-2004 | M
(10 rows)
alter table rank DROP partition boys restrict;
select * from rank ;
id | rank | year | gender
----+------+------------+--------
6 | 1 | 01-15-2001 | F
7 | 1 | 02-15-2002 | F
8 | 1 | 03-15-2003 | F
9 | 1 | 04-15-2004 | F
10 | 1 | 05-15-2005 | F
(5 rows)
-- MPP-3722: complain if for(value) matches the default partition
alter table rank truncate partition for('N');
ERROR: FOR expression matches DEFAULT partition for specified value of relation "rank"
HINT: FOR expression may only specify a non-default partition in this context.
alter table rank DROP partition for('N');
ERROR: FOR expression matches DEFAULT partition for specified value of relation "rank"
HINT: FOR expression may only specify a non-default partition in this context.
alter table rank DROP partition if exists for('N');
ERROR: FOR expression matches DEFAULT partition for specified value of relation "rank"
HINT: FOR expression may only specify a non-default partition in this context.
alter table rank DROP default partition if exists ;
-- can't drop the final partition - must drop the table
alter table rank DROP partition girls;
ERROR: cannot drop partition "rank_1_prt_girls" of "rank" -- only one remains
HINT: Use DROP TABLE "rank" to remove the table and the final partition
-- MPP-4011: make FOR(value) work
alter table rank alter partition for ('F') add default partition def1;
alter table rank alter partition for ('F')
truncate partition for ('2010-10-10');
ERROR: FOR expression matches DEFAULT partition for specified value of relation "rank_1_prt_girls"
HINT: FOR expression may only specify a non-default partition in this context.
alter table rank truncate partition for ('F');
drop table rank cascade;
alter table hhh exchange partition cc with table nosuchtable;
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh exchange partition aa with table nosuchtable;
ERROR: relation "nosuchtable" does not exist
alter table hhh rename partition cc to aa;
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh rename partition bb to aa;
ERROR: relation "hhh_1_prt_aa" already exists
alter table hhh rename partition aa to aa;
ERROR: relation "hhh_1_prt_aa" already exists
alter table hhh rename partition aa to "funky fresh";
alter table hhh rename partition "funky fresh" to aa;
-- use FOR PARTITION VALUE (with implicate date conversion)
alter table hhh rename partition for ('2007-01-01') to "funky fresh";
alter table hhh rename partition for ('2007-01-01') to aa;
alter table hhh set subpartition template ();
ERROR: relation "hhh" does not have a level 1 subpartition template specification
alter table hhh split partition cc at ('a');
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh split partition cc at ('a') into (partition gg, partition hh);
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh split partition aa at ('a');
ERROR: invalid input syntax for type date: "a"
LINE 1: alter table hhh split partition aa at ('a');
^
alter table hhh truncate partition cc;
ERROR: relation "public.hhh_1_prt_cc" does not exist
alter table hhh truncate partition aa;
insert into hhh values('a', date '2007-01-02', 'b');
insert into hhh values('a', date '2007-02-01', 'b');
insert into hhh values('a', date '2007-03-01', 'b');
insert into hhh values('a', date '2007-04-01', 'b');
insert into hhh values('a', date '2007-05-01', 'b');
insert into hhh values('a', date '2007-06-01', 'b');
insert into hhh values('a', date '2007-07-01', 'b');
insert into hhh values('a', date '2007-08-01', 'b');
insert into hhh values('a', date '2007-09-01', 'b');
insert into hhh values('a', date '2007-10-01', 'b');
insert into hhh values('a', date '2007-11-01', 'b');
insert into hhh values('a', date '2007-12-01', 'b');
insert into hhh values('a', date '2008-01-02', 'b');
insert into hhh values('a', date '2008-02-01', 'b');
insert into hhh values('a', date '2008-03-01', 'b');
insert into hhh values('a', date '2008-04-01', 'b');
insert into hhh values('a', date '2008-05-01', 'b');
insert into hhh values('a', date '2008-06-01', 'b');
insert into hhh values('a', date '2008-07-01', 'b');
insert into hhh values('a', date '2008-08-01', 'b');
insert into hhh values('a', date '2008-09-01', 'b');
insert into hhh values('a', date '2008-10-01', 'b');
insert into hhh values('a', date '2008-11-01', 'b');
insert into hhh values('a', date '2008-12-01', 'b');
select * from hhh;
a | b | d
---+------------+-----
a | 01-02-2007 | b
a | 02-01-2007 | b
a | 03-01-2007 | b
a | 04-01-2007 | b
a | 05-01-2007 | b
a | 06-01-2007 | b
a | 07-01-2007 | b
a | 08-01-2007 | b
a | 09-01-2007 | b
a | 10-01-2007 | b
a | 11-01-2007 | b
a | 12-01-2007 | b
a | 01-02-2008 | b
a | 02-01-2008 | b
a | 03-01-2008 | b
a | 04-01-2008 | b
a | 05-01-2008 | b
a | 06-01-2008 | b
a | 07-01-2008 | b
a | 08-01-2008 | b
a | 09-01-2008 | b
a | 10-01-2008 | b
a | 11-01-2008 | b
a | 12-01-2008 | b
(24 rows)
alter table hhh truncate partition aa;
select * from hhh;
a | b | d
---+------------+-----
a | 01-02-2008 | b
a | 02-01-2008 | b
a | 03-01-2008 | b
a | 04-01-2008 | b
a | 05-01-2008 | b
a | 06-01-2008 | b
a | 07-01-2008 | b
a | 08-01-2008 | b
a | 09-01-2008 | b
a | 10-01-2008 | b
a | 11-01-2008 | b
a | 12-01-2008 | b
(12 rows)
alter table hhh truncate partition bb;
select * from hhh;
a | b | d
---+---+---
(0 rows)
insert into hhh values('a', date '2007-01-02', 'b');
insert into hhh values('a', date '2007-02-01', 'b');
insert into hhh values('a', date '2007-03-01', 'b');
insert into hhh values('a', date '2007-04-01', 'b');
insert into hhh values('a', date '2007-05-01', 'b');
insert into hhh values('a', date '2007-06-01', 'b');
insert into hhh values('a', date '2007-07-01', 'b');
insert into hhh values('a', date '2007-08-01', 'b');
insert into hhh values('a', date '2007-09-01', 'b');
insert into hhh values('a', date '2007-10-01', 'b');
insert into hhh values('a', date '2007-11-01', 'b');
insert into hhh values('a', date '2007-12-01', 'b');
insert into hhh values('a', date '2008-01-02', 'b');
insert into hhh values('a', date '2008-02-01', 'b');
insert into hhh values('a', date '2008-03-01', 'b');
insert into hhh values('a', date '2008-04-01', 'b');
insert into hhh values('a', date '2008-05-01', 'b');
insert into hhh values('a', date '2008-06-01', 'b');
insert into hhh values('a', date '2008-07-01', 'b');
insert into hhh values('a', date '2008-08-01', 'b');
insert into hhh values('a', date '2008-09-01', 'b');
insert into hhh values('a', date '2008-10-01', 'b');
insert into hhh values('a', date '2008-11-01', 'b');
insert into hhh values('a', date '2008-12-01', 'b');
select * from hhh;
a | b | d
---+------------+-----
a | 01-02-2007 | b
a | 02-01-2007 | b
a | 03-01-2007 | b
a | 04-01-2007 | b
a | 05-01-2007 | b
a | 06-01-2007 | b
a | 07-01-2007 | b
a | 08-01-2007 | b
a | 09-01-2007 | b
a | 10-01-2007 | b
a | 11-01-2007 | b
a | 12-01-2007 | b
a | 01-02-2008 | b
a | 02-01-2008 | b
a | 03-01-2008 | b
a | 04-01-2008 | b
a | 05-01-2008 | b
a | 06-01-2008 | b
a | 07-01-2008 | b
a | 08-01-2008 | b
a | 09-01-2008 | b
a | 10-01-2008 | b
a | 11-01-2008 | b
a | 12-01-2008 | b
(24 rows)
-- truncate child partitions recursively
truncate table hhh;
select * from hhh;
a | b | d
---+---+---
(0 rows)
drop table hhh cascade;
-- default partitions
-- default partitions cannot have boundary specifications
create table jjj (aa int, bb date)
partition by range(bb)
(partition j1 end (date '2008-01-01'),
partition j2 end (date '2009-01-01'),
default partition j3 end (date '2010-01-01'));
ERROR: syntax error at or near "end"
LINE 5: default partition j3 end (date '2010-01-01'));
^
-- more than one default partition
create table jjj (aa int, bb date)
partition by range(bb)
(partition j1 end (date '2008-01-01'),
partition j2 end (date '2009-01-01'),
default partition j3,
default partition j4);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ERROR: multiple default partitions are not allowed
LINE 6: default partition j4);
^
-- check default
create table foz (i int, d date) distributed by (i)
partition by range (d)
(
default partition dsf,
partition foo start (date '2001-01-01') end (date '2005-01-01')
every(interval '1 year')
);
insert into foz values(1, '2003-04-01');
insert into foz values(2, '2010-04-01');
select * from foz;
i | d
---+------------
2 | 04-01-2010
1 | 04-01-2003
(2 rows)
select * from foz_1_prt_dsf;
i | d
---+------------
2 | 04-01-2010
(1 row)
drop table foz cascade;
-- check for out of order partition definitions. We should order these correctly
-- and determine the appropriate boundaries.
create table d (i int, j int) distributed by (i) partition by range(j)
( start (10), start(5), start(50) end(60));
insert into d values(1, 5);
insert into d values(1, 10);
insert into d values(1, 11);
insert into d values(1, 55);
insert into d values(1, 70);
ERROR: no partition of relation "d" found for row (seg1 127.0.1.1:7003 pid=6502)
DETAIL: Partition key of the failing row contains (j) = (70).
\d+ d
Partitioned table "public.d"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
j | integer | | | | plain | |
Partition key: RANGE (j)
Partitions: d_1_prt_1 FOR VALUES FROM (10) TO (50),
d_1_prt_2 FOR VALUES FROM (5) TO (10),
d_1_prt_3 FOR VALUES FROM (50) TO (60)
Distributed by: (i)
select * from d;
i | j
---+----
1 | 5
1 | 10
1 | 11
1 | 55
(4 rows)
select * from d_1_prt_1;
i | j
---+----
1 | 10
1 | 11
(2 rows)
select * from d_1_prt_2;
i | j
---+---
1 | 5
(1 row)
select * from d_1_prt_3;
i | j
---+----
1 | 55
(1 row)
drop table d cascade;
-- check for NULL support
-- list
create table d (i int, j int) partition by list(j)
(partition a values(1, 2, NULL),
partition b values(3, 4)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into d values(1, 1);
insert into d values(1, 2);
insert into d values(1, NULL);
insert into d values(1, 3);
insert into d values(1, 4);
select * from d_1_prt_a;
i | j
---+---
1 | 1
1 | 2
1 |
(3 rows)
select * from d_1_prt_b;
i | j
---+---
1 | 3
1 | 4
(2 rows)
drop table d cascade;
--range
-- Reject NULL values
create table d (i int, j int) partition by range(j)
(partition a start (1) end(10), partition b start(11) end(20));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into d values (1, 1);
insert into d values (1, 2);
insert into d values (1, NULL);
ERROR: no partition of relation "d" found for row (seg1 127.0.1.1:7003 pid=6502)
DETAIL: Partition key of the failing row contains (j) = (null).
drop table d cascade;
-- allow NULLs into the default partition
create table d (i int, j int) partition by range(j)
(partition a start (1) end(10), partition b start(11) end(20),
default partition abc);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into d values (1, 1);
insert into d values (1, 2);
insert into d values (1, NULL);
select * from d_1_prt_abc;
i | j
---+---
1 |
(1 row)
alter table d split default partition start (21,1) end (30) into (partition c, default partition);
ERROR: number of START values should cover all partition key columns
LINE 1: alter table d split default partition start (21,1) end (30) ...
^
alter table d split default partition start (21) end (30,1) into (partition c, default partition);
ERROR: number of END values should cover all partition key columns
LINE 1: alter table d split default partition start (21) end (30,1) ...
^
drop table d cascade;
-- multicolumn list support
create type d_partkey as (b int, c int);
create table d (a int, k d_partkey) distributed by (a)
partition by list(k)
(partition a values(CAST('(1,2)' as d_partkey), CAST('(3,4)' as d_partkey)),
partition b values(CAST('(100,20)' as d_partkey)),
partition c values(CAST('(1000,1001)' as d_partkey), CAST('(1001,1002)' as d_partkey), CAST('(1003,1004)' as d_partkey)));
\d+ d
Partitioned table "public.d"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
k | d_partkey | | | | extended | |
Partition key: LIST (k)
Partitions: d_1_prt_a FOR VALUES IN ('(1,2)', '(3,4)'),
d_1_prt_b FOR VALUES IN ('(100,20)'),
d_1_prt_c FOR VALUES IN ('(1000,1001)', '(1001,1002)', '(1003,1004)')
Distributed by: (a)
insert into d values(1, (1, 2));
insert into d values(1, (3, 4));
insert into d values(1, (100, 20));
insert into d values(1, (100, 2000));
ERROR: no partition of relation "d" found for row (seg1 127.0.1.1:7003 pid=10417)
DETAIL: Partition key of the failing row contains (k) = ((100,2000)).
insert into d values(1, ('1000', '1001')), (1, ('1001', '1002')), (1, ('1003', '1004'));
insert into d values(1, (100, NULL));
ERROR: no partition of relation "d" found for row (seg1 127.0.1.1:7003 pid=10417)
DETAIL: Partition key of the failing row contains (k) = ((100,)).
select * from d_1_prt_a;
a | k
---+-------
1 | (1,2)
1 | (3,4)
(2 rows)
select * from d_1_prt_b;
a | k
---+----------
1 | (100,20)
(1 row)
select * from d_1_prt_c;
a | k
---+-------------
1 | (1000,1001)
1 | (1001,1002)
1 | (1003,1004)
(3 rows)
drop table d cascade;
-- test multi value range partitioning
create table b (i int, j date) distributed by (i)
partition by range (i, j)
(start(1, '2008-01-01') end (10, '2009-01-01'),
start(1, '2009-01-01') end(15, '2010-01-01'),
start(15, '2010-01-01') end (30, '2011-01-01'),
start(1, '2011-01-01') end (100, '2012-01-01')
);
ERROR: too many columns for RANGE partition -- only one column is allowed
-- should work
insert into b values(1, '2008-06-11');
ERROR: relation "b" does not exist
LINE 1: insert into b values(1, '2008-06-11');
^
insert into b values(11, '2009-08-24');
ERROR: relation "b" does not exist
LINE 1: insert into b values(11, '2009-08-24');
^
insert into b values(25, '2010-01-22');
ERROR: relation "b" does not exist
LINE 1: insert into b values(25, '2010-01-22');
^
insert into b values(90, '2011-05-04');
ERROR: relation "b" does not exist
LINE 1: insert into b values(90, '2011-05-04');
^
-- shouldn't work
insert into b values(1, '2019-01-01');
ERROR: relation "b" does not exist
LINE 1: insert into b values(1, '2019-01-01');
^
insert into b values(91, '2008-05-05');
ERROR: relation "b" does not exist
LINE 1: insert into b values(91, '2008-05-05');
^
select * from b_1_prt_1;
ERROR: relation "b_1_prt_1" does not exist
LINE 1: select * from b_1_prt_1;
^
select * from b_1_prt_2;
ERROR: relation "b_1_prt_2" does not exist
LINE 1: select * from b_1_prt_2;
^
select * from b_1_prt_3;
ERROR: relation "b_1_prt_3" does not exist
LINE 1: select * from b_1_prt_3;
^
select * from b_1_prt_4;
ERROR: relation "b_1_prt_4" does not exist
LINE 1: select * from b_1_prt_4;
^
drop table b;
ERROR: table "b" does not exist
-- try some different combinations
create table b (i int, n numeric(20, 2), t timestamp, s text)
distributed by (i)
partition by range(n, t, s)
(
start(2000.99, '2007-01-01 00:00:00', 'AAA')
end (4000.95, '2007-02-02 15:00:00', 'BBB'),
start(2000.99, '2007-01-01 00:00:00', 'BBB')
end (4000.95, '2007-02-02 16:00:00', 'CCC'),
start(4000.95, '2007-01-01 00:00:00', 'AAA')
end (7000.95, '2007-02-02 15:00:00', 'BBB')
);
ERROR: too many columns for RANGE partition -- only one column is allowed
-- should work
insert into b values(1, 2000.99, '2007-01-01 00:00:00', 'AAA');
ERROR: relation "b" does not exist
LINE 1: insert into b values(1, 2000.99, '2007-01-01 00:00:00', 'AAA...
^
insert into b values(2, 2000.99, '2007-01-01 00:00:00', 'BBB');
ERROR: relation "b" does not exist
LINE 1: insert into b values(2, 2000.99, '2007-01-01 00:00:00', 'BBB...
^
insert into b values(3, 4000.95, '2007-01-01 00:00:00', 'AAA');
ERROR: relation "b" does not exist
LINE 1: insert into b values(3, 4000.95, '2007-01-01 00:00:00', 'AAA...
^
insert into b values(6, 3000, '2007-02-02 15:30:00', 'BBC');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-02-02 15:30:00', 'BBC');
^
insert into b values(6, 3000, '2007-02-02 15:30:00', 'CC');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-02-02 15:30:00', 'CC');
^
insert into b values(6, 3000, '2007-02-02 16:00:00'::timestamp -
'1 second'::interval, 'BBZZZZZZZZZZ');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-02-02 16:00:00'::timesta...
^
-- should fail
insert into b values(6, 3000, '2007-02-02 15:30:00', 'CCCCCCC');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-02-02 15:30:00', 'CCCCCC...
^
insert into b values(4, 5000, '2007-01-01 12:00:00', 'BCC');
ERROR: relation "b" does not exist
LINE 1: insert into b values(4, 5000, '2007-01-01 12:00:00', 'BCC');
^
insert into b values(5, 8000, '2007-01-01 12:00:00', 'ZZZ');
ERROR: relation "b" does not exist
LINE 1: insert into b values(5, 8000, '2007-01-01 12:00:00', 'ZZZ');
^
insert into b values(6, 3000, '2007-02-02 16:00:00', 'ABZZZZZZZZZZ');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-02-02 16:00:00', 'ABZZZZ...
^
insert into b values(6, 1000, '2007-02-02 16:00:00', 'ABZZZZZZZZZZ');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 1000, '2007-02-02 16:00:00', 'ABZZZZ...
^
insert into b values(6, 3000, '2006-02-02 16:00:00', 'ABZZZZZZZZZZ');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2006-02-02 16:00:00', 'ABZZZZ...
^
insert into b values(6, 3000, '2007-02-02 00:00:00', 'A');
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-02-02 00:00:00', 'A');
^
-- NULL tests
insert into b default values;
ERROR: relation "b" does not exist
LINE 1: insert into b default values;
^
insert into b values(6, 3000, '2007-01-01 12:00:00', NULL);
ERROR: relation "b" does not exist
LINE 1: insert into b values(6, 3000, '2007-01-01 12:00:00', NULL);
^
drop table b;
ERROR: table "b" does not exist
-- MPP-3988: allow same column in multiple partitioning keys at
-- different levels -- so this is legal again...
drop table if exists a;
-- TEST: make sure GPOPT (aka pivotal query optimizer) fall back to Postgres query optimizer
-- for queries with partition elimination over FULL OUTER JOIN
-- between partitioned tables.
-- SETUP
-- start_ignore
drop table if exists s1;
NOTICE: table "s1" does not exist, skipping
drop table if exists s2;
NOTICE: table "s2" does not exist, skipping
-- setup two partitioned tables s1 and s2
create table s1 (d1 int, p1 int)
distributed by (d1)
partition by list (p1)
(
values (0),
values (1));
create table s2 (d2 int, p2 int)
distributed by (d2)
partition by list (p2)
(
values (0),
values (1));
-- end_ignore
-- VERIFY
-- expect GPOPT fall back to Postgres query optimizer
-- since GPOPT don't support partition elimination through full outer joins
select * from s1 full outer join s2 on s1.d1 = s2.d2 and s1.p1 = s2.p2 where s1.p1 = 1;
d1 | p1 | d2 | p2
----+----+----+----
(0 rows)
-- CLEANUP
-- start_ignore
drop table if exists s1;
drop table if exists s2;
-- end_ignore
-- the following case is to test when we have a template
-- we can correct add new subpartition with relation options.
create table test_part_relops_tmpl (id int, p1 text, p2 text, count int)
distributed by (id)
partition by list (p1)
subpartition by list (p2)
(
partition m1 values ('m1')
(subpartition l1 values ('l1'),
subpartition l2 values ('l2')),
partition m2 values ('m2')
(subpartition l1 values ('l1'),
subpartition l2 values ('l2'))
);
alter table test_part_relops_tmpl
set subpartition template
(
subpartition l1 values('l1')
);
-- previously, we do wrong in the function of `add_partition_rule`
-- which invokes `transformRelOptions`, and transformRelOptions
-- may return NULL in some cases. For example, the invokation of
-- transformRelOptions in add_partition_rule set ignoreOids = true,
-- so the following statement creates such senario by passing oids options,
-- then transformRelOptions return NULL and we should correctly handle
-- null pointers.
alter table test_part_relops_tmpl alter partition for ('m1') add partition l3 values ('l3')
with (oids=false);
create table mpp_2914A(id int, buyDate date, kind char(1))
DISTRIBUTED BY (id)
partition by list (kind)
subpartition by range(buyDate)
subpartition template
(
start (date '2001-01-01'),
start (date '2002-01-01'),
start (date '2003-01-01'),
start (date '2004-01-01'),
start (date '2005-01-01')
)
(
partition auction values('a','A'),
partition buyItNow values('b', 'B'),
default partition catchall
);
select count(*) from mpp_2914A;
count
-------
0
(1 row)
\d mpp_2914a*
Partitioned table "public.mpp_2914a"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition key: LIST (kind)
Number of partitions: 3 (Use \d+ to list them.)
Distributed by: (id)
Partitioned table "public.mpp_2914a_1_prt_auction"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a FOR VALUES IN ('a', 'A')
Partition key: RANGE (buydate)
Number of partitions: 5 (Use \d+ to list them.)
Distributed by: (id)
Table "public.mpp_2914a_1_prt_auction_2_prt_1"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_auction FOR VALUES FROM ('01-01-2001') TO ('01-01-2002')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_auction_2_prt_2"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_auction FOR VALUES FROM ('01-01-2002') TO ('01-01-2003')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_auction_2_prt_3"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_auction FOR VALUES FROM ('01-01-2003') TO ('01-01-2004')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_auction_2_prt_4"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_auction FOR VALUES FROM ('01-01-2004') TO ('01-01-2005')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_auction_2_prt_5"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_auction FOR VALUES FROM ('01-01-2005') TO (MAXVALUE)
Distributed by: (id)
Partitioned table "public.mpp_2914a_1_prt_buyitnow"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a FOR VALUES IN ('b', 'B')
Partition key: RANGE (buydate)
Number of partitions: 5 (Use \d+ to list them.)
Distributed by: (id)
Table "public.mpp_2914a_1_prt_buyitnow_2_prt_1"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_buyitnow FOR VALUES FROM ('01-01-2001') TO ('01-01-2002')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_buyitnow_2_prt_2"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_buyitnow FOR VALUES FROM ('01-01-2002') TO ('01-01-2003')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_buyitnow_2_prt_3"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_buyitnow FOR VALUES FROM ('01-01-2003') TO ('01-01-2004')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_buyitnow_2_prt_4"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_buyitnow FOR VALUES FROM ('01-01-2004') TO ('01-01-2005')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_buyitnow_2_prt_5"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_buyitnow FOR VALUES FROM ('01-01-2005') TO (MAXVALUE)
Distributed by: (id)
Partitioned table "public.mpp_2914a_1_prt_catchall"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a DEFAULT
Partition key: RANGE (buydate)
Number of partitions: 5 (Use \d+ to list them.)
Distributed by: (id)
Table "public.mpp_2914a_1_prt_catchall_2_prt_1"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_catchall FOR VALUES FROM ('01-01-2001') TO ('01-01-2002')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_catchall_2_prt_2"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_catchall FOR VALUES FROM ('01-01-2002') TO ('01-01-2003')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_catchall_2_prt_3"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_catchall FOR VALUES FROM ('01-01-2003') TO ('01-01-2004')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_catchall_2_prt_4"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_catchall FOR VALUES FROM ('01-01-2004') TO ('01-01-2005')
Distributed by: (id)
Table "public.mpp_2914a_1_prt_catchall_2_prt_5"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914a_1_prt_catchall FOR VALUES FROM ('01-01-2005') TO (MAXVALUE)
Distributed by: (id)
create table mpp_2914B(id int, buyDate date, kind char(1))
DISTRIBUTED BY (id)
partition by list (kind)
subpartition by range(buyDate)
(
partition auction values('a','A')
(
subpartition y2001 start (date '2001-01-01'),
subpartition y2002 start (date '2002-01-01'),
subpartition y2003 start (date '2003-01-01'),
subpartition y2004 start (date '2004-01-01'),
subpartition y2005 start (date '2005-01-01')
),
partition buyitnow values('b','B')
(
subpartition y2001 start (date '2001-01-01'),
subpartition y2002 start (date '2002-01-01'),
subpartition y2003 start (date '2003-01-01'),
subpartition y2004 start (date '2004-01-01'),
subpartition y2005 start (date '2005-01-01')
),
default partition catchAll
(
subpartition y2001 start (date '2001-01-01'),
subpartition y2002 start (date '2002-01-01'),
subpartition y2003 start (date '2003-01-01'),
subpartition y2004 start (date '2004-01-01'),
subpartition y2005 start (date '2005-01-01')
)
);
select count(*) from mpp_2914B;
count
-------
0
(1 row)
\d mpp_2914b*
Partitioned table "public.mpp_2914b"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition key: LIST (kind)
Number of partitions: 3 (Use \d+ to list them.)
Distributed by: (id)
Partitioned table "public.mpp_2914b_1_prt_auction"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b FOR VALUES IN ('a', 'A')
Partition key: RANGE (buydate)
Number of partitions: 5 (Use \d+ to list them.)
Distributed by: (id)
Table "public.mpp_2914b_1_prt_auction_2_prt_y2001"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_auction FOR VALUES FROM ('01-01-2001') TO ('01-01-2002')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_auction_2_prt_y2002"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_auction FOR VALUES FROM ('01-01-2002') TO ('01-01-2003')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_auction_2_prt_y2003"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_auction FOR VALUES FROM ('01-01-2003') TO ('01-01-2004')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_auction_2_prt_y2004"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_auction FOR VALUES FROM ('01-01-2004') TO ('01-01-2005')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_auction_2_prt_y2005"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_auction FOR VALUES FROM ('01-01-2005') TO (MAXVALUE)
Distributed by: (id)
Partitioned table "public.mpp_2914b_1_prt_buyitnow"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b FOR VALUES IN ('b', 'B')
Partition key: RANGE (buydate)
Number of partitions: 5 (Use \d+ to list them.)
Distributed by: (id)
Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2001"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_buyitnow FOR VALUES FROM ('01-01-2001') TO ('01-01-2002')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2002"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_buyitnow FOR VALUES FROM ('01-01-2002') TO ('01-01-2003')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2003"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_buyitnow FOR VALUES FROM ('01-01-2003') TO ('01-01-2004')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2004"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_buyitnow FOR VALUES FROM ('01-01-2004') TO ('01-01-2005')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_buyitnow_2_prt_y2005"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_buyitnow FOR VALUES FROM ('01-01-2005') TO (MAXVALUE)
Distributed by: (id)
Partitioned table "public.mpp_2914b_1_prt_catchall"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b DEFAULT
Partition key: RANGE (buydate)
Number of partitions: 5 (Use \d+ to list them.)
Distributed by: (id)
Table "public.mpp_2914b_1_prt_catchall_2_prt_y2001"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_catchall FOR VALUES FROM ('01-01-2001') TO ('01-01-2002')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_catchall_2_prt_y2002"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_catchall FOR VALUES FROM ('01-01-2002') TO ('01-01-2003')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_catchall_2_prt_y2003"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_catchall FOR VALUES FROM ('01-01-2003') TO ('01-01-2004')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_catchall_2_prt_y2004"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_catchall FOR VALUES FROM ('01-01-2004') TO ('01-01-2005')
Distributed by: (id)
Table "public.mpp_2914b_1_prt_catchall_2_prt_y2005"
Column | Type | Collation | Nullable | Default
---------+--------------+-----------+----------+---------
id | integer | | |
buydate | date | | |
kind | character(1) | | |
Partition of: mpp_2914b_1_prt_catchall FOR VALUES FROM ('01-01-2005') TO (MAXVALUE)
Distributed by: (id)
drop table mpp_2914a cascade;
drop table mpp_2914b cascade;
create table mpp10847_pkeyconstraints(
pkid serial,
option1 int,
option2 int,
option3 int,
primary key(pkid, option3))
distributed by (pkid) partition by range (option3)
(
partition aa start(1) end(100) inclusive,
partition bb start(101) end(200) inclusive,
partition cc start(201) end (300) inclusive
);
insert into mpp10847_pkeyconstraints values (10000, 50, 50, 102);
-- This is supposed to fail as you're not supposed to be able to use the same
-- primary key in the same table. But GPDB cannot currently enforce that.
insert into mpp10847_pkeyconstraints values (10000, 50, 50, 5);
select * from mpp10847_pkeyconstraints;
pkid | option1 | option2 | option3
-------+---------+---------+---------
10000 | 50 | 50 | 5
10000 | 50 | 50 | 102
(2 rows)
drop table mpp10847_pkeyconstraints;
-- Test that ADD/EXCHANGE/SPLIT PARTITION works, even when there are partial or expression
-- indexes on the table. (MPP-13750)
create table dcl_messaging_test
(
message_create_date timestamp(3) not null,
trace_socket varchar(1024) null,
trace_count varchar(1024) null,
variable_10 varchar(1024) null,
variable_11 varchar(1024) null,
variable_12 varchar(1024) null,
variable_13 varchar(1024) default('-1'),
variable_14 varchar(1024) null,
variable_15 varchar(1024) null,
variable_16 varchar(1024) null,
variable_17 varchar(1024) null,
variable_18 varchar(1024) null,
variable_19 varchar(1024) null,
variable_20 varchar(1024) null
)
distributed by (message_create_date)
partition by range (message_create_date)
(
START (timestamp '2011-09-01') END (timestamp '2011-09-10') EVERY (interval '1 day'),
DEFAULT PARTITION outlying_dates
);
-- partial index
create index dcl_messaging_test_index13 on dcl_messaging_test(variable_13) where message_create_date > '2011-09-02';
-- expression index
create index dcl_messaging_test_index16 on dcl_messaging_test(upper(variable_16));
alter table dcl_messaging_test drop default partition;
-- ADD case
alter table dcl_messaging_test add partition start (timestamp '2011-09-10') inclusive end (timestamp '2011-09-11') exclusive;
-- EXCHANGE case
create table dcl_candidate(like dcl_messaging_test including indexes) with (appendonly=true);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
insert into dcl_candidate(message_create_date) values (timestamp '2011-09-06');
alter table dcl_messaging_test exchange partition for ('2011-09-06') with table dcl_candidate;
-- SPLIT case
alter table dcl_messaging_test split partition for (timestamp '2011-09-06') at (timestamp '2011-09-06 12:00:00') into (partition x1, partition x2);
--
-- Create table with 4 partitions
CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2008-01-05') EXCLUSIVE
EVERY (INTERVAL '1 day') );
-- Add unbound partition right before the start succeeds
alter table mpp13806 add partition test end (date '2008-01-01') exclusive;
select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%';
relname | pg_get_expr
---------------------+--------------------------------------------------
mpp13806 |
mpp13806_1_prt_1 | FOR VALUES FROM ('01-01-2008') TO ('01-02-2008')
mpp13806_1_prt_2 | FOR VALUES FROM ('01-02-2008') TO ('01-03-2008')
mpp13806_1_prt_3 | FOR VALUES FROM ('01-03-2008') TO ('01-04-2008')
mpp13806_1_prt_4 | FOR VALUES FROM ('01-04-2008') TO ('01-05-2008')
mpp13806_1_prt_test | FOR VALUES FROM (MINVALUE) TO ('01-01-2008')
(6 rows)
-- Drop the partition
alter TABLE mpp13806 drop partition test;
-- Add unbound partition with a gap succeeds
alter table mpp13806 add partition test end (date '2007-12-31') exclusive;
select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%';
relname | pg_get_expr
---------------------+--------------------------------------------------
mpp13806 |
mpp13806_1_prt_1 | FOR VALUES FROM ('01-01-2008') TO ('01-02-2008')
mpp13806_1_prt_2 | FOR VALUES FROM ('01-02-2008') TO ('01-03-2008')
mpp13806_1_prt_3 | FOR VALUES FROM ('01-03-2008') TO ('01-04-2008')
mpp13806_1_prt_4 | FOR VALUES FROM ('01-04-2008') TO ('01-05-2008')
mpp13806_1_prt_test | FOR VALUES FROM (MINVALUE) TO ('12-31-2007')
(6 rows)
-- Fill the gap succeeds/adding immediately before the first partition succeeds
alter table mpp13806 add partition test1 start (date '2007-12-31') inclusive end (date '2008-01-01') exclusive;
select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%';
relname | pg_get_expr
----------------------+--------------------------------------------------
mpp13806 |
mpp13806_1_prt_1 | FOR VALUES FROM ('01-01-2008') TO ('01-02-2008')
mpp13806_1_prt_2 | FOR VALUES FROM ('01-02-2008') TO ('01-03-2008')
mpp13806_1_prt_3 | FOR VALUES FROM ('01-03-2008') TO ('01-04-2008')
mpp13806_1_prt_4 | FOR VALUES FROM ('01-04-2008') TO ('01-05-2008')
mpp13806_1_prt_test | FOR VALUES FROM (MINVALUE) TO ('12-31-2007')
mpp13806_1_prt_test1 | FOR VALUES FROM ('12-31-2007') TO ('01-01-2008')
(7 rows)
drop table if exists mpp13806;
CREATE TABLE mpp13806 (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2008-01-01') EXCLUSIVE
END (date '2008-01-05') EXCLUSIVE
EVERY (INTERVAL '1 day') );
-- For good measure, test the opposite case
alter table mpp13806 add partition test end (date '2008-01-01') inclusive;
select relname, pg_get_expr(relpartbound, oid) from pg_class where relname like 'mpp13806%';
relname | pg_get_expr
---------------------+--------------------------------------------------
mpp13806 |
mpp13806_1_prt_1 | FOR VALUES FROM ('01-02-2008') TO ('01-03-2008')
mpp13806_1_prt_2 | FOR VALUES FROM ('01-03-2008') TO ('01-04-2008')
mpp13806_1_prt_3 | FOR VALUES FROM ('01-04-2008') TO ('01-05-2008')
mpp13806_1_prt_test | FOR VALUES FROM (MINVALUE) TO ('01-02-2008')
(5 rows)
drop table mpp13806;
--
-- Create two tables mpp14613_range (range partitioned) and
-- mpp14613_list (list partitioned) with 5 partitions (including default
-- partition) and 3 subpartitions (including default subpartition) each
create table mpp14613_list(
a int,
b int,
c int,
d int)
partition by range(b)
subpartition by list(c)
subpartition template
(
default subpartition subothers,
subpartition s1 values(1,2,3),
subpartition s2 values(4,5,6)
)
(
default partition others,
start(1) end(5) every(1)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table mpp14613_range(
a int,
b int,
c int,
d int
)
partition by range(b)
subpartition by range(c)
subpartition template
(
default subpartition subothers,
start (1) end(7) every (3)
)
(
default partition others,
start(1) end(5) every(1)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- SPLIT partition
alter table mpp14613_list alter partition others split partition subothers at (10) into (partition b1, partition subothers);
ERROR: SPLIT PARTITION is not currently supported when leaf partition is list partitioned in multi level partition table
alter table mpp14613_range alter partition others split partition subothers at (10) into (partition b1, partition subothers);
ERROR: AT clause cannot be used when splitting a default RANGE partition
-- ALTER TABLE ... ALTER PARTITION ... SPLIT DEFAULT PARTITION
create table foo(
a int,
b int,
c int,
d int)
partition by range(b)
subpartition by list(c)
subpartition template
(
default subpartition subothers,
subpartition s1 values(1,2,3),
subpartition s2 values(4,5,6)
)
(
default partition others,
start(1) end(5) every(1)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
alter table foo alter partition others split partition subothers at (10) into (partition b1, partition subothers);
ERROR: SPLIT PARTITION is not currently supported when leaf partition is list partitioned in multi level partition table
alter table foo alter partition others split partition subothers at (10) into (partition b1, default partition);
ERROR: INTO can only have second partition by name
LINE 1: ...artition others split partition subothers at (10) into (part...
^
alter table foo alter partition others split default partition at (10) into (partition b1, default partition);
ERROR: SPLIT PARTITION is not currently supported when leaf partition is list partitioned in multi level partition table
drop table foo;
--
-- Drop index on a partitioned table. The indexes on the partitions are removed.
--
create table pt_indx_tab (c1 integer, c2 int, c3 text) partition by range (c1) (partition A start (integer '0') end (integer '5') every (integer '1'));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create unique index pt_indx_drop on pt_indx_tab(c1);
select count(*) from pg_index where indrelid='pt_indx_tab'::regclass;
count
-------
1
(1 row)
select count(*) from pg_index where indrelid='pt_indx_tab_1_prt_a_1'::regclass;
count
-------
1
(1 row)
drop index pt_indx_drop;
select count(*) from pg_index where indrelid='pt_indx_tab'::regclass;
count
-------
0
(1 row)
select count(*) from pg_index where indrelid='pt_indx_tab_1_prt_a_1'::regclass;
count
-------
0
(1 row)
--
-- Test changing the datatype of a column in a partitioning key column.
-- (Not supported, throws an error).
--
create table mpp18179 (a int, b int, i int)
distributed by (a)
partition by list (b)
( PARTITION ab1 VALUES (1),
PARTITION ab2 values (2),
default partition other
);
alter table mpp18179 alter column b type varchar(20);
ERROR: cannot alter column "b" because it is part of the partition key of relation "mpp18179"
--
-- Drop index on partitioned table, and recreate it.
--
CREATE TABLE mpp7635_aoi_table2 (id INTEGER)
PARTITION BY RANGE (id)
(START (0) END (200000) EVERY (100000))
;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO mpp7635_aoi_table2(id) VALUES (0);
-- Create index
CREATE INDEX mpp7635_ix3 ON mpp7635_aoi_table2 USING BITMAP (id);
select * from pg_indexes where tablename like 'mpp7635%';
schemaname | tablename | indexname | tablespace | indexdef
------------+----------------------------+-----------------------------------+------------+-------------------------------------------------------------------------------------------------------
public | mpp7635_aoi_table2 | mpp7635_ix3 | | CREATE INDEX mpp7635_ix3 ON ONLY public.mpp7635_aoi_table2 USING bitmap (id)
public | mpp7635_aoi_table2_1_prt_1 | mpp7635_aoi_table2_1_prt_1_id_idx | | CREATE INDEX mpp7635_aoi_table2_1_prt_1_id_idx ON public.mpp7635_aoi_table2_1_prt_1 USING bitmap (id)
public | mpp7635_aoi_table2_1_prt_2 | mpp7635_aoi_table2_1_prt_2_id_idx | | CREATE INDEX mpp7635_aoi_table2_1_prt_2_id_idx ON public.mpp7635_aoi_table2_1_prt_2 USING bitmap (id)
(3 rows)
-- Drop it
DROP INDEX mpp7635_ix3;
select * from pg_indexes where tablename like 'mpp7635%';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
-- Create it again.
CREATE INDEX mpp7635_ix3 ON mpp7635_aoi_table2 (id);
select * from pg_indexes where tablename like 'mpp7635%';
schemaname | tablename | indexname | tablespace | indexdef
------------+----------------------------+-----------------------------------+------------+------------------------------------------------------------------------------------------------------
public | mpp7635_aoi_table2 | mpp7635_ix3 | | CREATE INDEX mpp7635_ix3 ON ONLY public.mpp7635_aoi_table2 USING btree (id)
public | mpp7635_aoi_table2_1_prt_1 | mpp7635_aoi_table2_1_prt_1_id_idx | | CREATE INDEX mpp7635_aoi_table2_1_prt_1_id_idx ON public.mpp7635_aoi_table2_1_prt_1 USING btree (id)
public | mpp7635_aoi_table2_1_prt_2 | mpp7635_aoi_table2_1_prt_2_id_idx | | CREATE INDEX mpp7635_aoi_table2_1_prt_2_id_idx ON public.mpp7635_aoi_table2_1_prt_2 USING btree (id)
(3 rows)
--
-- Test handling of NULL values in SPLIT PARTITION.
--
CREATE TABLE mpp7863 (id int, dat char(8))
DISTRIBUTED BY (id)
PARTITION BY RANGE (dat)
( PARTITION Oct09 START (200910) INCLUSIVE END (200911) EXCLUSIVE ,
PARTITION Nov09 START (200911) INCLUSIVE END (200912) EXCLUSIVE ,
PARTITION Dec09 START (200912) INCLUSIVE END (201001) EXCLUSIVE ,
DEFAULT PARTITION extra);
insert into mpp7863 values(generate_series(1, 100),'200910');
insert into mpp7863 values(generate_series(101, 200),'200911');
insert into mpp7863 values(generate_series(201, 300),'200912');
insert into mpp7863 values(generate_series(301, 30300),'');
insert into mpp7863 (id) values(generate_series(30301, 60300));
insert into mpp7863 values(generate_series(60301, 60400),'201001');
select count(*) from mpp7863_1_prt_extra;
count
-------
60100
(1 row)
select count(*) from mpp7863_1_prt_extra where dat is null;
count
-------
30000
(1 row)
select count(*) from mpp7863_1_prt_extra where dat ='';
count
-------
30000
(1 row)
select count(*) from mpp7863;
count
-------
60400
(1 row)
alter table mpp7863 split default partition start (201001) inclusive end (201002) exclusive into (partition jan10,default partition);
select count(*) from mpp7863_1_prt_extra where dat is null;
count
-------
30000
(1 row)
select count(*) from mpp7863_1_prt_extra where dat ='';
count
-------
30000
(1 row)
select count(*) from mpp7863_1_prt_extra;
count
-------
60000
(1 row)
select dat, count(*) from mpp7863 group by 1 order by 2,1;
dat | count
----------+-------
200910 | 100
200911 | 100
200912 | 100
201001 | 100
| 30000
| 30000
(6 rows)
--
-- Test handling of dropped columns in SPLIT PARTITION. (PR #9386)
--
DROP TABLE IF EXISTS users_test;
NOTICE: table "users_test" does not exist, skipping
CREATE TABLE users_test
(
id INT,
dd TEXT,
user_name VARCHAR(40),
user_email VARCHAR(60),
born_time TIMESTAMP,
create_time TIMESTAMP
)
DISTRIBUTED BY (id)
PARTITION BY RANGE (create_time)
(
PARTITION p2019 START ('2019-01-01'::TIMESTAMP) END ('2020-01-01'::TIMESTAMP),
DEFAULT PARTITION extra
);
-- Drop useless column dd for some reason
ALTER TABLE users_test DROP COLUMN dd;
-- Assume we forgot/failed to split out new partitions beforehand
INSERT INTO users_test VALUES(1, 'A', 'A@abc.com', '1970-01-01', '2019-01-01 12:00:00');
INSERT INTO users_test VALUES(2, 'B', 'B@abc.com', '1980-01-01', '2020-01-01 12:00:00');
INSERT INTO users_test VALUES(3, 'C', 'C@abc.com', '1990-01-01', '2021-01-01 12:00:00');
-- New partition arrives late
ALTER TABLE users_test SPLIT DEFAULT PARTITION START ('2020-01-01'::TIMESTAMP) END ('2021-01-01'::TIMESTAMP)
INTO (PARTITION p2020, DEFAULT PARTITION);
-- Expect A
SELECT user_name FROM users_test_1_prt_p2019;
user_name
-----------
A
(1 row)
-- Expect B
SELECT user_name FROM users_test_1_prt_p2020;
user_name
-----------
B
(1 row)
-- Expect C
SELECT user_name FROM users_test_1_prt_extra;
user_name
-----------
C
(1 row)
-- Github issue: https://github.com/greenplum-db/gpdb/issues/9460
-- When creating unique or primary key index on Partition table,
-- the cols in index must contain all partition keys.
CREATE TABLE t_idx_col_contain_partkey(a int, b int) DISTRIBUTED BY (a)
PARTITION BY list (b)
(PARTITION t1 values (1),
PARTITION t2 values (2));
-- the following statement should fail because index cols does not contain part key
CREATE UNIQUE INDEX uidx_t_idx_col_contain_partkey on t_idx_col_contain_partkey(a);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "t_idx_col_contain_partkey" lacks column "b" which is part of the partition key.
-- the following statement should work
CREATE UNIQUE INDEX uidx_t_idx_col_contain_partkey on t_idx_col_contain_partkey(a, b);
DROP INDEX uidx_t_idx_col_contain_partkey;
DROP TABLE t_idx_col_contain_partkey;
-- test unique index for multi level partition table
CREATE TABLE t_idx_col_contain_partkey
(
r_regionkey integer not null,
r_name char(25),
r_comment varchar(152)
)
DISTRIBUTED BY (r_regionkey)
PARTITION BY RANGE (r_regionkey)
SUBPARTITION BY LIST (r_name) SUBPARTITION TEMPLATE
(
SUBPARTITION africa VALUES ('AFRICA'),
SUBPARTITION america VALUES ('AMERICA'),
SUBPARTITION asia VALUES ('ASIA'),
SUBPARTITION europe VALUES ('EUROPE'),
SUBPARTITION mideast VALUES ('MIDDLE EAST'),
SUBPARTITION australia VALUES ('AUSTRALIA'),
SUBPARTITION antarctica VALUES ('ANTARCTICA')
)
(
PARTITION region1 start (0),
PARTITION region2 start (3),
PARTITION region3 start (5) end (8)
);
-- should fail, must contain all the partition keys of all levels
CREATE UNIQUE INDEX uidx_t_idx_col_contain_partkey on t_idx_col_contain_partkey(r_regionkey);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "t_idx_col_contain_partkey_1_prt_region1" lacks column "r_name" which is part of the partition key.
-- should work
CREATE UNIQUE INDEX uidx_t_idx_col_contain_partkey on t_idx_col_contain_partkey(r_regionkey, r_name);
DROP INDEX uidx_t_idx_col_contain_partkey;
DROP TABLE t_idx_col_contain_partkey;
--
-- Test EXCHANGE PARTITION, when the new table has different CHECK constraints
--
CREATE TABLE constraint_mismatch_tbl (
id int,
date date,
amt decimal(10,2)
CONSTRAINT amt_check CHECK (amt > 0)
) DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
(PARTITION Jan08 START (date '2008-01-01'),
PARTITION Feb08 START (date '2008-02-01'),
PARTITION Mar08 START (date '2008-03-01') END (date '2008-04-01'));
-- fail: new table doesn't have 'amt_check' constraint
CREATE TABLE mismatch_exchange_tbl (
id int,
date date,
amt decimal(10,2)
) DISTRIBUTED BY (id);
INSERT INTO mismatch_exchange_tbl SELECT i, '2008-03-02', i FROM generate_series(11,15)i;
ALTER TABLE constraint_mismatch_tbl EXCHANGE PARTITION mar08 WITH TABLE mismatch_exchange_tbl;
ERROR: child table is missing constraint "amt_check"
-- fail: new table has a constraint called 'amt_check', but it's different from the parent's
DROP TABLE mismatch_exchange_tbl;
CREATE TABLE mismatch_exchange_tbl (
id int,
date date,
amt decimal(10,2)
CONSTRAINT amt_check CHECK (amt <> 0)
) DISTRIBUTED BY (id);
INSERT INTO mismatch_exchange_tbl SELECT i, '2008-03-02', i FROM generate_series(11,15)i;
ALTER TABLE constraint_mismatch_tbl EXCHANGE PARTITION mar08 WITH TABLE mismatch_exchange_tbl;
ERROR: child table "mismatch_exchange_tbl" has different definition for check constraint "amt_check"
-- success: new table has compatible 'amt_check' constraint
DROP TABLE mismatch_exchange_tbl;
CREATE TABLE mismatch_exchange_tbl (
id int,
date date,
amt decimal(10,2)
CONSTRAINT amt_check CHECK (amt > 0)
) DISTRIBUTED BY (id);
INSERT INTO mismatch_exchange_tbl SELECT i, '2008-03-02', i FROM generate_series(11,15)i;
ALTER TABLE constraint_mismatch_tbl EXCHANGE PARTITION mar08 WITH TABLE mismatch_exchange_tbl;
--
-- END INCLUSIVE should work for CREATE, ADD PARTITION, and SPLIT PARTITION for
-- the following data types. The INCLUSIVE END value will be converted to an
-- EXCLUSIVE upper bound during transformation. If the INCLUSIVE END value is
-- smaller than the maximum value of the data type, the exclusive upper bound
-- will be the END INCLUSIVE value + '1', where '1' is the resolution of the
-- data type. Otherwise, MAXVALUE will be stored as the upper bound.
--
-- END INCLUSIVE should work for bigint
CREATE TABLE end_inclusive_bigint (a int, b bigint)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION pmax_create START (9223372036854775805) END (9223372036854775807) INCLUSIVE EVERY (1),
PARTITION p1 START (1) END (3) INCLUSIVE,
PARTITION p20 START (20),
DEFAULT PARTITION other
);
ALTER TABLE end_inclusive_bigint SPLIT DEFAULT PARTITION START (7) END (10) INCLUSIVE INTO (PARTITION p7, DEFAULT PARTITION);
\d+ end_inclusive_bigint
Partitioned table "public.end_inclusive_bigint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | bigint | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_bigint_1_prt_p1 FOR VALUES FROM ('1') TO ('4'),
end_inclusive_bigint_1_prt_p20 FOR VALUES FROM ('20') TO ('9223372036854775805'),
end_inclusive_bigint_1_prt_p7 FOR VALUES FROM ('7') TO ('11'),
end_inclusive_bigint_1_prt_pmax_create_1 FOR VALUES FROM ('9223372036854775805') TO ('9223372036854775806'),
end_inclusive_bigint_1_prt_pmax_create_2 FOR VALUES FROM ('9223372036854775806') TO (MAXVALUE),
end_inclusive_bigint_1_prt_other DEFAULT
Distributed by: (a)
ALTER TABLE end_inclusive_bigint DROP PARTITION pmax_create_1;
ALTER TABLE end_inclusive_bigint DROP PARTITION pmax_create_2;
ALTER TABLE end_inclusive_bigint ADD PARTITION pmax_add START (9223372036854775805) END (9223372036854775807) INCLUSIVE;
\d+ end_inclusive_bigint
Partitioned table "public.end_inclusive_bigint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | bigint | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_bigint_1_prt_p1 FOR VALUES FROM ('1') TO ('4'),
end_inclusive_bigint_1_prt_p20 FOR VALUES FROM ('20') TO ('9223372036854775805'),
end_inclusive_bigint_1_prt_p7 FOR VALUES FROM ('7') TO ('11'),
end_inclusive_bigint_1_prt_pmax_add FOR VALUES FROM ('9223372036854775805') TO (MAXVALUE),
end_inclusive_bigint_1_prt_other DEFAULT
Distributed by: (a)
ALTER TABLE end_inclusive_bigint DROP PARTITION pmax_add;
ALTER TABLE end_inclusive_bigint SPLIT DEFAULT PARTITION START (9223372036854775805) END (9223372036854775807) INCLUSIVE INTO (PARTITION pmax_split, DEFAULT PARTITION);
\d+ end_inclusive_bigint
Partitioned table "public.end_inclusive_bigint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | bigint | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_bigint_1_prt_p1 FOR VALUES FROM ('1') TO ('4'),
end_inclusive_bigint_1_prt_p20 FOR VALUES FROM ('20') TO ('9223372036854775805'),
end_inclusive_bigint_1_prt_p7 FOR VALUES FROM ('7') TO ('11'),
end_inclusive_bigint_1_prt_pmax_split FOR VALUES FROM ('9223372036854775805') TO (MAXVALUE),
end_inclusive_bigint_1_prt_other DEFAULT
Distributed by: (a)
-- END INCLUSIVE should work for int
CREATE TABLE end_inclusive_int (a int, b int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 END (3) INCLUSIVE,
PARTITION pmax END (2147483647) INCLUSIVE
);
\d+ end_inclusive_int
Partitioned table "public.end_inclusive_int"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_int_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (4),
end_inclusive_int_1_prt_pmax FOR VALUES FROM (4) TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for smallint
CREATE TABLE end_inclusive_smallint (a int, b smallint)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START (1) END (3) INCLUSIVE,
PARTITION pmax START (4) END (32767) INCLUSIVE
);
\d+ end_inclusive_smallint
Partitioned table "public.end_inclusive_smallint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | smallint | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_smallint_1_prt_p1 FOR VALUES FROM ('1') TO ('4'),
end_inclusive_smallint_1_prt_pmax FOR VALUES FROM ('4') TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for date
CREATE TABLE end_inclusive_date (a int, b date)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('2020-06-16') END ('2020-06-17') INCLUSIVE,
PARTITION pmax START ('2020-06-18') END ('infinity') INCLUSIVE
);
\d+ end_inclusive_date
Partitioned table "public.end_inclusive_date"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | date | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_date_1_prt_p1 FOR VALUES FROM ('06-16-2020') TO ('06-18-2020'),
end_inclusive_date_1_prt_pmax FOR VALUES FROM ('06-18-2020') TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for time without time zone
CREATE TABLE end_inclusive_time (a int, b time)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('00:00:00.000001') END ('01:00:00') INCLUSIVE,
PARTITION pmax START ('23:00:00') END ('24:00:00') INCLUSIVE
);
\d+ end_inclusive_time
Partitioned table "public.end_inclusive_time"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | time without time zone | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_time_1_prt_p1 FOR VALUES FROM ('00:00:00.000001') TO ('01:00:00.000001'),
end_inclusive_time_1_prt_pmax FOR VALUES FROM ('23:00:00') TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for time with time zone
CREATE TABLE end_inclusive_timetz (a int, b time with time zone)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('00:00:00 EST') END ('01:00:00 PST') INCLUSIVE,
PARTITION pmax START ('23:00:00 EST') END ('24:00:00 PST') INCLUSIVE
);
\d+ end_inclusive_timetz
Partitioned table "public.end_inclusive_timetz"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | time with time zone | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_timetz_1_prt_p1 FOR VALUES FROM ('00:00:00-05') TO ('01:00:00.000001-08'),
end_inclusive_timetz_1_prt_pmax FOR VALUES FROM ('23:00:00-05') TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for timestamp without time zone
CREATE TABLE end_inclusive_timestamp (a int, b timestamp)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('2020-06-16 00:00:00') END ('2020-06-16 01:00:00') INCLUSIVE,
PARTITION pmax START ('2020-06-16 23:00:00') END ('infinity') INCLUSIVE
);
\d+ end_inclusive_timestamp
Partitioned table "public.end_inclusive_timestamp"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | timestamp without time zone | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_timestamp_1_prt_p1 FOR VALUES FROM ('Tue Jun 16 00:00:00 2020') TO ('Tue Jun 16 01:00:00.000001 2020'),
end_inclusive_timestamp_1_prt_pmax FOR VALUES FROM ('Tue Jun 16 23:00:00 2020') TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for timestamp with time zone
CREATE TABLE end_inclusive_timestamptz (a int, b timestamp with time zone)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('2020-06-16 00:00:00 PST') END ('2020-06-16 01:00:00 PST') INCLUSIVE,
PARTITION pmax START ('2020-06-16 23:00:00 EST') END ('infinity') INCLUSIVE
);
\d+ end_inclusive_timestamptz
Partitioned table "public.end_inclusive_timestamptz"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | timestamp with time zone | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_timestamptz_1_prt_p1 FOR VALUES FROM ('Tue Jun 16 01:00:00 2020 PDT') TO ('Tue Jun 16 02:00:00.000001 2020 PDT'),
end_inclusive_timestamptz_1_prt_pmax FOR VALUES FROM ('Tue Jun 16 21:00:00 2020 PDT') TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should work for interval
CREATE TABLE end_inclusive_interval (a int, b interval)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('1 year') END ('2 years') INCLUSIVE
);
\d+ end_inclusive_interval
Partitioned table "public.end_inclusive_interval"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | interval | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_interval_1_prt_p1 FOR VALUES FROM ('@ 1 year') TO ('@ 2 years 0.000001 secs')
Distributed by: (a)
-- END INCLUSIVE with MAXVALUE should work with implicit START/END
DROP TABLE end_inclusive_int;
CREATE TABLE end_inclusive_int (a int, b int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START (1),
PARTITION pmax END (2147483647) INCLUSIVE,
PARTITION p2 START (2) END (5) INCLUSIVE
);
\d+ end_inclusive_int
Partitioned table "public.end_inclusive_int"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_int_1_prt_p1 FOR VALUES FROM (1) TO (2),
end_inclusive_int_1_prt_p2 FOR VALUES FROM (2) TO (6),
end_inclusive_int_1_prt_pmax FOR VALUES FROM (6) TO (MAXVALUE)
Distributed by: (a)
DROP TABLE end_inclusive_int;
CREATE TABLE end_inclusive_int (a int, b int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION pmax END (2147483647) INCLUSIVE,
PARTITION p1 START (1),
PARTITION p2 START (2) END (5) INCLUSIVE
);
\d+ end_inclusive_int
Partitioned table "public.end_inclusive_int"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
Partition key: RANGE (b)
Partitions: end_inclusive_int_1_prt_p1 FOR VALUES FROM (1) TO (2),
end_inclusive_int_1_prt_p2 FOR VALUES FROM (2) TO (6),
end_inclusive_int_1_prt_pmax FOR VALUES FROM (6) TO (MAXVALUE)
Distributed by: (a)
-- END INCLUSIVE should fail when precision is specified
CREATE TABLE end_inclusive_time_with_precision (a int, b time(5))
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START ('00:00:00') END ('01:00:00') INCLUSIVE
);
ERROR: END INCLUSIVE not supported when partition key has precision specification: time(5) without time zone
HINT: Specify an exclusive END value and remove the INCLUSIVE keyword
-- END INCLUSIVE should fail for unsupported data types
CREATE TABLE end_inclusive_numeric (a int, b numeric)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START (1) END (3) INCLUSIVE
);
ERROR: END INCLUSIVE not supported for partition key data type: numeric
HINT: Specify an exclusive END value and remove the INCLUSIVE keyword
-- Also check START EXCLUSIVE
CREATE TABLE start_exclusive_smallint (a int, b smallint)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START (0) EXCLUSIVE END (3) INCLUSIVE,
PARTITION pmax START (4) EXCLUSIVE
);
\d+ start_exclusive_smallint
Partitioned table "public.start_exclusive_smallint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | smallint | | | | plain | |
Partition key: RANGE (b)
Partitions: start_exclusive_smallint_1_prt_p1 FOR VALUES FROM ('1') TO ('4'),
start_exclusive_smallint_1_prt_pmax FOR VALUES FROM ('5') TO (MAXVALUE)
Distributed by: (a)
-- If the START EXCLUSIVE value + 1 would overflow, you get an error
CREATE TABLE start_exclusive_smallint_overflow (a int, b smallint)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
(
PARTITION p1 START (0) EXCLUSIVE END (3) INCLUSIVE,
PARTITION pmax START (32767) EXCLUSIVE
);
ERROR: START EXCLUSIVE is out of range
LINE 6: PARTITION pmax START (32767) EXCLUSIVE
^
-- Test for ALTER TABLE WITH/WITHOUT VALIDATION.
-- It doesn't do anything anymore, but check that the syntax is accepted.
CREATE TABLE validation_syntax_tbl (a int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (a)
(
PARTITION p1 START (1) END (3)
);
CREATE TABLE exchange_tbl (a int4);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO exchange_tbl VALUES (100);
ALTER TABLE validation_syntax_tbl EXCHANGE PARTITION p1 WITH TABLE exchange_tbl WITH VALIDATION;
NOTICE: specifying "WITH VALIDATION" acts as no operation
DETAIL: If the new partition is a regular table, validation is performed to make sure all the rows obey partition constraint. If the new partition is external or foreign table, no validation is performed.
ERROR: partition constraint of relation "exchange_tbl" is violated by some row (seg2 127.0.0.1:7004 pid=4188949)
ALTER TABLE validation_syntax_tbl EXCHANGE PARTITION p1 WITH TABLE exchange_tbl WITHOUT VALIDATION;
NOTICE: specifying "WITHOUT VALIDATION" acts as no operation
DETAIL: If the new partition is a regular table, validation is performed to make sure all the rows obey partition constraint. If the new partition is external or foreign table, no validation is performed.
ERROR: partition constraint of relation "exchange_tbl" is violated by some row (seg2 127.0.0.1:7004 pid=4188949)
DROP TABLE exchange_tbl;
DROP TABLE validation_syntax_tbl;
--
-- Test a case where the automatically created partition name clashes with
-- another table or partition.
-- Before GPDB 7, the automatic table name generation used check if the name is
-- in use, and pick another name to avoid the clash. It's not as smart anymore.
-- It's more tricky now, because e.g. the ALTER TABLE ALTER/DROP/ADD PARTITION
-- commands rely on the deterministic naming of the partitions. If a user runs
-- into this, the work around is to use different table/partition names, or
-- use the upstream syntax and name each partition explicitly.
--
CREATE TABLE partitioned_table_with_very_long_name_123456789x
(
col1 int4,
col2 int4
)
DISTRIBUTED by (col1)
PARTITION BY RANGE(col2)
(partition partone start(1) end(100000001),
partition parttwo start(100000001) end(200000001),
partition partthree start(200000001) end(300000001));
CREATE TABLE partitioned_table_with_very_long_name_123456789y
(
col1 int4,
col2 int4
)
DISTRIBUTED by (col1)
PARTITION BY RANGE(col2)
(partition partone start(1) end(100000001),
partition parttwo start(100000001) end(200000001),
partition partthree start(200000001) end(300000001));
ERROR: relation "partitioned_table_with_very_long_name_123456789_1_prt_partthree" already exists