path: root/src/test/regress/expected/create_table.out

--
-- CREATE_TABLE
--
-- Error cases
CREATE TABLE unknowntab (
	u unknown    -- fail
);
ERROR:  column "u" has pseudo-type unknown
CREATE TYPE unknown_comptype AS (
	u unknown    -- fail
);
ERROR:  column "u" has pseudo-type unknown
-- invalid: non-lowercase quoted reloptions identifiers
CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a;
ERROR:  unrecognized parameter "Fillfactor"
CREATE UNLOGGED TABLE unlogged1 (a int primary key);			-- OK
CREATE TEMPORARY TABLE unlogged2 (a int primary key);			-- OK
SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
    relname     | relkind | relpersistence 
----------------+---------+----------------
 unlogged1      | r       | u
 unlogged1_pkey | i       | u
 unlogged2      | r       | t
 unlogged2_pkey | i       | t
(4 rows)

REINDEX INDEX unlogged1_pkey;
REINDEX INDEX unlogged2_pkey;
SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
    relname     | relkind | relpersistence 
----------------+---------+----------------
 unlogged1      | r       | u
 unlogged1_pkey | i       | u
 unlogged2      | r       | t
 unlogged2_pkey | i       | t
(4 rows)

DROP TABLE unlogged2;
INSERT INTO unlogged1 VALUES (42);
CREATE UNLOGGED TABLE public.unlogged2 (a int primary key);		-- also OK
CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key);	-- not OK
ERROR:  only temporary relations may be created in temporary schemas
LINE 1: CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key);
                              ^
CREATE TABLE pg_temp.implicitly_temp (a int primary key);		-- OK
CREATE TEMP TABLE explicitly_temp (a int primary key);			-- also OK
CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key);		-- also OK
CREATE TEMP TABLE public.temp_to_perm (a int primary key);		-- not OK
ERROR:  cannot create temporary relation in non-temporary schema
LINE 1: CREATE TEMP TABLE public.temp_to_perm (a int primary key);
                          ^
DROP TABLE unlogged1, public.unlogged2;
CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
ERROR:  relation "as_select1" already exists
CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
NOTICE:  relation "as_select1" already exists, skipping
DROP TABLE as_select1;
PREPARE select1 AS SELECT 1 as a;
CREATE TABLE as_select1 AS EXECUTE select1;
CREATE TABLE as_select1 AS EXECUTE select1;
ERROR:  relation "as_select1" already exists
SELECT * FROM as_select1;
 a 
---
 1
(1 row)

CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1;
NOTICE:  relation "as_select1" already exists, skipping
DROP TABLE as_select1;
DEALLOCATE select1;
-- create an extra wide table to test for issues related to that
-- (temporarily hide query, to avoid the long CREATE TABLE stmt)
\set ECHO none
INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col');
SELECT firstc, lastc FROM extra_wide_table;
  firstc   |  lastc   
-----------+----------
 first col | last col
(1 row)

-- check that tables with oids cannot be created anymore
CREATE TABLE withoid() WITH OIDS;
ERROR:  syntax error at or near "OIDS"
LINE 1: CREATE TABLE withoid() WITH OIDS;
                                    ^
CREATE TABLE withoid() WITH (oids);
ERROR:  tables declared WITH OIDS are not supported
CREATE TABLE withoid() WITH (oids = true);
ERROR:  tables declared WITH OIDS are not supported
-- but explicitly not adding oids is still supported
CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
-- check restriction with default expressions
-- invalid use of column reference in default expressions
CREATE TABLE default_expr_column (id int DEFAULT (id));
ERROR:  cannot use column reference in DEFAULT expression
LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (id));
                                                          ^
CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
ERROR:  cannot use column reference in DEFAULT expression
LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
                                                          ^
CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
ERROR:  cannot use column reference in DEFAULT expression
LINE 1: ...TE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
                                                                 ^
