1-- 2-- CREATE_TABLE 3-- 4 5-- 6-- CLASS DEFINITIONS 7-- 8CREATE TABLE hobbies_r ( 9 name text, 10 person text 11); 12 13CREATE TABLE equipment_r ( 14 name text, 15 hobby text 16); 17 18CREATE TABLE onek ( 19 unique1 int4, 20 unique2 int4, 21 two int4, 22 four int4, 23 ten int4, 24 twenty int4, 25 hundred int4, 26 thousand int4, 27 twothousand int4, 28 fivethous int4, 29 tenthous int4, 30 odd int4, 31 even int4, 32 stringu1 name, 33 stringu2 name, 34 string4 name 35); 36 37CREATE TABLE tenk1 ( 38 unique1 int4, 39 unique2 int4, 40 two int4, 41 four int4, 42 ten int4, 43 twenty int4, 44 hundred int4, 45 thousand int4, 46 twothousand int4, 47 fivethous int4, 48 tenthous int4, 49 odd int4, 50 even int4, 51 stringu1 name, 52 stringu2 name, 53 string4 name 54); 55 56CREATE TABLE tenk2 ( 57 unique1 int4, 58 unique2 int4, 59 two int4, 60 four int4, 61 ten int4, 62 twenty int4, 63 hundred int4, 64 thousand int4, 65 twothousand int4, 66 fivethous int4, 67 tenthous int4, 68 odd int4, 69 even int4, 70 stringu1 name, 71 stringu2 name, 72 string4 name 73); 74 75 76CREATE TABLE person ( 77 name text, 78 age int4, 79 location point 80); 81 82 83CREATE TABLE emp ( 84 salary int4, 85 manager name 86) INHERITS (person); 87 88 89CREATE TABLE student ( 90 gpa float8 91) INHERITS (person); 92 93 94CREATE TABLE stud_emp ( 95 percent int4 96) INHERITS (emp, student); 97 98 99CREATE TABLE city ( 100 name name, 101 location box, 102 budget city_budget 103); 104 105CREATE TABLE dept ( 106 dname name, 107 mgrname text 108); 109 110CREATE TABLE slow_emp4000 ( 111 home_base box 112); 113 114CREATE TABLE fast_emp4000 ( 115 home_base box 116); 117 118CREATE TABLE road ( 119 name text, 120 thepath path 121); 122 123CREATE TABLE ihighway () INHERITS (road); 124 125CREATE TABLE shighway ( 126 surface text 127) INHERITS (road); 128 129CREATE TABLE real_city ( 130 pop int4, 131 cname text, 132 outline path 133); 134 135-- 136-- test the "star" operators a bit more thoroughly -- this time, 137-- throw in lots of NULL fields... 138-- 139-- a is the type root 140-- b and c inherit from a (one-level single inheritance) 141-- d inherits from b and c (two-level multiple inheritance) 142-- e inherits from c (two-level single inheritance) 143-- f inherits from e (three-level single inheritance) 144-- 145CREATE TABLE a_star ( 146 class char, 147 a int4 148); 149 150CREATE TABLE b_star ( 151 b text 152) INHERITS (a_star); 153 154CREATE TABLE c_star ( 155 c name 156) INHERITS (a_star); 157 158CREATE TABLE d_star ( 159 d float8 160) INHERITS (b_star, c_star); 161 162CREATE TABLE e_star ( 163 e int2 164) INHERITS (c_star); 165 166CREATE TABLE f_star ( 167 f polygon 168) INHERITS (e_star); 169 170CREATE TABLE aggtest ( 171 a int2, 172 b float4 173); 174 175CREATE TABLE hash_i4_heap ( 176 seqno int4, 177 random int4 178); 179 180CREATE TABLE hash_name_heap ( 181 seqno int4, 182 random name 183); 184 185CREATE TABLE hash_txt_heap ( 186 seqno int4, 187 random text 188); 189 190CREATE TABLE hash_f8_heap ( 191 seqno int4, 192 random float8 193); 194 195-- don't include the hash_ovfl_heap stuff in the distribution 196-- the data set is too large for what it's worth 197-- 198-- CREATE TABLE hash_ovfl_heap ( 199-- x int4, 200-- y int4 201-- ); 202 203CREATE TABLE bt_i4_heap ( 204 seqno int4, 205 random int4 206); 207 208CREATE TABLE bt_name_heap ( 209 seqno name, 210 random int4 211); 212 213CREATE TABLE bt_txt_heap ( 214 seqno text, 215 random int4 216); 217 218CREATE TABLE bt_f8_heap ( 219 seqno float8, 220 random int4 221); 222 223CREATE TABLE array_op_test ( 224 seqno int4, 225 i int4[], 226 t text[] 227); 228 229CREATE TABLE array_index_op_test ( 230 seqno int4, 231 i int4[], 232 t text[] 233); 234 235CREATE TABLE testjsonb ( 236 j jsonb 237); 238 239CREATE TABLE unknowntab ( 240 u unknown -- fail 241); 242 243CREATE TYPE unknown_comptype AS ( 244 u unknown -- fail 245); 246 247CREATE TABLE IF NOT EXISTS test_tsvector( 248 t text, 249 a tsvector 250); 251 252CREATE TABLE IF NOT EXISTS test_tsvector( 253 t text 254); 255 256-- invalid: non-lowercase quoted reloptions identifiers 257CREATE TABLE tas_case WITH ("Fillfactor" = 10) AS SELECT 1 a; 258 259CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK 260CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK 261SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname; 262REINDEX INDEX unlogged1_pkey; 263REINDEX INDEX unlogged2_pkey; 264SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname; 265DROP TABLE unlogged2; 266INSERT INTO unlogged1 VALUES (42); 267CREATE UNLOGGED TABLE public.unlogged2 (a int primary key); -- also OK 268CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); -- not OK 269CREATE TABLE pg_temp.implicitly_temp (a int primary key); -- OK 270CREATE TEMP TABLE explicitly_temp (a int primary key); -- also OK 271CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key); -- also OK 272CREATE TEMP TABLE public.temp_to_perm (a int primary key); -- not OK 273DROP TABLE unlogged1, public.unlogged2; 274 275CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; 276CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; 277CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; 278DROP TABLE as_select1; 279 280PREPARE select1 AS SELECT 1 as a; 281CREATE TABLE as_select1 AS EXECUTE select1; 282CREATE TABLE as_select1 AS EXECUTE select1; 283SELECT * FROM as_select1; 284CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1; 285DROP TABLE as_select1; 286DEALLOCATE select1; 287 288-- create an extra wide table to test for issues related to that 289-- (temporarily hide query, to avoid the long CREATE TABLE stmt) 290\set ECHO none 291SELECT 'CREATE TABLE extra_wide_table(firstc text, '|| array_to_string(array_agg('c'||i||' bool'),',')||', lastc text);' 292FROM generate_series(1, 1100) g(i) 293\gexec 294\set ECHO all 295INSERT INTO extra_wide_table(firstc, lastc) VALUES('first col', 'last col'); 296SELECT firstc, lastc FROM extra_wide_table; 297 298-- check that tables with oids cannot be created anymore 299CREATE TABLE withoid() WITH OIDS; 300CREATE TABLE withoid() WITH (oids); 301CREATE TABLE withoid() WITH (oids = true); 302 303-- but explicitly not adding oids is still supported 304CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid; 305CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid; 306 307-- check restriction with default expressions 308-- invalid use of column reference in default expressions 309CREATE TABLE default_expr_column (id int DEFAULT (id)); 310CREATE TABLE default_expr_column (id int DEFAULT (bar.id)); 311CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id))); 312-- invalid column definition 313CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent))); 314-- invalid use of aggregate 315CREATE TABLE default_expr_agg (a int DEFAULT (avg(1))); 316-- invalid use of subquery 317CREATE TABLE default_expr_agg (a int DEFAULT (select 1)); 318-- invalid use of set-returning function 319CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3))); 320 321-- Verify that subtransaction rollback restores rd_createSubid. 322BEGIN; 323CREATE TABLE remember_create_subid (c int); 324SAVEPOINT q; DROP TABLE remember_create_subid; ROLLBACK TO q; 325COMMIT; 326DROP TABLE remember_create_subid; 327 328-- Verify that subtransaction rollback restores rd_firstRelfilenodeSubid. 329CREATE TABLE remember_node_subid (c int); 330BEGIN; 331ALTER TABLE remember_node_subid ALTER c TYPE bigint; 332SAVEPOINT q; DROP TABLE remember_node_subid; ROLLBACK TO q; 333COMMIT; 334DROP TABLE remember_node_subid; 335 336-- 337-- Partitioned tables 338-- 339 340-- cannot combine INHERITS and PARTITION BY (although grammar allows) 341CREATE TABLE partitioned ( 342 a int 343) INHERITS (some_table) PARTITION BY LIST (a); 344 345-- cannot use more than 1 column as partition key for list partitioned table 346CREATE TABLE partitioned ( 347 a1 int, 348 a2 int 349) PARTITION BY LIST (a1, a2); -- fail 350 351-- unsupported constraint type for partitioned tables 352CREATE TABLE partitioned ( 353 a int, 354 EXCLUDE USING gist (a WITH &&) 355) PARTITION BY RANGE (a); 356 357-- prevent using prohibited expressions in the key 358CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; 359CREATE TABLE partitioned ( 360 a int 361) PARTITION BY RANGE (retset(a)); 362DROP FUNCTION retset(int); 363 364CREATE TABLE partitioned ( 365 a int 366) PARTITION BY RANGE ((avg(a))); 367 368CREATE TABLE partitioned ( 369 a int, 370 b int 371) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b))); 372 373CREATE TABLE partitioned ( 374 a int 375) PARTITION BY LIST ((a LIKE (SELECT 1))); 376 377CREATE TABLE partitioned ( 378 a int 379) PARTITION BY RANGE ((42)); 380 381CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; 382CREATE TABLE partitioned ( 383 a int 384) PARTITION BY RANGE (const_func()); 385DROP FUNCTION const_func(); 386 387-- only accept valid partitioning strategy 388CREATE TABLE partitioned ( 389 a int 390) PARTITION BY MAGIC (a); 391 392-- specified column must be present in the table 393CREATE TABLE partitioned ( 394 a int 395) PARTITION BY RANGE (b); 396 397-- cannot use system columns in partition key 398CREATE TABLE partitioned ( 399 a int 400) PARTITION BY RANGE (xmin); 401 402-- cannot use pseudotypes 403CREATE TABLE partitioned ( 404 a int, 405 b int 406) PARTITION BY RANGE (((a, b))); 407CREATE TABLE partitioned ( 408 a int, 409 b int 410) PARTITION BY RANGE (a, ('unknown')); 411 412-- functions in key must be immutable 413CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL; 414CREATE TABLE partitioned ( 415 a int 416) PARTITION BY RANGE (immut_func(a)); 417DROP FUNCTION immut_func(int); 418 419-- prevent using columns of unsupported types in key (type must have a btree operator class) 420CREATE TABLE partitioned ( 421 a point 422) PARTITION BY LIST (a); 423CREATE TABLE partitioned ( 424 a point 425) PARTITION BY LIST (a point_ops); 426CREATE TABLE partitioned ( 427 a point 428) PARTITION BY RANGE (a); 429CREATE TABLE partitioned ( 430 a point 431) PARTITION BY RANGE (a point_ops); 432 433-- cannot add NO INHERIT constraints to partitioned tables 434CREATE TABLE partitioned ( 435 a int, 436 CONSTRAINT check_a CHECK (a > 0) NO INHERIT 437) PARTITION BY RANGE (a); 438 439-- some checks after successful creation of a partitioned table 440CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL; 441 442CREATE TABLE partitioned ( 443 a int, 444 b int, 445 c text, 446 d text 447) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C"); 448 449-- check relkind 450SELECT relkind FROM pg_class WHERE relname = 'partitioned'; 451 452-- prevent a function referenced in partition key from being dropped 453DROP FUNCTION plusone(int); 454 455-- partitioned table cannot participate in regular inheritance 456CREATE TABLE partitioned2 ( 457 a int, 458 b text 459) PARTITION BY RANGE ((a+1), substr(b, 1, 5)); 460CREATE TABLE fail () INHERITS (partitioned2); 461 462-- Partition key in describe output 463\d partitioned 464\d+ partitioned2 465 466INSERT INTO partitioned2 VALUES (1, 'hello'); 467CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc'); 468\d+ part2_1 469 470DROP TABLE partitioned, partitioned2; 471 472-- check reference to partitioned table's rowtype in partition descriptor 473create table partitioned (a int, b int) 474 partition by list ((row(a, b)::partitioned)); 475create table partitioned1 476 partition of partitioned for values in ('(1,2)'::partitioned); 477create table partitioned2 478 partition of partitioned for values in ('(2,4)'::partitioned); 479explain (costs off) 480select * from partitioned where row(a,b)::partitioned = '(1,2)'::partitioned; 481drop table partitioned; 482 483-- whole-row Var in partition key works too 484create table partitioned (a int, b int) 485 partition by list ((partitioned)); 486create table partitioned1 487 partition of partitioned for values in ('(1,2)'); 488create table partitioned2 489 partition of partitioned for values in ('(2,4)'); 490explain (costs off) 491select * from partitioned where partitioned = '(1,2)'::partitioned; 492\d+ partitioned1 493drop table partitioned; 494 495-- check that dependencies of partition columns are handled correctly 496create domain intdom1 as int; 497 498create table partitioned ( 499 a intdom1, 500 b text 501) partition by range (a); 502 503alter table partitioned drop column a; -- fail 504 505drop domain intdom1; -- fail, requires cascade 506 507drop domain intdom1 cascade; 508 509table partitioned; -- gone 510 511-- likewise for columns used in partition expressions 512create domain intdom1 as int; 513 514create table partitioned ( 515 a intdom1, 516 b text 517) partition by range (plusone(a)); 518 519alter table partitioned drop column a; -- fail 520 521drop domain intdom1; -- fail, requires cascade 522 523drop domain intdom1 cascade; 524 525table partitioned; -- gone 526 527 528-- 529-- Partitions 530-- 531 532-- check partition bound syntax 533 534CREATE TABLE list_parted ( 535 a int 536) PARTITION BY LIST (a); 537CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1'); 538CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); 539CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); 540CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); 541\d+ list_parted 542 543-- forbidden expressions for partition bound with list partitioned table 544CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename); 545CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename); 546CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a); 547CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a)); 548CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename)); 549CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1)); 550CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1)); 551CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (generate_series(4, 6)); 552CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "POSIX"); 553 554-- syntax does not allow empty list of values for list partitions 555CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (); 556-- trying to specify range for list partitioned table 557CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2); 558-- trying to specify modulus and remainder for list partitioned table 559CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 560 561-- check default partition cannot be created more than once 562CREATE TABLE part_default PARTITION OF list_parted DEFAULT; 563CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT; 564 565-- specified literal can't be cast to the partition column data type 566CREATE TABLE bools ( 567 a bool 568) PARTITION BY LIST (a); 569CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1); 570DROP TABLE bools; 571 572-- specified literal can be cast, and the cast might not be immutable 573CREATE TABLE moneyp ( 574 a money 575) PARTITION BY LIST (a); 576CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10); 577CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11'); 578CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int); 579DROP TABLE moneyp; 580 581-- cast is immutable 582CREATE TABLE bigintp ( 583 a bigint 584) PARTITION BY LIST (a); 585CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10); 586-- fails due to overlap: 587CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10'); 588DROP TABLE bigintp; 589 590CREATE TABLE range_parted ( 591 a date 592) PARTITION BY RANGE (a); 593 594-- forbidden expressions for partition bounds with range partitioned table 595CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 596 FOR VALUES FROM (somename) TO ('2019-01-01'); 597CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 598 FOR VALUES FROM (somename.somename) TO ('2019-01-01'); 599CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 600 FOR VALUES FROM (a) TO ('2019-01-01'); 601CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 602 FOR VALUES FROM (max(a)) TO ('2019-01-01'); 603CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 604 FOR VALUES FROM (max(somename)) TO ('2019-01-01'); 605CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 606 FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01'); 607CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 608 FOR VALUES FROM ((select 1)) TO ('2019-01-01'); 609CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 610 FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01'); 611 612-- trying to specify list for range partitioned table 613CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a'); 614-- trying to specify modulus and remainder for range partitioned table 615CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 616-- each of start and end bounds must have same number of values as the 617-- length of the partition key 618CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z'); 619CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1); 620 621-- cannot specify null values in range bounds 622CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue); 623 624-- trying to specify modulus and remainder for range partitioned table 625CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 626 627-- check partition bound syntax for the hash partition 628CREATE TABLE hash_parted ( 629 a int 630) PARTITION BY HASH (a); 631CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0); 632CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1); 633CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2); 634CREATE TABLE hpart_4 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 3); 635-- modulus 25 is factor of modulus of 50 but 10 is not a factor of 25. 636CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 25, REMAINDER 3); 637-- previous modulus 50 is factor of 150 but this modulus is not a factor of next modulus 200. 638CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3); 639-- overlapping remainders 640CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 100, REMAINDER 3); 641-- trying to specify range for the hash partitioned table 642CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z'); 643-- trying to specify list value for the hash partitioned table 644CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000); 645 646-- trying to create default partition for the hash partitioned table 647CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT; 648 649-- check if compatible with the specified parent 650 651-- cannot create as partition of a non-partitioned table 652CREATE TABLE unparted ( 653 a int 654); 655CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a'); 656CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1); 657DROP TABLE unparted; 658 659-- cannot create a permanent rel as partition of a temp rel 660CREATE TEMP TABLE temp_parted ( 661 a int 662) PARTITION BY LIST (a); 663CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); 664DROP TABLE temp_parted; 665 666-- check for partition bound overlap and other invalid specifications 667 668CREATE TABLE list_parted2 ( 669 a varchar 670) PARTITION BY LIST (a); 671CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z'); 672CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b'); 673CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT; 674 675CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null); 676CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c'); 677-- check default partition overlap 678INSERT INTO list_parted2 VALUES('X'); 679CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y'); 680 681CREATE TABLE range_parted2 ( 682 a int 683) PARTITION BY RANGE (a); 684 685-- trying to create range partition with empty range 686CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0); 687-- note that the range '[1, 1)' has no elements 688CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1); 689 690CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1); 691CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2); 692CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10); 693CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (-1) TO (1); 694CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue); 695CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30); 696CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40); 697CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30); 698CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50); 699 700-- Create a default partition for range partitioned table 701CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT; 702 703-- More than one default partition is not allowed, so this should give error 704CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT; 705 706-- Check if the range for default partitions overlap 707INSERT INTO range_parted2 VALUES (85); 708CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90); 709CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100); 710 711-- now check for multi-column range partition key 712CREATE TABLE range_parted3 ( 713 a int, 714 b int 715) PARTITION BY RANGE (a, (b+1)); 716 717CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue); 718CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1); 719 720CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1); 721CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10); 722CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue); 723CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20); 724CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT; 725 726-- cannot create a partition that says column b is allowed to range 727-- from -infinity to +infinity, while there exist partitions that have 728-- more specific ranges 729CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue); 730 731-- check for partition bound overlap and other invalid specifications for the hash partition 732CREATE TABLE hash_parted2 ( 733 a varchar 734) PARTITION BY HASH (a); 735CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2); 736CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0); 737CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4); 738CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5); 739-- overlap with part_4 740CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1); 741-- modulus must be greater than zero 742CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1); 743-- remainder must be greater than or equal to zero and less than modulus 744CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8); 745 746-- check schema propagation from parent 747 748CREATE TABLE parted ( 749 a text, 750 b int NOT NULL DEFAULT 0, 751 CONSTRAINT check_a CHECK (length(a) > 0) 752) PARTITION BY LIST (a); 753 754CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a'); 755 756-- only inherited attributes (never local ones) 757SELECT attname, attislocal, attinhcount FROM pg_attribute 758 WHERE attrelid = 'part_a'::regclass and attnum > 0 759 ORDER BY attnum; 760 761-- able to specify column default, column constraint, and table constraint 762 763-- first check the "column specified more than once" error 764CREATE TABLE part_b PARTITION OF parted ( 765 b NOT NULL, 766 b DEFAULT 1, 767 b CHECK (b >= 0), 768 CONSTRAINT check_a CHECK (length(a) > 0) 769) FOR VALUES IN ('b'); 770 771CREATE TABLE part_b PARTITION OF parted ( 772 b NOT NULL DEFAULT 1, 773 CONSTRAINT check_a CHECK (length(a) > 0), 774 CONSTRAINT check_b CHECK (b >= 0) 775) FOR VALUES IN ('b'); 776-- conislocal should be false for any merged constraints, true otherwise 777SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount; 778 779-- Once check_b is added to the parent, it should be made non-local for part_b 780ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0); 781SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; 782 783-- Neither check_a nor check_b are droppable from part_b 784ALTER TABLE part_b DROP CONSTRAINT check_a; 785ALTER TABLE part_b DROP CONSTRAINT check_b; 786 787-- And dropping it from parted should leave no trace of them on part_b, unlike 788-- traditional inheritance where they will be left behind, because they would 789-- be local constraints. 790ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b; 791SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; 792 793-- specify PARTITION BY for a partition 794CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); 795CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b)); 796 797-- create a level-2 partition 798CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); 799 800-- check that NOT NULL and default value are inherited correctly 801create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a); 802create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1); 803insert into parted_notnull_inh_test (b) values (null); 804-- note that while b's default is overriden, a's default is preserved 805\d parted_notnull_inh_test1 806drop table parted_notnull_inh_test; 807 808-- check that collations are assigned in partition bound expressions 809create table parted_boolean_col (a bool, b text) partition by list(a); 810create table parted_boolean_less partition of parted_boolean_col 811 for values in ('foo' < 'bar'); 812create table parted_boolean_greater partition of parted_boolean_col 813 for values in ('foo' > 'bar'); 814drop table parted_boolean_col; 815 816-- check for a conflicting COLLATE clause 817create table parted_collate_must_match (a text collate "C", b text collate "C") 818 partition by range (a); 819-- on the partition key 820create table parted_collate_must_match1 partition of parted_collate_must_match 821 (a collate "POSIX") for values from ('a') to ('m'); 822-- on another column 823create table parted_collate_must_match2 partition of parted_collate_must_match 824 (b collate "POSIX") for values from ('m') to ('z'); 825drop table parted_collate_must_match; 826 827-- check that non-matching collations for partition bound 828-- expressions are coerced to the right collation 829 830create table test_part_coll_posix (a text) partition by range (a collate "POSIX"); 831-- ok, collation is implicitly coerced 832create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g'); 833-- ok 834create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m'); 835-- ok, collation is implicitly coerced 836create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s'); 837-- ok; partition collation silently overrides the default collation of type 'name' 838create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z'); 839 840drop table test_part_coll_posix; 841 842-- Partition bound in describe output 843\d+ part_b 844 845-- Both partition bound and partition key in describe output 846\d+ part_c 847 848-- a level-2 partition's constraint will include the parent's expressions 849\d+ part_c_1_10 850 851-- Show partition count in the parent's describe output 852-- Tempted to include \d+ output listing partitions with bound info but 853-- output could vary depending on the order in which partition oids are 854-- returned. 855\d parted 856\d hash_parted 857 858-- check that we get the expected partition constraints 859CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c); 860CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE); 861\d+ unbounded_range_part 862DROP TABLE unbounded_range_part; 863CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE); 864\d+ range_parted4_1 865CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE); 866\d+ range_parted4_2 867CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE); 868\d+ range_parted4_3 869DROP TABLE range_parted4; 870 871-- user-defined operator class in partition key 872CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql 873 AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$; 874CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS 875 OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4), 876 OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4), 877 OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4); 878CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops); 879CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000); 880INSERT INTO partkey_t VALUES (100); 881INSERT INTO partkey_t VALUES (200); 882 883-- cleanup 884DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; 885DROP TABLE partkey_t, hash_parted, hash_parted2; 886DROP OPERATOR CLASS test_int4_ops USING btree; 887DROP FUNCTION my_int4_sort(int4,int4); 888 889-- comments on partitioned tables columns 890CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a); 891COMMENT ON TABLE parted_col_comment IS 'Am partitioned table'; 892COMMENT ON COLUMN parted_col_comment.a IS 'Partition key'; 893SELECT obj_description('parted_col_comment'::regclass); 894\d+ parted_col_comment 895DROP TABLE parted_col_comment; 896 897-- list partitioning on array type column 898CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a); 899CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}'); 900\d+ arrlp12 901DROP TABLE arrlp; 902 903-- partition on boolean column 904create table boolspart (a bool) partition by list (a); 905create table boolspart_t partition of boolspart for values in (true); 906create table boolspart_f partition of boolspart for values in (false); 907\d+ boolspart 908drop table boolspart; 909 910-- partitions mixing temporary and permanent relations 911create table perm_parted (a int) partition by list (a); 912create temporary table temp_parted (a int) partition by list (a); 913create table perm_part partition of temp_parted default; -- error 914create temp table temp_part partition of perm_parted default; -- error 915create temp table temp_part partition of temp_parted default; -- ok 916drop table perm_parted cascade; 917drop table temp_parted cascade; 918 919-- check that adding partitions to a table while it is being used is prevented 920create table tab_part_create (a int) partition by list (a); 921create or replace function func_part_create() returns trigger 922 language plpgsql as $$ 923 begin 924 execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)'; 925 return null; 926 end $$; 927create trigger trig_part_create before insert on tab_part_create 928 for each statement execute procedure func_part_create(); 929insert into tab_part_create values (1); 930drop table tab_part_create; 931drop function func_part_create(); 932 933-- test using a volatile expression as partition bound 934create table volatile_partbound_test (partkey timestamp) partition by range (partkey); 935create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp); 936create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue); 937-- this should go into the partition volatile_partbound_test2 938insert into volatile_partbound_test values (current_timestamp); 939select tableoid::regclass from volatile_partbound_test; 940drop table volatile_partbound_test; 941 942-- test the case where a check constraint on default partition allows 943-- to avoid scanning it when adding a new partition 944create table defcheck (a int, b int) partition by list (b); 945create table defcheck_def (a int, c int, b int); 946alter table defcheck_def drop c; 947alter table defcheck attach partition defcheck_def default; 948alter table defcheck_def add check (b <= 0 and b is not null); 949create table defcheck_1 partition of defcheck for values in (1, null); 950 951-- test that complex default partition constraints are enforced correctly 952insert into defcheck_def values (0, 0); 953create table defcheck_0 partition of defcheck for values in (0); 954drop table defcheck; 955 956-- tests of column drop with partition tables and indexes using 957-- predicates and expressions. 958create table part_column_drop ( 959 useless_1 int, 960 id int, 961 useless_2 int, 962 d int, 963 b int, 964 useless_3 int 965) partition by range (id); 966alter table part_column_drop drop column useless_1; 967alter table part_column_drop drop column useless_2; 968alter table part_column_drop drop column useless_3; 969create index part_column_drop_b_pred on part_column_drop(b) where b = 1; 970create index part_column_drop_b_expr on part_column_drop((b = 1)); 971create index part_column_drop_d_pred on part_column_drop(d) where d = 2; 972create index part_column_drop_d_expr on part_column_drop((d = 2)); 973create table part_column_drop_1_10 partition of 974 part_column_drop for values from (1) to (10); 975\d part_column_drop 976\d part_column_drop_1_10 977drop table part_column_drop; 978