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' collate "POSIX"); 553CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((1+1) collate "POSIX"); 554 555-- syntax does not allow empty list of values for list partitions 556CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (); 557-- trying to specify range for list partitioned table 558CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2); 559-- trying to specify modulus and remainder for list partitioned table 560CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 561 562-- check default partition cannot be created more than once 563CREATE TABLE part_default PARTITION OF list_parted DEFAULT; 564CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT; 565 566-- specified literal can't be cast to the partition column data type 567CREATE TABLE bools ( 568 a bool 569) PARTITION BY LIST (a); 570CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1); 571DROP TABLE bools; 572 573-- specified literal can be cast, and the cast might not be immutable 574CREATE TABLE moneyp ( 575 a money 576) PARTITION BY LIST (a); 577CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10); 578CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11'); 579CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int); 580DROP TABLE moneyp; 581 582-- cast is immutable 583CREATE TABLE bigintp ( 584 a bigint 585) PARTITION BY LIST (a); 586CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10); 587-- fails due to overlap: 588CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10'); 589DROP TABLE bigintp; 590 591CREATE TABLE range_parted ( 592 a date 593) PARTITION BY RANGE (a); 594 595-- forbidden expressions for partition bounds with range partitioned table 596CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 597 FOR VALUES FROM (somename) TO ('2019-01-01'); 598CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 599 FOR VALUES FROM (somename.somename) TO ('2019-01-01'); 600CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 601 FOR VALUES FROM (a) TO ('2019-01-01'); 602CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 603 FOR VALUES FROM (max(a)) TO ('2019-01-01'); 604CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 605 FOR VALUES FROM (max(somename)) TO ('2019-01-01'); 606CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 607 FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01'); 608CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 609 FOR VALUES FROM ((select 1)) TO ('2019-01-01'); 610CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 611 FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01'); 612 613-- trying to specify list for range partitioned table 614CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a'); 615-- trying to specify modulus and remainder for range partitioned table 616CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 617-- each of start and end bounds must have same number of values as the 618-- length of the partition key 619CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z'); 620CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1); 621 622-- cannot specify null values in range bounds 623CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue); 624 625-- trying to specify modulus and remainder for range partitioned table 626CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 627 628-- check partition bound syntax for the hash partition 629CREATE TABLE hash_parted ( 630 a int 631) PARTITION BY HASH (a); 632CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0); 633CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1); 634CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2); 635-- modulus 25 is factor of modulus of 50 but 10 is not 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 factor of next modulus 200. 638CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3); 639-- trying to specify range for the hash partitioned table 640CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z'); 641-- trying to specify list value for the hash partitioned table 642CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000); 643 644-- trying to create default partition for the hash partitioned table 645CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT; 646 647-- check if compatible with the specified parent 648 649-- cannot create as partition of a non-partitioned table 650CREATE TABLE unparted ( 651 a int 652); 653CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a'); 654CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1); 655DROP TABLE unparted; 656 657-- cannot create a permanent rel as partition of a temp rel 658CREATE TEMP TABLE temp_parted ( 659 a int 660) PARTITION BY LIST (a); 661CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); 662DROP TABLE temp_parted; 663 664-- check for partition bound overlap and other invalid specifications 665 666CREATE TABLE list_parted2 ( 667 a varchar 668) PARTITION BY LIST (a); 669CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z'); 670CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b'); 671CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT; 672 673CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null); 674CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c'); 675-- check default partition overlap 676INSERT INTO list_parted2 VALUES('X'); 677CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y'); 678 679CREATE TABLE range_parted2 ( 680 a int 681) PARTITION BY RANGE (a); 682 683-- trying to create range partition with empty range 684CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0); 685-- note that the range '[1, 1)' has no elements 686CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1); 687 688CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1); 689CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2); 690CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10); 691CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue); 692CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30); 693CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40); 694CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30); 695CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50); 696 697-- Create a default partition for range partitioned table 698CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT; 699 700-- More than one default partition is not allowed, so this should give error 701CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT; 702 703-- Check if the range for default partitions overlap 704INSERT INTO range_parted2 VALUES (85); 705CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90); 706CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100); 707 708-- now check for multi-column range partition key 709CREATE TABLE range_parted3 ( 710 a int, 711 b int 712) PARTITION BY RANGE (a, (b+1)); 713 714CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue); 715CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1); 716 717CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1); 718CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10); 719CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue); 720CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20); 721CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT; 722 723-- cannot create a partition that says column b is allowed to range 724-- from -infinity to +infinity, while there exist partitions that have 725-- more specific ranges 726CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue); 727 728-- check for partition bound overlap and other invalid specifications for the hash partition 729CREATE TABLE hash_parted2 ( 730 a varchar 731) PARTITION BY HASH (a); 732CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2); 733CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0); 734CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4); 735CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5); 736-- overlap with part_4 737CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1); 738-- modulus must be greater than zero 739CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1); 740-- remainder must be greater than or equal to zero and less than modulus 741CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8); 742 743-- check schema propagation from parent 744 745CREATE TABLE parted ( 746 a text, 747 b int NOT NULL DEFAULT 0, 748 CONSTRAINT check_a CHECK (length(a) > 0) 749) PARTITION BY LIST (a); 750 751CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a'); 752 753-- only inherited attributes (never local ones) 754SELECT attname, attislocal, attinhcount FROM pg_attribute 755 WHERE attrelid = 'part_a'::regclass and attnum > 0 756 ORDER BY attnum; 757 758-- able to specify column default, column constraint, and table constraint 759 760-- first check the "column specified more than once" error 761CREATE TABLE part_b PARTITION OF parted ( 762 b NOT NULL, 763 b DEFAULT 1, 764 b CHECK (b >= 0), 765 CONSTRAINT check_a CHECK (length(a) > 0) 766) FOR VALUES IN ('b'); 767 768CREATE TABLE part_b PARTITION OF parted ( 769 b NOT NULL DEFAULT 1, 770 CONSTRAINT check_a CHECK (length(a) > 0), 771 CONSTRAINT check_b CHECK (b >= 0) 772) FOR VALUES IN ('b'); 773-- conislocal should be false for any merged constraints, true otherwise 774SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount; 775 776-- Once check_b is added to the parent, it should be made non-local for part_b 777ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0); 778SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; 779 780-- Neither check_a nor check_b are droppable from part_b 781ALTER TABLE part_b DROP CONSTRAINT check_a; 782ALTER TABLE part_b DROP CONSTRAINT check_b; 783 784-- And dropping it from parted should leave no trace of them on part_b, unlike 785-- traditional inheritance where they will be left behind, because they would 786-- be local constraints. 787ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b; 788SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; 789 790-- specify PARTITION BY for a partition 791CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); 792CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b)); 793 794-- create a level-2 partition 795CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); 796 797-- check that NOT NULL and default value are inherited correctly 798create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a); 799create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1); 800insert into parted_notnull_inh_test (b) values (null); 801-- note that while b's default is overriden, a's default is preserved 802\d parted_notnull_inh_test1 803drop table parted_notnull_inh_test; 804 805-- check that collations are assigned in partition bound expressions 806create table parted_boolean_col (a bool, b text) partition by list(a); 807create table parted_boolean_less partition of parted_boolean_col 808 for values in ('foo' < 'bar'); 809create table parted_boolean_greater partition of parted_boolean_col 810 for values in ('foo' > 'bar'); 811drop table parted_boolean_col; 812 813-- check for a conflicting COLLATE clause 814create table parted_collate_must_match (a text collate "C", b text collate "C") 815 partition by range (a); 816-- on the partition key 817create table parted_collate_must_match1 partition of parted_collate_must_match 818 (a collate "POSIX") for values from ('a') to ('m'); 819-- on another column 820create table parted_collate_must_match2 partition of parted_collate_must_match 821 (b collate "POSIX") for values from ('m') to ('z'); 822drop table parted_collate_must_match; 823 824-- check that specifying incompatible collations for partition bound 825-- expressions fails promptly 826 827create table test_part_coll_posix (a text) partition by range (a collate "POSIX"); 828-- fail 829create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g'); 830-- ok 831create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "POSIX") to ('g'); 832-- ok 833create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m'); 834 835-- using a cast expression uses the target type's default collation 836 837-- fail 838create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s'); 839-- ok 840create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "POSIX") to ('s'); 841-- ok; partition collation silently overrides the default collation of type 'name' 842create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z'); 843 844drop table test_part_coll_posix; 845 846-- Partition bound in describe output 847\d+ part_b 848 849-- Both partition bound and partition key in describe output 850\d+ part_c 851 852-- a level-2 partition's constraint will include the parent's expressions 853\d+ part_c_1_10 854 855-- Show partition count in the parent's describe output 856-- Tempted to include \d+ output listing partitions with bound info but 857-- output could vary depending on the order in which partition oids are 858-- returned. 859\d parted 860\d hash_parted 861 862-- check that we get the expected partition constraints 863CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c); 864CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE); 865\d+ unbounded_range_part 866DROP TABLE unbounded_range_part; 867CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE); 868\d+ range_parted4_1 869CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE); 870\d+ range_parted4_2 871CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE); 872\d+ range_parted4_3 873DROP TABLE range_parted4; 874 875-- user-defined operator class in partition key 876CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql 877 AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$; 878CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS 879 OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4), 880 OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4), 881 OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4); 882CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops); 883CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000); 884INSERT INTO partkey_t VALUES (100); 885INSERT INTO partkey_t VALUES (200); 886 887-- cleanup 888DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; 889DROP TABLE partkey_t, hash_parted, hash_parted2; 890DROP OPERATOR CLASS test_int4_ops USING btree; 891DROP FUNCTION my_int4_sort(int4,int4); 892 893-- comments on partitioned tables columns 894CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a); 895COMMENT ON TABLE parted_col_comment IS 'Am partitioned table'; 896COMMENT ON COLUMN parted_col_comment.a IS 'Partition key'; 897SELECT obj_description('parted_col_comment'::regclass); 898\d+ parted_col_comment 899DROP TABLE parted_col_comment; 900 901-- list partitioning on array type column 902CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a); 903CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}'); 904\d+ arrlp12 905DROP TABLE arrlp; 906 907-- partition on boolean column 908create table boolspart (a bool) partition by list (a); 909create table boolspart_t partition of boolspart for values in (true); 910create table boolspart_f partition of boolspart for values in (false); 911\d+ boolspart 912drop table boolspart; 913 914-- partitions mixing temporary and permanent relations 915create table perm_parted (a int) partition by list (a); 916create temporary table temp_parted (a int) partition by list (a); 917create table perm_part partition of temp_parted default; -- error 918create temp table temp_part partition of perm_parted default; -- error 919create temp table temp_part partition of temp_parted default; -- ok 920drop table perm_parted cascade; 921drop table temp_parted cascade; 922 923-- check that adding partitions to a table while it is being used is prevented 924create table tab_part_create (a int) partition by list (a); 925create or replace function func_part_create() returns trigger 926 language plpgsql as $$ 927 begin 928 execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)'; 929 return null; 930 end $$; 931create trigger trig_part_create before insert on tab_part_create 932 for each statement execute procedure func_part_create(); 933insert into tab_part_create values (1); 934drop table tab_part_create; 935drop function func_part_create(); 936 937-- test using a volatile expression as partition bound 938create table volatile_partbound_test (partkey timestamp) partition by range (partkey); 939create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp); 940create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue); 941-- this should go into the partition volatile_partbound_test2 942insert into volatile_partbound_test values (current_timestamp); 943select tableoid::regclass from volatile_partbound_test; 944drop table volatile_partbound_test; 945 946-- test the case where a check constraint on default partition allows 947-- to avoid scanning it when adding a new partition 948create table defcheck (a int, b int) partition by list (b); 949create table defcheck_def (a int, c int, b int); 950alter table defcheck_def drop c; 951alter table defcheck attach partition defcheck_def default; 952alter table defcheck_def add check (b <= 0 and b is not null); 953create table defcheck_1 partition of defcheck for values in (1, null); 954 955-- test that complex default partition constraints are enforced correctly 956insert into defcheck_def values (0, 0); 957create table defcheck_0 partition of defcheck for values in (0); 958drop table defcheck; 959 960-- tests of column drop with partition tables and indexes using 961-- predicates and expressions. 962create table part_column_drop ( 963 useless_1 int, 964 id int, 965 useless_2 int, 966 d int, 967 b int, 968 useless_3 int 969) partition by range (id); 970alter table part_column_drop drop column useless_1; 971alter table part_column_drop drop column useless_2; 972alter table part_column_drop drop column useless_3; 973create index part_column_drop_b_pred on part_column_drop(b) where b = 1; 974create index part_column_drop_b_expr on part_column_drop((b = 1)); 975create index part_column_drop_d_pred on part_column_drop(d) where d = 2; 976create index part_column_drop_d_expr on part_column_drop((d = 2)); 977create table part_column_drop_1_10 partition of 978 part_column_drop for values from (1) to (10); 979\d part_column_drop 980\d part_column_drop_1_10 981drop table part_column_drop; 982