-- invalid column definition
CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
ERROR:  cannot use column reference in DEFAULT expression
LINE 1: ...TABLE default_expr_non_column (a int DEFAULT (avg(non_existe...
                                                             ^
-- invalid use of aggregate
CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
ERROR:  aggregate functions are not allowed in DEFAULT expressions
LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
                                                      ^
-- invalid use of subquery
CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
ERROR:  cannot use subquery in DEFAULT expression
LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
                                                     ^
-- invalid use of set-returning function
CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
ERROR:  set-returning functions are not allowed in DEFAULT expressions
LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (generate_serie...
                                                      ^
-- Verify that subtransaction rollback restores rd_createSubid.
BEGIN;
CREATE TABLE remember_create_subid (c int);
SAVEPOINT q; DROP TABLE remember_create_subid; ROLLBACK TO q;
COMMIT;
DROP TABLE remember_create_subid;
-- Verify that subtransaction rollback restores rd_firstRelfilenodeSubid.
CREATE TABLE remember_node_subid (c int);
BEGIN;
ALTER TABLE remember_node_subid ALTER c TYPE bigint;
SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q;
COMMIT;
DROP TABLE remember_node_subid;
--
-- Partitioned tables
--
-- cannot combine INHERITS and PARTITION BY (although grammar allows)
CREATE TABLE partitioned (
	a int
) INHERITS (some_table) PARTITION BY LIST (a);
ERROR:  cannot create partitioned table as inheritance child
-- cannot use more than 1 column as partition key for list partitioned table
CREATE TABLE partitioned (
	a1 int,
	a2 int
) PARTITION BY LIST (a1, a2);	-- fail
ERROR:  cannot use "list" partition strategy with more than one column
-- unsupported constraint type for partitioned tables
CREATE TABLE partitioned (
	a int,
	EXCLUDE USING gist (a WITH &&)
) PARTITION BY RANGE (a);
ERROR:  exclusion constraints are not supported on partitioned tables
LINE 3:  EXCLUDE USING gist (a WITH &&)
         ^
-- prevent using prohibited expressions in the key
CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE (retset(a));
ERROR:  set-returning functions are not allowed in partition key expressions
DROP FUNCTION retset(int);
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE ((avg(a)));
ERROR:  aggregate functions are not allowed in partition key expressions
CREATE TABLE partitioned (
	a int,
	b int
) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b)));
ERROR:  window functions are not allowed in partition key expressions
CREATE TABLE partitioned (
	a int
) PARTITION BY LIST ((a LIKE (SELECT 1)));
ERROR:  cannot use subquery in partition key expression
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE ((42));
ERROR:  cannot use constant expression as partition key
CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE (const_func());
ERROR:  cannot use constant expression as partition key
DROP FUNCTION const_func();
-- only accept valid partitioning strategy
CREATE TABLE partitioned (
    a int
) PARTITION BY MAGIC (a);
ERROR:  unrecognized partitioning strategy "magic"
-- specified column must be present in the table
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE (b);
ERROR:  column "b" named in partition key does not exist
LINE 3: ) PARTITION BY RANGE (b);
                              ^
-- cannot use system columns in partition key
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE (xmin);
ERROR:  cannot use system column "xmin" in partition key
LINE 3: ) PARTITION BY RANGE (xmin);
                              ^
-- cannot use pseudotypes
CREATE TABLE partitioned (
	a int,
	b int
) PARTITION BY RANGE (((a, b)));
ERROR:  partition key column 1 has pseudo-type record
CREATE TABLE partitioned (
	a int,
	b int
) PARTITION BY RANGE (a, ('unknown'));
ERROR:  partition key column 2 has pseudo-type unknown
-- functions in key must be immutable
CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL;
CREATE TABLE partitioned (
	a int
) PARTITION BY RANGE (immut_func(a));
ERROR:  functions in partition key expression must be marked IMMUTABLE
DROP FUNCTION immut_func(int);
-- prevent using columns of unsupported types in key (type must have a btree operator class)
CREATE TABLE partitioned (
	a point
) PARTITION BY LIST (a);
ERROR:  data type point has no default operator class for access method "btree"
HINT:  You must specify a btree operator class or define a default btree operator class for the data type.
CREATE TABLE partitioned (
	a point
) PARTITION BY LIST (a point_ops);
ERROR:  operator class "point_ops" does not exist for access method "btree"
CREATE TABLE partitioned (
	a point
) PARTITION BY RANGE (a);
ERROR:  data type point has no default operator class for access method "btree"
HINT:  You must specify a btree operator class or define a default btree operator class for the data type.
CREATE TABLE partitioned (
	a point
) PARTITION BY RANGE (a point_ops);
ERROR:  operator class "point_ops" does not exist for access method "btree"
-- cannot add NO INHERIT constraints to partitioned tables
CREATE TABLE partitioned (
	a int,
	CONSTRAINT check_a CHECK (a > 0) NO INHERIT
) PARTITION BY RANGE (a);
ERROR:  cannot add NO INHERIT constraint to partitioned table "partitioned"
-- some checks after successful creation of a partitioned table
CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL;
CREATE TABLE partitioned (
	a int,
	b int,
	c text,
	d text
) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C");
-- check relkind
SELECT relkind FROM pg_class WHERE relname = 'partitioned';
 relkind 
---------
 p
(1 row)

