src/test/regress/sql/create_table.sql

   1 --
   2 -- CREATE_TABLE
   3 --
   4
   5 -- Error cases
   6 CREATE TABLE unknowntab (
   7         u unknown    -- fail
   8 );
   9
  10 CREATE TYPE unknown_comptype AS (
  11         u unknown    -- fail
  12 );
  13
  14 -- invalid: non-lowercase quoted reloptions identifiers
  15 CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a;
  16
  17 CREATE UNLOGGED TABLE unlogged1 (a int primary key);                    -- OK
  18 CREATE TEMPORARY TABLE unlogged2 (a int primary key);                   -- OK
  19 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
  20 REINDEX INDEX unlogged1_pkey;
  21 REINDEX INDEX unlogged2_pkey;
  22 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
  23 DROP TABLE unlogged2;
  24 INSERT INTO unlogged1 VALUES (42);
  25 CREATE UNLOGGED TABLE public.unlogged2 (a int primary key);             -- also OK
  26 CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key);    -- not OK
  27 CREATE TABLE pg_temp.implicitly_temp (a int primary key);               -- OK
  28 CREATE TEMP TABLE explicitly_temp (a int primary key);                  -- also OK
  29 CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key);              -- also OK
  30 CREATE TEMP TABLE public.temp_to_perm (a int primary key);              -- not OK
  31 DROP TABLE unlogged1, public.unlogged2;
  32
  33 CREATE UNLOGGED TABLE unlogged1 (a int) PARTITION BY RANGE (a); -- fail
  34 CREATE TABLE unlogged1 (a int) PARTITION BY RANGE (a); -- ok
  35 ALTER TABLE unlogged1 SET LOGGED; -- fails
  36 ALTER TABLE unlogged1 SET UNLOGGED; -- fails
  37 DROP TABLE unlogged1;
  38
  39 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
  40 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
  41 CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
  42 DROP TABLE as_select1;
  43
  44 PREPARE select1 AS SELECT 1 as a;
  45 CREATE TABLE as_select1 AS EXECUTE select1;
  46 CREATE TABLE as_select1 AS EXECUTE select1;
  47 SELECT * FROM as_select1;
  48 CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1;
  49 DROP TABLE as_select1;
  50 DEALLOCATE select1;
  51
  52 -- create an extra wide table to test for issues related to that
  53 -- (temporarily hide query, to avoid the long CREATE TABLE stmt)
  54 \set ECHO none
  55 SELECT 'CREATE TABLE extra_wide_table(firstc text, '|| array_to_string(array_agg('c'||i||' bool'),',')||', lastc text);'
  56 FROM generate_series(1, 1100) g(i)
  57 \gexec
  58 \set ECHO all
  59 INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col');
  60 SELECT firstc, lastc FROM extra_wide_table;
  61
  62 -- check that tables with oids cannot be created anymore
  63 CREATE TABLE withoid() WITH OIDS;
  64 CREATE TABLE withoid() WITH (oids);
  65 CREATE TABLE withoid() WITH (oids = true);
  66
  67 -- but explicitly not adding oids is still supported
  68 CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
  69 CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
  70
  71 -- check restriction with default expressions
  72 -- invalid use of column reference in default expressions
  73 CREATE TABLE default_expr_column (id int DEFAULT (id));
  74 CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
  75 CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
  76 -- invalid column definition
  77 CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
  78 -- invalid use of aggregate
  79 CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
  80 -- invalid use of subquery
  81 CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
  82 -- invalid use of set-returning function
  83 CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
  84
  85 -- Verify that subtransaction rollback restores rd_createSubid.
  86 BEGIN;
  87 CREATE TABLE remember_create_subid (c int);
  88 SAVEPOINT q; DROP TABLE remember_create_subid; ROLLBACK TO q;
  89 COMMIT;
  90 DROP TABLE remember_create_subid;
  91
  92 -- Verify that subtransaction rollback restores rd_firstRelfilenodeSubid.
  93 CREATE TABLE remember_node_subid (c int);
  94 BEGIN;
  95 ALTER TABLE remember_node_subid ALTER c TYPE bigint;
  96 SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q;
  97 COMMIT;
  98 DROP TABLE remember_node_subid;
  99
 100 --
 101 -- Partitioned tables
 102 --
 103
 104 -- cannot combine INHERITS and PARTITION BY (although grammar allows)
 105 CREATE TABLE partitioned (
 106         a int
 107 ) INHERITS (some_table) PARTITION BY LIST (a);
 108
 109 -- cannot use more than 1 column as partition key for list partitioned table
 110 CREATE TABLE partitioned (
 111         a1 int,
 112         a2 int
 113 ) PARTITION BY LIST (a1, a2);   -- fail
 114
 115 -- prevent using prohibited expressions in the key
 116 CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
 117 CREATE TABLE partitioned (
 118         a int
 119 ) PARTITION BY RANGE (retset(a));
 120 DROP FUNCTION retset(int);
 121
 122 CREATE TABLE partitioned (
 123         a int
 124 ) PARTITION BY RANGE ((avg(a)));
 125
 126 CREATE TABLE partitioned (
 127         a int,
 128         b int
 129 ) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b)));
 130
 131 CREATE TABLE partitioned (
 132         a int
 133 ) PARTITION BY LIST ((a LIKE (SELECT 1)));
 134
 135 CREATE TABLE partitioned (
 136         a int
 137 ) PARTITION BY RANGE ((42));
 138
 139 CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
 140 CREATE TABLE partitioned (
 141         a int
 142 ) PARTITION BY RANGE (const_func());
 143 DROP FUNCTION const_func();
 144
 145 -- only accept valid partitioning strategy
 146 CREATE TABLE partitioned (
 147     a int
 148 ) PARTITION BY MAGIC (a);
 149
 150 -- specified column must be present in the table
 151 CREATE TABLE partitioned (
 152         a int
 153 ) PARTITION BY RANGE (b);
 154
 155 -- cannot use system columns in partition key
 156 CREATE TABLE partitioned (
 157         a int
 158 ) PARTITION BY RANGE (xmin);
 159
 160 -- cannot use pseudotypes
 161 CREATE TABLE partitioned (
 162         a int,
 163         b int
 164 ) PARTITION BY RANGE (((a, b)));
 165 CREATE TABLE partitioned (
 166         a int,
 167         b int
 168 ) PARTITION BY RANGE (a, ('unknown'));
 169
 170 -- functions in key must be immutable
 171 CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL;
 172 CREATE TABLE partitioned (
 173         a int
 174 ) PARTITION BY RANGE (immut_func(a));
 175 DROP FUNCTION immut_func(int);
 176
 177 -- prevent using columns of unsupported types in key (type must have a btree operator class)
 178 CREATE TABLE partitioned (
 179         a point
 180 ) PARTITION BY LIST (a);
 181 CREATE TABLE partitioned (
 182         a point
 183 ) PARTITION BY LIST (a point_ops);
 184 CREATE TABLE partitioned (
 185         a point
 186 ) PARTITION BY RANGE (a);
 187 CREATE TABLE partitioned (
 188         a point
 189 ) PARTITION BY RANGE (a point_ops);
 190
 191 -- cannot add NO INHERIT constraints to partitioned tables
 192 CREATE TABLE partitioned (
 193         a int,
 194         CONSTRAINT check_a CHECK (a > 0) NO INHERIT
 195 ) PARTITION BY RANGE (a);
 196
 197 -- some checks after successful creation of a partitioned table
 198 CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL;
 199
 200 CREATE TABLE partitioned (
 201         a int,
 202         b int,
 203         c text,
 204         d text
 205 ) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C");
 206
 207 -- check relkind
 208 SELECT relkind FROM pg_class WHERE relname = 'partitioned';
 209
 210 -- prevent a function referenced in partition key from being dropped
 211 DROP FUNCTION plusone(int);
 212
 213 -- partitioned table cannot participate in regular inheritance
 214 CREATE TABLE partitioned2 (
 215         a int,
 216         b text
 217 ) PARTITION BY RANGE ((a+1), substr(b, 1, 5));
 218 CREATE TABLE fail () INHERITS (partitioned2);
 219
 220 -- Partition key in describe output
 221 \d partitioned
 222 \d+ partitioned2
 223
 224 INSERT INTO partitioned2 VALUES (1, 'hello');
 225 CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc');
 226 \d+ part2_1
 227
 228 DROP TABLE partitioned, partitioned2;
 229
 230 -- check reference to partitioned table's rowtype in partition descriptor
 231 create table partitioned (a int, b int)
 232   partition by list ((row(a, b)::partitioned));
 233 create table partitioned1
 234   partition of partitioned for values in ('(1,2)'::partitioned);
 235 create table partitioned2
 236   partition of partitioned for values in ('(2,4)'::partitioned);
 237 explain (costs off)
 238 select * from partitioned where row(a,b)::partitioned = '(1,2)'::partitioned;
 239 drop table partitioned;
 240
 241 -- whole-row Var in partition key works too
 242 create table partitioned (a int, b int)
 243   partition by list ((partitioned));
 244 create table partitioned1
 245   partition of partitioned for values in ('(1,2)');
 246 create table partitioned2
 247   partition of partitioned for values in ('(2,4)');
 248 explain (costs off)
 249 select * from partitioned where partitioned = '(1,2)'::partitioned;
 250 \d+ partitioned1
 251 drop table partitioned;
 252
 253 -- check that dependencies of partition columns are handled correctly
 254 create domain intdom1 as int;
 255
 256 create table partitioned (
 257         a intdom1,
 258         b text
 259 ) partition by range (a);
 260
 261 alter table partitioned drop column a;  -- fail
 262
 263 drop domain intdom1;  -- fail, requires cascade
 264
 265 drop domain intdom1 cascade;
 266
 267 table partitioned;  -- gone
 268
 269 -- likewise for columns used in partition expressions
 270 create domain intdom1 as int;
 271
 272 create table partitioned (
 273         a intdom1,
 274         b text
 275 ) partition by range (plusone(a));
 276
 277 alter table partitioned drop column a;  -- fail
 278
 279 drop domain intdom1;  -- fail, requires cascade
 280
 281 drop domain intdom1 cascade;
 282
 283 table partitioned;  -- gone
 284
 285
 286 --
 287 -- Partitions
 288 --
 289
 290 -- check partition bound syntax
 291
 292 CREATE TABLE list_parted (
 293         a int
 294 ) PARTITION BY LIST (a);
 295 CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1');
 296 CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2);
 297 CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1));
 298 CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
 299 \d+ list_parted
 300
 301 -- forbidden expressions for partition bound with list partitioned table
 302 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
 303 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename);
 304 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
 305 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
 306 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename));
 307 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
 308 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1));
 309 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (generate_series(4, 6));
 310 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "POSIX");
 311
 312 -- syntax does not allow empty list of values for list partitions
 313 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
 314 -- trying to specify range for list partitioned table
 315 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
 316 -- trying to specify modulus and remainder for list partitioned table
 317 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
 318
 319 -- check default partition cannot be created more than once
 320 CREATE TABLE part_default PARTITION OF list_parted DEFAULT;
 321 CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
 322
 323 -- specified literal can't be cast to the partition column data type
 324 CREATE TABLE bools (
 325         a bool
 326 ) PARTITION BY LIST (a);
 327 CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
 328 DROP TABLE bools;
 329
 330 -- specified literal can be cast, and the cast might not be immutable
 331 CREATE TABLE moneyp (
 332         a money
 333 ) PARTITION BY LIST (a);
 334 CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10);
 335 CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11');
 336 CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int);
 337 DROP TABLE moneyp;
 338
 339 -- cast is immutable
 340 CREATE TABLE bigintp (
 341         a bigint
 342 ) PARTITION BY LIST (a);
 343 CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10);
 344 -- fails due to overlap:
 345 CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10');
 346 DROP TABLE bigintp;
 347
 348 CREATE TABLE range_parted (
 349         a date
 350 ) PARTITION BY RANGE (a);
 351
 352 -- forbidden expressions for partition bounds with range partitioned table
 353 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 354   FOR VALUES FROM (somename) TO ('2019-01-01');
 355 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 356   FOR VALUES FROM (somename.somename) TO ('2019-01-01');
 357 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 358   FOR VALUES FROM (a) TO ('2019-01-01');
 359 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 360   FOR VALUES FROM (max(a)) TO ('2019-01-01');
 361 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 362   FOR VALUES FROM (max(somename)) TO ('2019-01-01');
 363 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 364   FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01');
 365 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 366   FOR VALUES FROM ((select 1)) TO ('2019-01-01');
 367 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
 368   FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01');
 369
 370 -- trying to specify list for range partitioned table
 371 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
 372 -- trying to specify modulus and remainder for range partitioned table
 373 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
 374 -- each of start and end bounds must have same number of values as the
 375 -- length of the partition key
 376 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
 377 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
 378
 379 -- cannot specify null values in range bounds
 380 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue);
 381
 382 -- trying to specify modulus and remainder for range partitioned table
 383 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1);
 384
 385 -- check partition bound syntax for the hash partition
 386 CREATE TABLE hash_parted (
 387         a int
 388 ) PARTITION BY HASH (a);
 389 CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0);
 390 CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1);
 391 CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2);
 392 CREATE TABLE hpart_4 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 3);
 393 -- modulus 25 is factor of modulus of 50 but 10 is not a factor of 25.
 394 CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 25, REMAINDER 3);
 395 -- previous modulus 50 is factor of 150 but this modulus is not a factor of next modulus 200.
 396 CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3);
 397 -- overlapping remainders
 398 CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 100, REMAINDER 3);
 399 -- trying to specify range for the hash partitioned table
 400 CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
 401 -- trying to specify list value for the hash partitioned table
 402 CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
 403
 404 -- trying to create default partition for the hash partitioned table
 405 CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT;
 406
 407 -- check if compatible with the specified parent
 408
 409 -- cannot create as partition of a non-partitioned table
 410 CREATE TABLE unparted (
 411         a int
 412 );
 413 CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
 414 CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1);
 415 DROP TABLE unparted;
 416
 417 -- cannot create a permanent rel as partition of a temp rel
 418 CREATE TEMP TABLE temp_parted (
 419         a int
 420 ) PARTITION BY LIST (a);
 421 CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
 422 DROP TABLE temp_parted;
 423
 424 -- check for partition bound overlap and other invalid specifications
 425
 426 CREATE TABLE list_parted2 (
 427         a varchar
 428 ) PARTITION BY LIST (a);
 429 CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z');
 430 CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b');
 431 CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT;
 432
 433 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
 434 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
 435 -- check default partition overlap
 436 INSERT INTO list_parted2 VALUES('X');
 437 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
 438
 439 CREATE TABLE range_parted2 (
 440         a int
 441 ) PARTITION BY RANGE (a);
 442
 443 -- trying to create range partition with empty range
 444 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
 445 -- note that the range '[1, 1)' has no elements
 446 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
 447
 448 CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1);
 449 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2);
 450 CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
 451 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1);
 452 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue);
 453 CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
 454 CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
 455 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
 456 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
 457
 458 -- Create a default partition for range partitioned table
 459 CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT;
 460
 461 -- More than one default partition is not allowed, so this should give error
 462 CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT;
 463
 464 -- Check if the range for default partitions overlap
 465 INSERT INTO range_parted2 VALUES (85);
 466 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90);
 467 CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100);
 468
 469 -- now check for multi-column range partition key
 470 CREATE TABLE range_parted3 (
 471         a int,
 472         b int
 473 ) PARTITION BY RANGE (a, (b+1));
 474
 475 CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue);
 476 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1);
 477
 478 CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1);
 479 CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
 480 CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue);
 481 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
 482 CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT;
 483
 484 -- cannot create a partition that says column b is allowed to range
 485 -- from -infinity to +infinity, while there exist partitions that have
 486 -- more specific ranges
 487 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue);
 488
 489 -- check for partition bound overlap and other invalid specifications for the hash partition
 490 CREATE TABLE hash_parted2 (
 491         a varchar
 492 ) PARTITION BY HASH (a);
 493 CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
 494 CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0);
 495 CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4);
 496 CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5);
 497 -- overlap with part_4
 498 CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
 499 -- modulus must be greater than zero
 500 CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1);
 501 -- remainder must be greater than or equal to zero and less than modulus
 502 CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8);
 503
 504 -- check schema propagation from parent
 505
 506 CREATE TABLE parted (
 507         a text,
 508         b int NOT NULL DEFAULT 0,
 509         CONSTRAINT check_a CHECK (length(a) > 0)
 510 ) PARTITION BY LIST (a);
 511
 512 CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a');
 513
 514 -- only inherited attributes (never local ones)
 515 SELECT attname, attislocal, attinhcount FROM pg_attribute
 516   WHERE attrelid = 'part_a'::regclass and attnum > 0
 517   ORDER BY attnum;
 518
 519 -- able to specify column default, column constraint, and table constraint
 520
 521 -- first check the "column specified more than once" error
 522 CREATE TABLE part_b PARTITION OF parted (
 523         b NOT NULL,
 524         b DEFAULT 1,
 525         b CHECK (b >= 0),
 526         CONSTRAINT check_a CHECK (length(a) > 0)
 527 ) FOR VALUES IN ('b');
 528
 529 CREATE TABLE part_b PARTITION OF parted (
 530         b NOT NULL DEFAULT 1,
 531         CONSTRAINT check_a CHECK (length(a) > 0),
 532         CONSTRAINT check_b CHECK (b >= 0)
 533 ) FOR VALUES IN ('b');
 534 -- conislocal should be false for any merged constraints, true otherwise
 535 SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
 536
 537 -- Once check_b is added to the parent, it should be made non-local for part_b
 538 ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
 539 SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
 540
 541 -- Neither check_a nor check_b are droppable from part_b
 542 ALTER TABLE part_b DROP CONSTRAINT check_a;
 543 ALTER TABLE part_b DROP CONSTRAINT check_b;
 544
 545 -- And dropping it from parted should leave no trace of them on part_b, unlike
 546 -- traditional inheritance where they will be left behind, because they would
 547 -- be local constraints.
 548 ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
 549 SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname;
 550
 551 -- specify PARTITION BY for a partition
 552 CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
 553 CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
 554
 555 -- create a level-2 partition
 556 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
 557
 558 -- check that NOT NULL and default value are inherited correctly
 559 create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a);
 560 create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1);
 561 insert into parted_notnull_inh_test (b) values (null);
 562 -- note that while b's default is overridden, a's default is preserved
 563 \d parted_notnull_inh_test1
 564 drop table parted_notnull_inh_test;
 565
 566 -- check that collations are assigned in partition bound expressions
 567 create table parted_boolean_col (a bool, b text) partition by list(a);
 568 create table parted_boolean_less partition of parted_boolean_col
 569   for values in ('foo' < 'bar');
 570 create table parted_boolean_greater partition of parted_boolean_col
 571   for values in ('foo' > 'bar');
 572 drop table parted_boolean_col;
 573
 574 -- check for a conflicting COLLATE clause
 575 create table parted_collate_must_match (a text collate "C", b text collate "C")
 576   partition by range (a);
 577 -- on the partition key
 578 create table parted_collate_must_match1 partition of parted_collate_must_match
 579   (a collate "POSIX") for values from ('a') to ('m');
 580 -- on another column
 581 create table parted_collate_must_match2 partition of parted_collate_must_match
 582   (b collate "POSIX") for values from ('m') to ('z');
 583 drop table parted_collate_must_match;
 584
 585 -- check that non-matching collations for partition bound
 586 -- expressions are coerced to the right collation
 587
 588 create table test_part_coll_posix (a text) partition by range (a collate "POSIX");
 589 -- ok, collation is implicitly coerced
 590 create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g');
 591 -- ok
 592 create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m');
 593 -- ok, collation is implicitly coerced
 594 create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s');
 595 -- ok; partition collation silently overrides the default collation of type 'name'
 596 create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z');
 597
 598 drop table test_part_coll_posix;
 599
 600 -- Partition bound in describe output
 601 \d+ part_b
 602
 603 -- Both partition bound and partition key in describe output
 604 \d+ part_c
 605
 606 -- a level-2 partition's constraint will include the parent's expressions
 607 \d+ part_c_1_10
 608
 609 -- Show partition count in the parent's describe output
 610 -- Tempted to include \d+ output listing partitions with bound info but
 611 -- output could vary depending on the order in which partition oids are
 612 -- returned.
 613 \d parted
 614 \d hash_parted
 615
 616 -- check that we get the expected partition constraints
 617 CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
 618 CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE);
 619 \d+ unbounded_range_part
 620 DROP TABLE unbounded_range_part;
 621 CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE);
 622 \d+ range_parted4_1
 623 CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
 624 \d+ range_parted4_2
 625 CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE);
 626 \d+ range_parted4_3
 627 DROP TABLE range_parted4;
 628
 629 -- user-defined operator class in partition key
 630 CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql
 631   AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$;
 632 CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS
 633   OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4),
 634   OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4),
 635   OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4);
 636 CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops);
 637 CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000);
 638 INSERT INTO partkey_t VALUES (100);
 639 INSERT INTO partkey_t VALUES (200);
 640
 641 -- cleanup
 642 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
 643 DROP TABLE partkey_t, hash_parted, hash_parted2;
 644 DROP OPERATOR CLASS test_int4_ops USING btree;
 645 DROP FUNCTION my_int4_sort(int4,int4);
 646
 647 -- comments on partitioned tables columns
 648 CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
 649 COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
 650 COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
 651 SELECT obj_description('parted_col_comment'::regclass);
 652 \d+ parted_col_comment
 653 DROP TABLE parted_col_comment;
 654
 655 -- specifying storage parameters for partitioned tables is not supported
 656 CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a) WITH (fillfactor=100);
 657
 658 -- list partitioning on array type column
 659 CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
 660 CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
 661 \d+ arrlp12
 662 DROP TABLE arrlp;
 663
 664 -- partition on boolean column
 665 create table boolspart (a bool) partition by list (a);
 666 create table boolspart_t partition of boolspart for values in (true);
 667 create table boolspart_f partition of boolspart for values in (false);
 668 \d+ boolspart
 669 drop table boolspart;
 670
 671 -- partitions mixing temporary and permanent relations
 672 create table perm_parted (a int) partition by list (a);
 673 create temporary table temp_parted (a int) partition by list (a);
 674 create table perm_part partition of temp_parted default; -- error
 675 create temp table temp_part partition of perm_parted default; -- error
 676 create temp table temp_part partition of temp_parted default; -- ok
 677 drop table perm_parted cascade;
 678 drop table temp_parted cascade;
 679
 680 -- check that adding partitions to a table while it is being used is prevented
 681 create table tab_part_create (a int) partition by list (a);
 682 create or replace function func_part_create() returns trigger
 683   language plpgsql as $$
 684   begin
 685     execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)';
 686     return null;
 687   end $$;
 688 create trigger trig_part_create before insert on tab_part_create
 689   for each statement execute procedure func_part_create();
 690 insert into tab_part_create values (1);
 691 drop table tab_part_create;
 692 drop function func_part_create();
 693
 694 -- test using a volatile expression as partition bound
 695 create table volatile_partbound_test (partkey timestamp) partition by range (partkey);
 696 create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp);
 697 create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue);
 698 -- this should go into the partition volatile_partbound_test2
 699 insert into volatile_partbound_test values (current_timestamp);
 700 select tableoid::regclass from volatile_partbound_test;
 701 drop table volatile_partbound_test;
 702
 703 -- test the case where a check constraint on default partition allows
 704 -- to avoid scanning it when adding a new partition
 705 create table defcheck (a int, b int) partition by list (b);
 706 create table defcheck_def (a int, c int, b int);
 707 alter table defcheck_def drop c;
 708 alter table defcheck attach partition defcheck_def default;
 709 alter table defcheck_def add check (b <= 0 and b is not null);
 710 create table defcheck_1 partition of defcheck for values in (1, null);
 711
 712 -- test that complex default partition constraints are enforced correctly
 713 insert into defcheck_def values (0, 0);
 714 create table defcheck_0 partition of defcheck for values in (0);
 715 drop table defcheck;
 716
 717 -- tests of column drop with partition tables and indexes using
 718 -- predicates and expressions.
 719 create table part_column_drop (
 720   useless_1 int,
 721   id int,
 722   useless_2 int,
 723   d int,
 724   b int,
 725   useless_3 int
 726 ) partition by range (id);
 727 alter table part_column_drop drop column useless_1;
 728 alter table part_column_drop drop column useless_2;
 729 alter table part_column_drop drop column useless_3;
 730 create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
 731 create index part_column_drop_b_expr on part_column_drop((b = 1));
 732 create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
 733 create index part_column_drop_d_expr on part_column_drop((d = 2));
 734 create table part_column_drop_1_10 partition of
 735   part_column_drop for values from (1) to (10);
 736 \d part_column_drop
 737 \d part_column_drop_1_10
 738 drop table part_column_drop;