-- prevent a function referenced in partition key from being dropped
DROP FUNCTION plusone(int);
ERROR:  cannot drop function plusone(integer) because other objects depend on it
DETAIL:  table partitioned depends on function plusone(integer)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- partitioned table cannot participate in regular inheritance
CREATE TABLE partitioned2 (
	a int,
	b text
) PARTITION BY RANGE ((a+1), substr(b, 1, 5));
CREATE TABLE fail () INHERITS (partitioned2);
ERROR:  cannot inherit from partitioned table "partitioned2"
-- Partition key in describe output
\d partitioned
      Partitioned table "public.partitioned"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | text    |           |          | 
 d      | text    |           |          | 
Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")
Number of partitions: 0

\d+ partitioned2
                          Partitioned table "public.partitioned2"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              | 
 b      | text    |           |          |         | extended |              | 
Partition key: RANGE (((a + 1)), substr(b, 1, 5))
Number of partitions: 0

INSERT INTO partitioned2 VALUES (1, 'hello');
ERROR:  no partition of relation "partitioned2" found for row
DETAIL:  Partition key of the failing row contains ((a + 1), substr(b, 1, 5)) = (2, hello).
CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc');
\d+ part2_1
                                  Table "public.part2_1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           |          |         | plain    |              | 
 b      | text    |           |          |         | extended |              | 
Partition of: partitioned2 FOR VALUES FROM ('-1', 'aaaaa') TO (100, 'ccccc')
Partition constraint: (((a + 1) IS NOT NULL) AND (substr(b, 1, 5) IS NOT NULL) AND (((a + 1) > '-1'::integer) OR (((a + 1) = '-1'::integer) AND (substr(b, 1, 5) >= 'aaaaa'::text))) AND (((a + 1) < 100) OR (((a + 1) = 100) AND (substr(b, 1, 5) < 'ccccc'::text))))

DROP TABLE partitioned, partitioned2;
-- check reference to partitioned table's rowtype in partition descriptor
create table partitioned (a int, b int)
  partition by list ((row(a, b)::partitioned));
create table partitioned1
  partition of partitioned for values in ('(1,2)'::partitioned);
create table partitioned2
  partition of partitioned for values in ('(2,4)'::partitioned);
explain (costs off)
select * from partitioned where row(a,b)::partitioned = '(1,2)'::partitioned;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on partitioned1 partitioned
   Filter: (ROW(a, b)::partitioned = '(1,2)'::partitioned)
(2 rows)

drop table partitioned;
-- whole-row Var in partition key works too
create table partitioned (a int, b int)
  partition by list ((partitioned));
create table partitioned1
  partition of partitioned for values in ('(1,2)');
create table partitioned2
  partition of partitioned for values in ('(2,4)');
explain (costs off)
select * from partitioned where partitioned = '(1,2)'::partitioned;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on partitioned1 partitioned
   Filter: ((partitioned.*)::partitioned = '(1,2)'::partitioned)
(2 rows)

\d+ partitioned1
                               Table "public.partitioned1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
Partition of: partitioned FOR VALUES IN ('(1,2)')
Partition constraint: (((partitioned1.*)::partitioned IS DISTINCT FROM NULL) AND ((partitioned1.*)::partitioned = '(1,2)'::partitioned))

drop table partitioned;
-- check that dependencies of partition columns are handled correctly
create domain intdom1 as int;
create table partitioned (
	a intdom1,
	b text
) partition by range (a);
alter table partitioned drop column a;  -- fail
ERROR:  cannot drop column "a" because it is part of the partition key of relation "partitioned"
drop domain intdom1;  -- fail, requires cascade
ERROR:  cannot drop type intdom1 because other objects depend on it
DETAIL:  table partitioned depends on type intdom1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
drop domain intdom1 cascade;
NOTICE:  drop cascades to table partitioned
table partitioned;  -- gone
ERROR:  relation "partitioned" does not exist
LINE 1: table partitioned;
              ^
-- likewise for columns used in partition expressions
create domain intdom1 as int;
create table partitioned (
	a intdom1,
	b text
) partition by range (plusone(a));
alter table partitioned drop column a;  -- fail
ERROR:  cannot drop column "a" because it is part of the partition key of relation "partitioned"
drop domain intdom1;  -- fail, requires cascade
ERROR:  cannot drop type intdom1 because other objects depend on it
DETAIL:  table partitioned depends on type intdom1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
drop domain intdom1 cascade;
NOTICE:  drop cascades to table partitioned
table partitioned;  -- gone
ERROR:  relation "partitioned" does not exist
LINE 1: table partitioned;
              ^
--
-- Partitions
--
-- check partition bound syntax
CREATE TABLE list_parted (
	a int
) PARTITION BY LIST (a);
CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1');
CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2);
CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1));
CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
\d+ list_parted
                          Partitioned table "public.list_parted"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: LIST (a)
Partitions: part_null FOR VALUES IN (NULL),
            part_p1 FOR VALUES IN (1),
            part_p2 FOR VALUES IN (2),
            part_p3 FOR VALUES IN (3)

-- forbidden expressions for partition bound with list partitioned table
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
ERROR:  cannot use column reference in partition bound expression
LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
                                                             ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename);
ERROR:  cannot use column reference in partition bound expression
LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename.s...
                                                             ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
ERROR:  cannot use column reference in partition bound expression
LINE 1: ..._bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
                                                                    ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
ERROR:  cannot use column reference in partition bound expression
LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
                                                                   ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename));
ERROR:  cannot use column reference in partition bound expression
LINE 1: ..._fail PARTITION OF list_parted FOR VALUES IN (sum(somename))...
                                                             ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
ERROR:  aggregate functions are not allowed in partition bound
LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
                                                               ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1));
ERROR:  cannot use subquery in partition bound
LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1)...
                                                             ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (generate_series(4, 6));
ERROR:  set-returning functions are not allowed in partition bound
LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (generate_s...
                                                             ^
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "POSIX");
ERROR:  collations are not supported by type integer
LINE 1: ...ail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "P...
                                                             ^
-- syntax does not allow empty list of values for list partitions
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
ERROR:  syntax error at or near ")"
LINE 1: ...E TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
                                                                     ^
-- trying to specify range for list partitioned table
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
ERROR:  invalid bound specification for a list partition
LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) T...
                                                             ^
-- trying to specify modulus and remainder for list partitioned table
CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
ERROR:  invalid bound specification for a list partition
LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODU...
                                                             ^
-- check default partition cannot be created more than once
CREATE TABLE part_default PARTITION OF list_parted DEFAULT;
CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
ERROR:  partition "fail_default_part" conflicts with existing default partition "part_default"
LINE 1: ...TE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
                                                               ^
-- specified literal can't be cast to the partition column data type
CREATE TABLE bools (
	a bool
) PARTITION BY LIST (a);
CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
ERROR:  specified value cannot be cast to type boolean for column "a"
LINE 1: ...REATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
                                                                    ^
DROP TABLE bools;
-- specified literal can be cast, and the cast might not be immutable
CREATE TABLE moneyp (
	a money
) PARTITION BY LIST (a);
CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11');
CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int);
DROP TABLE moneyp;
-- cast is immutable
CREATE TABLE bigintp (
	a bigint
) PARTITION BY LIST (a);
CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10);
-- fails due to overlap:
CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10');
ERROR:  partition "bigintp_10_2" would overlap partition "bigintp_10"
LINE 1: ...ABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10');
                                                                 ^
DROP TABLE bigintp;
CREATE TABLE range_parted (
	a date
) PARTITION BY RANGE (a);
-- forbidden expressions for partition bounds with range partitioned table
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (somename) TO ('2019-01-01');
ERROR:  cannot use column reference in partition bound expression
LINE 2:   FOR VALUES FROM (somename) TO ('2019-01-01');
                           ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (somename.somename) TO ('2019-01-01');
ERROR:  cannot use column reference in partition bound expression
LINE 2:   FOR VALUES FROM (somename.somename) TO ('2019-01-01');
                           ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (a) TO ('2019-01-01');
ERROR:  cannot use column reference in partition bound expression
LINE 2:   FOR VALUES FROM (a) TO ('2019-01-01');
                           ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (max(a)) TO ('2019-01-01');
ERROR:  cannot use column reference in partition bound expression
LINE 2:   FOR VALUES FROM (max(a)) TO ('2019-01-01');
                               ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (max(somename)) TO ('2019-01-01');
ERROR:  cannot use column reference in partition bound expression
LINE 2:   FOR VALUES FROM (max(somename)) TO ('2019-01-01');
                               ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01');
ERROR:  aggregate functions are not allowed in partition bound
LINE 2:   FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01'...
                           ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM ((select 1)) TO ('2019-01-01');
ERROR:  cannot use subquery in partition bound
LINE 2:   FOR VALUES FROM ((select 1)) TO ('2019-01-01');
                           ^
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
  FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01');
ERROR:  set-returning functions are not allowed in partition bound
LINE 2:   FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01');
                           ^
-- trying to specify list for range partitioned table
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
ERROR:  invalid bound specification for a range partition
LINE 1: ...BLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
                                                              ^
-- trying to specify modulus and remainder for range partitioned table
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
ERROR:  invalid bound specification for a range partition
LINE 1: ...LE fail_part PARTITION OF range_parted FOR VALUES WITH (MODU...
                                                             ^
-- each of start and end bounds must have same number of values as the
-- length of the partition key
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
ERROR:  FROM must specify exactly one value per partitioning column
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
ERROR:  TO must specify exactly one value per partitioning column
-- cannot specify null values in range bounds
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue);
ERROR:  cannot specify NULL in range bound
-- trying to specify modulus and remainder for range partitioned table
CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
ERROR:  invalid bound specification for a range partition
LINE 1: ...LE fail_part PARTITION OF range_parted FOR VALUES WITH (MODU...
                                                             ^
-- check partition bound syntax for the hash partition
CREATE TABLE hash_parted (
	a int
) PARTITION BY HASH (a);
CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0);
CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1);
CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2);
CREATE TABLE hpart_4 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 3);
-- modulus 25 is factor of modulus of 50 but 10 is not a factor of 25.
CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 25, REMAINDER 3);
ERROR:  every hash partition modulus must be a factor of the next larger modulus
DETAIL:  The new modulus 25 is not divisible by 10, the modulus of existing partition "hpart_4".
-- previous modulus 50 is factor of 150 but this modulus is not a factor of next modulus 200.
CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3);
ERROR:  every hash partition modulus must be a factor of the next larger modulus
DETAIL:  The new modulus 150 is not a factor of 200, the modulus of existing partition "hpart_3".
-- overlapping remainders
CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 100, REMAINDER 3);
ERROR:  partition "fail_part" would overlap partition "hpart_4"
LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODU...
                                                             ^
-- trying to specify range for the hash partitioned table
CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
ERROR:  invalid bound specification for a hash partition
LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a',...
                                                             ^
-- trying to specify list value for the hash partitioned table
CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
ERROR:  invalid bound specification for a hash partition
LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
                                                             ^
-- trying to create default partition for the hash partitioned table
CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT;
ERROR:  a hash-partitioned table may not have a default partition
-- check if compatible with the specified parent
-- cannot create as partition of a non-partitioned table
CREATE TABLE unparted (
	a int
);
CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
ERROR:  "unparted" is not partitioned
CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1);
ERROR:  "unparted" is not partitioned
DROP TABLE unparted;
-- cannot create a permanent rel as partition of a temp rel
CREATE TEMP TABLE temp_parted (
	a int
) PARTITION BY LIST (a);
CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
ERROR:  cannot create a permanent relation as partition of temporary relation "temp_parted"
DROP TABLE temp_parted;
-- check for partition bound overlap and other invalid specifications
CREATE TABLE list_parted2 (
	a varchar
) PARTITION BY LIST (a);
CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z');
CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b');
CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
ERROR:  partition "fail_part" would overlap partition "part_null_z"
LINE 1: ...LE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
                                                                 ^
CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
ERROR:  partition "fail_part" would overlap partition "part_ab"
LINE 1: ...ail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
                                                             ^
-- check default partition overlap
INSERT INTO list_parted2 VALUES('X');
CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
ERROR:  updated partition constraint for default partition "list_parted2_def" would be violated by some row
CREATE TABLE range_parted2 (
	a int
) PARTITION BY RANGE (a);
-- trying to create range partition with empty range
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
ERROR:  empty range bound specified for partition "fail_part"
LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
                                                             ^
DETAIL:  Specified lower bound (1) is greater than or equal to upper bound (0).
-- note that the range '[1, 1)' has no elements
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
ERROR:  empty range bound specified for partition "fail_part"
LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
                                                             ^
DETAIL:  Specified lower bound (1) is greater than or equal to upper bound (1).
CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2);
ERROR:  partition "fail_part" would overlap partition "part0"
LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) ...
                                                             ^
CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1);
ERROR:  partition "fail_part" would overlap partition "part0"
LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1)...
                                                             ^
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue);
ERROR:  partition "fail_part" would overlap partition "part1"
LINE 1: ..._part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (max...
                                                             ^
CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
ERROR:  partition "fail_part" would overlap partition "part2"
LINE 1: ...art PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
                                                                   ^
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
ERROR:  partition "fail_part" would overlap partition "part2"
LINE 1: ...art PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
                                                                   ^
-- Create a default partition for range partitioned table
CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT;
-- More than one default partition is not allowed, so this should give error
CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT;
ERROR:  partition "fail_default_part" conflicts with existing default partition "range2_default"
LINE 1: ... TABLE fail_default_part PARTITION OF range_parted2 DEFAULT;
                                                               ^
-- Check if the range for default partitions overlap
INSERT INTO range_parted2 VALUES (85);
CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90);
ERROR:  updated partition constraint for default partition "range2_default" would be violated by some row
CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100);
-- now check for multi-column range partition key
CREATE TABLE range_parted3 (
	a int,
	b int
) PARTITION BY RANGE (a, (b+1));
CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue);
CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1);
ERROR:  partition "fail_part" would overlap partition "part00"
LINE 1: ..._part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalu...
                                                             ^
CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1);
CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue);
CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
ERROR:  partition "fail_part" would overlap partition "part12"
LINE 1: ...rt PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1,...
                                                             ^
CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT;
-- cannot create a partition that says column b is allowed to range
-- from -infinity to +infinity, while there exist partitions that have
-- more specific ranges
CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue);
ERROR:  partition "fail_part" would overlap partition "part10"
LINE 1: ..._part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalu...
                                                             ^
-- check for partition bound overlap and other invalid specifications for the hash partition
CREATE TABLE hash_parted2 (
	a varchar
) PARTITION BY HASH (a);
CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5);
-- overlap with part_4
CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
ERROR:  partition "fail_part" would overlap partition "h2part_4"
LINE 1: ...LE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODU...
                                                             ^
-- modulus must be greater than zero
CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1);
ERROR:  modulus for hash partition must be an integer value greater than zero
-- remainder must be greater than or equal to zero and less than modulus
CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8);
ERROR:  remainder for hash partition must be less than modulus
-- check schema propagation from parent
CREATE TABLE parted (
	a text,
	b int NOT NULL DEFAULT 0,
	CONSTRAINT check_a CHECK (length(a) > 0)
) PARTITION BY LIST (a);
CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a');
-- only inherited attributes (never local ones)
SELECT attname, attislocal, attinhcount FROM pg_attribute
  WHERE attrelid = 'part_a'::regclass and attnum > 0
  ORDER BY attnum;
 attname | attislocal | attinhcount 
---------+------------+-------------
 a       | f          |           1
 b       | f          |           1
(2 rows)

-- able to specify column default, column constraint, and table constraint
-- first check the "column specified more than once" error
CREATE TABLE part_b PARTITION OF parted (
	b NOT NULL,
	b DEFAULT 1,
	b CHECK (b >= 0),
	CONSTRAINT check_a CHECK (length(a) > 0)
) FOR VALUES IN ('b');
ERROR:  column "b" specified more than once
CREATE TABLE part_b PARTITION OF parted (
	b NOT NULL DEFAULT 1,
	CONSTRAINT check_a CHECK (length(a) > 0),
	CONSTRAINT check_b CHECK (b >= 0)
) FOR VALUES IN ('b');
NOTICE:  merging constraint "check_a" with inherited definition
-- conislocal should be false for any merged constraints, true otherwise
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
 conislocal | coninhcount 
------------+-------------
 f          |           1
 t          |           0
(2 rows)

-- Once check_b is added to the parent, it should be made non-local for part_b
ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
NOTICE:  merging constraint "check_b" with inherited definition
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
 conislocal | coninhcount 
------------+-------------
 f          |           1
 f          |           1
(2 rows)

-- Neither check_a nor check_b are droppable from part_b
ALTER TABLE part_b DROP CONSTRAINT check_a;
ERROR:  cannot drop inherited constraint "check_a" of relation "part_b"
ALTER TABLE part_b DROP CONSTRAINT check_b;
ERROR:  cannot drop inherited constraint "check_b" of relation "part_b"
-- And dropping it from parted should leave no trace of them on part_b, unlike
-- traditional inheritance where they will be left behind, because they would
-- be local constraints.
ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
 conislocal | coninhcount 
------------+-------------
(0 rows)

-- specify PARTITION BY for a partition
CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
ERROR:  column "c" named in partition key does not exist
LINE 1: ...TITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
                                                                    ^
CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
-- create a level-2 partition
CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
-- check that NOT NULL and default value are inherited correctly
create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a);
create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1);
insert into parted_notnull_inh_test (b) values (null);
ERROR:  null value in column "b" of relation "parted_notnull_inh_test1" violates not-null constraint
DETAIL:  Failing row contains (1, null).
-- note that while b's default is overridden, a's default is preserved
\d parted_notnull_inh_test1
      Table "public.parted_notnull_inh_test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 1
 b      | integer |           | not null | 1
Partition of: parted_notnull_inh_test FOR VALUES IN (1)

drop table parted_notnull_inh_test;
-- check that collations are assigned in partition bound expressions
create table parted_boolean_col (a bool, b text) partition by list(a);
create table parted_boolean_less partition of parted_boolean_col
  for values in ('foo' < 'bar');
create table parted_boolean_greater partition of parted_boolean_col
  for values in ('foo' > 'bar');
drop table parted_boolean_col;
-- check for a conflicting COLLATE clause
create table parted_collate_must_match (a text collate "C", b text collate "C")
  partition by range (a);
-- on the partition key
create table parted_collate_must_match1 partition of parted_collate_must_match
  (a collate "POSIX") for values from ('a') to ('m');
-- on another column
create table parted_collate_must_match2 partition of parted_collate_must_match
  (b collate "POSIX") for values from ('m') to ('z');
drop table parted_collate_must_match;
-- check that non-matching collations for partition bound
-- expressions are coerced to the right collation
create table test_part_coll_posix (a text) partition by range (a collate "POSIX");
-- ok, collation is implicitly coerced
create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g');
-- ok
create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m');
-- ok, collation is implicitly coerced
create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s');
-- ok; partition collation silently overrides the default collation of type 'name'
create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z');
drop table test_part_coll_posix;
-- Partition bound in describe output
\d+ part_b
                                   Table "public.part_b"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | text    |           |          |         | extended |              | 
 b      | integer |           | not null | 1       | plain    |              | 
Partition of: parted FOR VALUES IN ('b')
Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))

-- Both partition bound and partition key in describe output
\d+ part_c
                             Partitioned table "public.part_c"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | text    |           |          |         | extended |              | 
 b      | integer |           | not null | 0       | plain    |              | 
Partition of: parted FOR VALUES IN ('c')
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
Partition key: RANGE (b)
Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)

-- a level-2 partition's constraint will include the parent's expressions
\d+ part_c_1_10
                                Table "public.part_c_1_10"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | text    |           |          |         | extended |              | 
 b      | integer |           | not null | 0       | plain    |              | 
Partition of: part_c FOR VALUES FROM (1) TO (10)
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))

-- Show partition count in the parent's describe output
-- Tempted to include \d+ output listing partitions with bound info but
-- output could vary depending on the order in which partition oids are
-- returned.
\d parted
         Partitioned table "public.parted"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | text    |           |          | 
 b      | integer |           | not null | 0
Partition key: LIST (a)
Number of partitions: 3 (Use \d+ to list them.)

\d hash_parted
      Partitioned table "public.hash_parted"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Partition key: HASH (a)
Number of partitions: 4 (Use \d+ to list them.)

-- check that we get the expected partition constraints
CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE);
\d+ unbounded_range_part
                           Table "public.unbounded_range_part"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
 c      | integer |           |          |         | plain   |              | 
Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL))

DROP TABLE unbounded_range_part;
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE);
\d+ range_parted4_1
                              Table "public.range_parted4_1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
 c      | integer |           |          |         | plain   |              | 
Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))

CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
\d+ range_parted4_2
                              Table "public.range_parted4_2"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
 c      | integer |           |          |         | plain   |              | 
Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))

CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE);
\d+ range_parted4_3
                              Table "public.range_parted4_3"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
 c      | integer |           |          |         | plain   |              | 
Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))

DROP TABLE range_parted4;
-- user-defined operator class in partition key
CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql
  AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$;
CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS
  OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4),
  OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4),
  OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4);
CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops);
CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000);
INSERT INTO partkey_t VALUES (100);
INSERT INTO partkey_t VALUES (200);
-- cleanup
DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
DROP TABLE partkey_t, hash_parted, hash_parted2;
DROP OPERATOR CLASS test_int4_ops USING btree;
DROP FUNCTION my_int4_sort(int4,int4);
-- comments on partitioned tables columns
CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
SELECT obj_description('parted_col_comment'::regclass);
   obj_description    
----------------------
 Am partitioned table
(1 row)

\d+ parted_col_comment
                        Partitioned table "public.parted_col_comment"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target |  Description  
--------+---------+-----------+----------+---------+----------+--------------+---------------
 a      | integer |           |          |         | plain    |              | Partition key
 b      | text    |           |          |         | extended |              | 
Partition key: LIST (a)
Number of partitions: 0

DROP TABLE parted_col_comment;
-- specifying storage parameters for partitioned tables is not supported
CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a) WITH (fillfactor=100);
ERROR:  cannot specify storage parameters for a partitioned table
HINT:  Specify storage parameters for its leaf partitions instead.
-- list partitioning on array type column
CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
\d+ arrlp12
                                   Table "public.arrlp12"
 Column |   Type    | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------+-----------+----------+---------+----------+--------------+-------------
 a      | integer[] |           |          |         | extended |              | 
Partition of: arrlp FOR VALUES IN ('{1}', '{2}')
Partition constraint: ((a IS NOT NULL) AND ((a = '{1}'::integer[]) OR (a = '{2}'::integer[])))

DROP TABLE arrlp;
-- partition on boolean column
create table boolspart (a bool) partition by list (a);
create table boolspart_t partition of boolspart for values in (true);
create table boolspart_f partition of boolspart for values in (false);
\d+ boolspart
                           Partitioned table "public.boolspart"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | boolean |           |          |         | plain   |              | 
Partition key: LIST (a)
Partitions: boolspart_f FOR VALUES IN (false),
            boolspart_t FOR VALUES IN (true)

drop table boolspart;
-- partitions mixing temporary and permanent relations
create table perm_parted (a int) partition by list (a);
create temporary table temp_parted (a int) partition by list (a);
create table perm_part partition of temp_parted default; -- error
ERROR:  cannot create a permanent relation as partition of temporary relation "temp_parted"
create temp table temp_part partition of perm_parted default; -- error
ERROR:  cannot create a temporary relation as partition of permanent relation "perm_parted"
create temp table temp_part partition of temp_parted default; -- ok
drop table perm_parted cascade;
drop table temp_parted cascade;
-- check that adding partitions to a table while it is being used is prevented
create table tab_part_create (a int) partition by list (a);
create or replace function func_part_create() returns trigger
  language plpgsql as $$
  begin
    execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)';
    return null;
  end $$;
create trigger trig_part_create before insert on tab_part_create
  for each statement execute procedure func_part_create();
insert into tab_part_create values (1);
ERROR:  cannot CREATE TABLE .. PARTITION OF "tab_part_create" because it is being used by active queries in this session
CONTEXT:  SQL statement "create table tab_part_create_1 partition of tab_part_create for values in (1)"
PL/pgSQL function func_part_create() line 3 at EXECUTE
drop table tab_part_create;
drop function func_part_create();
-- test using a volatile expression as partition bound
create table volatile_partbound_test (partkey timestamp) partition by range (partkey);
create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp);
create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue);
-- this should go into the partition volatile_partbound_test2
insert into volatile_partbound_test values (current_timestamp);
select tableoid::regclass from volatile_partbound_test;
         tableoid         
--------------------------
 volatile_partbound_test2
(1 row)

drop table volatile_partbound_test;
-- test the case where a check constraint on default partition allows
-- to avoid scanning it when adding a new partition
create table defcheck (a int, b int) partition by list (b);
create table defcheck_def (a int, c int, b int);
alter table defcheck_def drop c;
alter table defcheck attach partition defcheck_def default;
alter table defcheck_def add check (b <= 0 and b is not null);
create table defcheck_1 partition of defcheck for values in (1, null);
-- test that complex default partition constraints are enforced correctly
insert into defcheck_def values (0, 0);
create table defcheck_0 partition of defcheck for values in (0);
ERROR:  updated partition constraint for default partition "defcheck_def" would be violated by some row
drop table defcheck;
-- tests of column drop with partition tables and indexes using
-- predicates and expressions.
create table part_column_drop (
  useless_1 int,
  id int,
  useless_2 int,
  d int,
  b int,
  useless_3 int
) partition by range (id);
alter table part_column_drop drop column useless_1;
alter table part_column_drop drop column useless_2;
alter table part_column_drop drop column useless_3;
create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
create index part_column_drop_b_expr on part_column_drop((b = 1));
create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
create index part_column_drop_d_expr on part_column_drop((d = 2));
create table part_column_drop_1_10 partition of
  part_column_drop for values from (1) to (10);
\d part_column_drop
    Partitioned table "public.part_column_drop"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 d      | integer |           |          | 
 b      | integer |           |          | 
Partition key: RANGE (id)
Indexes:
    "part_column_drop_b_expr" btree ((b = 1))
    "part_column_drop_b_pred" btree (b) WHERE b = 1
    "part_column_drop_d_expr" btree ((d = 2))
    "part_column_drop_d_pred" btree (d) WHERE d = 2
Number of partitions: 1 (Use \d+ to list them.)

\d part_column_drop_1_10
       Table "public.part_column_drop_1_10"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 d      | integer |           |          | 
 b      | integer |           |          | 
Partition of: part_column_drop FOR VALUES FROM (1) TO (10)
Indexes:
    "part_column_drop_1_10_b_idx" btree (b) WHERE b = 1
    "part_column_drop_1_10_d_idx" btree (d) WHERE d = 2
    "part_column_drop_1_10_expr_idx" btree ((b = 1))
    "part_column_drop_1_10_expr_idx1" btree ((d = 2))

drop table part_column_drop;