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-- 322-- Partitioned tables 323-- 324 325-- cannot combine INHERITS and PARTITION BY (although grammar allows) 326CREATE TABLE partitioned ( 327 a int 328) INHERITS (some_table) PARTITION BY LIST (a); 329 330-- cannot use more than 1 column as partition key for list partitioned table 331CREATE TABLE partitioned ( 332 a1 int, 333 a2 int 334) PARTITION BY LIST (a1, a2); -- fail 335 336-- unsupported constraint type for partitioned tables 337CREATE TABLE partitioned ( 338 a int, 339 EXCLUDE USING gist (a WITH &&) 340) PARTITION BY RANGE (a); 341 342-- prevent using prohibited expressions in the key 343CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; 344CREATE TABLE partitioned ( 345 a int 346) PARTITION BY RANGE (retset(a)); 347DROP FUNCTION retset(int); 348 349CREATE TABLE partitioned ( 350 a int 351) PARTITION BY RANGE ((avg(a))); 352 353CREATE TABLE partitioned ( 354 a int, 355 b int 356) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b))); 357 358CREATE TABLE partitioned ( 359 a int 360) PARTITION BY LIST ((a LIKE (SELECT 1))); 361 362CREATE TABLE partitioned ( 363 a int 364) PARTITION BY RANGE (('a')); 365 366CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; 367CREATE TABLE partitioned ( 368 a int 369) PARTITION BY RANGE (const_func()); 370DROP FUNCTION const_func(); 371 372-- only accept valid partitioning strategy 373CREATE TABLE partitioned ( 374 a int 375) PARTITION BY MAGIC (a); 376 377-- specified column must be present in the table 378CREATE TABLE partitioned ( 379 a int 380) PARTITION BY RANGE (b); 381 382-- cannot use system columns in partition key 383CREATE TABLE partitioned ( 384 a int 385) PARTITION BY RANGE (xmin); 386 387-- functions in key must be immutable 388CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL; 389CREATE TABLE partitioned ( 390 a int 391) PARTITION BY RANGE (immut_func(a)); 392DROP FUNCTION immut_func(int); 393 394-- cannot contain whole-row references 395CREATE TABLE partitioned ( 396 a int 397) PARTITION BY RANGE ((partitioned)); 398 399-- prevent using columns of unsupported types in key (type must have a btree operator class) 400CREATE TABLE partitioned ( 401 a point 402) PARTITION BY LIST (a); 403CREATE TABLE partitioned ( 404 a point 405) PARTITION BY LIST (a point_ops); 406CREATE TABLE partitioned ( 407 a point 408) PARTITION BY RANGE (a); 409CREATE TABLE partitioned ( 410 a point 411) PARTITION BY RANGE (a point_ops); 412 413-- cannot add NO INHERIT constraints to partitioned tables 414CREATE TABLE partitioned ( 415 a int, 416 CONSTRAINT check_a CHECK (a > 0) NO INHERIT 417) PARTITION BY RANGE (a); 418 419-- some checks after successful creation of a partitioned table 420CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL; 421 422CREATE TABLE partitioned ( 423 a int, 424 b int, 425 c text, 426 d text 427) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C"); 428 429-- check relkind 430SELECT relkind FROM pg_class WHERE relname = 'partitioned'; 431 432-- prevent a function referenced in partition key from being dropped 433DROP FUNCTION plusone(int); 434 435-- partitioned table cannot participate in regular inheritance 436CREATE TABLE partitioned2 ( 437 a int, 438 b text 439) PARTITION BY RANGE ((a+1), substr(b, 1, 5)); 440CREATE TABLE fail () INHERITS (partitioned2); 441 442-- Partition key in describe output 443\d partitioned 444\d+ partitioned2 445 446INSERT INTO partitioned2 VALUES (1, 'hello'); 447CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc'); 448\d+ part2_1 449 450DROP TABLE partitioned, partitioned2; 451 452-- 453-- Partitions 454-- 455 456-- check partition bound syntax 457 458CREATE TABLE list_parted ( 459 a int 460) PARTITION BY LIST (a); 461CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1'); 462CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); 463CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); 464CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); 465\d+ list_parted 466 467-- forbidden expressions for partition bound with list partitioned table 468CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename); 469CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename); 470CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a); 471CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a)); 472CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename)); 473CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1)); 474CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN ((select 1)); 475CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (generate_series(4, 6)); 476 477-- syntax does not allow empty list of values for list partitions 478CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (); 479-- trying to specify range for list partitioned table 480CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2); 481-- trying to specify modulus and remainder for list partitioned table 482CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 483 484-- check default partition cannot be created more than once 485CREATE TABLE part_default PARTITION OF list_parted DEFAULT; 486CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT; 487 488-- specified literal can't be cast to the partition column data type 489CREATE TABLE bools ( 490 a bool 491) PARTITION BY LIST (a); 492CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1); 493DROP TABLE bools; 494 495-- specified literal can be cast, and the cast might not be immutable 496CREATE TABLE moneyp ( 497 a money 498) PARTITION BY LIST (a); 499CREATE TABLE moneyp_10 PARTITION OF moneyp FOR VALUES IN (10); 500CREATE TABLE moneyp_11 PARTITION OF moneyp FOR VALUES IN ('11'); 501CREATE TABLE moneyp_12 PARTITION OF moneyp FOR VALUES IN (to_char(12, '99')::int); 502DROP TABLE moneyp; 503 504-- cast is immutable 505CREATE TABLE bigintp ( 506 a bigint 507) PARTITION BY LIST (a); 508CREATE TABLE bigintp_10 PARTITION OF bigintp FOR VALUES IN (10); 509-- fails due to overlap: 510CREATE TABLE bigintp_10_2 PARTITION OF bigintp FOR VALUES IN ('10'); 511DROP TABLE bigintp; 512 513CREATE TABLE range_parted ( 514 a date 515) PARTITION BY RANGE (a); 516 517-- forbidden expressions for partition bounds with range partitioned table 518CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 519 FOR VALUES FROM (somename) TO ('2019-01-01'); 520CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 521 FOR VALUES FROM (somename.somename) TO ('2019-01-01'); 522CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 523 FOR VALUES FROM (a) TO ('2019-01-01'); 524CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 525 FOR VALUES FROM (max(a)) TO ('2019-01-01'); 526CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 527 FOR VALUES FROM (max(somename)) TO ('2019-01-01'); 528CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 529 FOR VALUES FROM (max('2019-02-01'::date)) TO ('2019-01-01'); 530CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 531 FOR VALUES FROM ((select 1)) TO ('2019-01-01'); 532CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted 533 FOR VALUES FROM (generate_series(1, 3)) TO ('2019-01-01'); 534 535-- trying to specify list for range partitioned table 536CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a'); 537-- trying to specify modulus and remainder for range partitioned table 538CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 539-- each of start and end bounds must have same number of values as the 540-- length of the partition key 541CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z'); 542CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1); 543 544-- cannot specify null values in range bounds 545CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (maxvalue); 546 547-- trying to specify modulus and remainder for range partitioned table 548CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES WITH (MODULUS 10, REMAINDER 1); 549 550-- check partition bound syntax for the hash partition 551CREATE TABLE hash_parted ( 552 a int 553) PARTITION BY HASH (a); 554CREATE TABLE hpart_1 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 10, REMAINDER 0); 555CREATE TABLE hpart_2 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 50, REMAINDER 1); 556CREATE TABLE hpart_3 PARTITION OF hash_parted FOR VALUES WITH (MODULUS 200, REMAINDER 2); 557-- modulus 25 is factor of modulus of 50 but 10 is not factor of 25. 558CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 25, REMAINDER 3); 559-- previous modulus 50 is factor of 150 but this modulus is not factor of next modulus 200. 560CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 150, REMAINDER 3); 561-- trying to specify range for the hash partitioned table 562CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z'); 563-- trying to specify list value for the hash partitioned table 564CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000); 565 566-- trying to create default partition for the hash partitioned table 567CREATE TABLE fail_default_part PARTITION OF hash_parted DEFAULT; 568 569-- check if compatible with the specified parent 570 571-- cannot create as partition of a non-partitioned table 572CREATE TABLE unparted ( 573 a int 574); 575CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a'); 576CREATE TABLE fail_part PARTITION OF unparted FOR VALUES WITH (MODULUS 2, REMAINDER 1); 577DROP TABLE unparted; 578 579-- cannot create a permanent rel as partition of a temp rel 580CREATE TEMP TABLE temp_parted ( 581 a int 582) PARTITION BY LIST (a); 583CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); 584DROP TABLE temp_parted; 585 586-- check for partition bound overlap and other invalid specifications 587 588CREATE TABLE list_parted2 ( 589 a varchar 590) PARTITION BY LIST (a); 591CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z'); 592CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b'); 593CREATE TABLE list_parted2_def PARTITION OF list_parted2 DEFAULT; 594 595CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null); 596CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c'); 597-- check default partition overlap 598INSERT INTO list_parted2 VALUES('X'); 599CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y'); 600 601CREATE TABLE range_parted2 ( 602 a int 603) PARTITION BY RANGE (a); 604 605-- trying to create range partition with empty range 606CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0); 607-- note that the range '[1, 1)' has no elements 608CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1); 609 610CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (1); 611CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (minvalue) TO (2); 612CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10); 613CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (maxvalue); 614CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30); 615CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40); 616CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30); 617CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50); 618 619-- Create a default partition for range partitioned table 620CREATE TABLE range2_default PARTITION OF range_parted2 DEFAULT; 621 622-- More than one default partition is not allowed, so this should give error 623CREATE TABLE fail_default_part PARTITION OF range_parted2 DEFAULT; 624 625-- Check if the range for default partitions overlap 626INSERT INTO range_parted2 VALUES (85); 627CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (80) TO (90); 628CREATE TABLE part4 PARTITION OF range_parted2 FOR VALUES FROM (90) TO (100); 629 630-- now check for multi-column range partition key 631CREATE TABLE range_parted3 ( 632 a int, 633 b int 634) PARTITION BY RANGE (a, (b+1)); 635 636CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, maxvalue); 637CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, minvalue) TO (0, 1); 638 639CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, 1); 640CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10); 641CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, maxvalue); 642CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20); 643CREATE TABLE range3_default PARTITION OF range_parted3 DEFAULT; 644 645-- cannot create a partition that says column b is allowed to range 646-- from -infinity to +infinity, while there exist partitions that have 647-- more specific ranges 648CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, minvalue) TO (1, maxvalue); 649 650-- check for partition bound overlap and other invalid specifications for the hash partition 651CREATE TABLE hash_parted2 ( 652 a varchar 653) PARTITION BY HASH (a); 654CREATE TABLE h2part_1 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 4, REMAINDER 2); 655CREATE TABLE h2part_2 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 0); 656CREATE TABLE h2part_3 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 4); 657CREATE TABLE h2part_4 PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 5); 658-- overlap with part_4 659CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 2, REMAINDER 1); 660-- modulus must be greater than zero 661CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 0, REMAINDER 1); 662-- remainder must be greater than or equal to zero and less than modulus 663CREATE TABLE fail_part PARTITION OF hash_parted2 FOR VALUES WITH (MODULUS 8, REMAINDER 8); 664 665-- check schema propagation from parent 666 667CREATE TABLE parted ( 668 a text, 669 b int NOT NULL DEFAULT 0, 670 CONSTRAINT check_a CHECK (length(a) > 0) 671) PARTITION BY LIST (a); 672 673CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a'); 674 675-- only inherited attributes (never local ones) 676SELECT attname, attislocal, attinhcount FROM pg_attribute 677 WHERE attrelid = 'part_a'::regclass and attnum > 0 678 ORDER BY attnum; 679 680-- able to specify column default, column constraint, and table constraint 681 682-- first check the "column specified more than once" error 683CREATE TABLE part_b PARTITION OF parted ( 684 b NOT NULL, 685 b DEFAULT 1, 686 b CHECK (b >= 0), 687 CONSTRAINT check_a CHECK (length(a) > 0) 688) FOR VALUES IN ('b'); 689 690CREATE TABLE part_b PARTITION OF parted ( 691 b NOT NULL DEFAULT 1, 692 CONSTRAINT check_a CHECK (length(a) > 0), 693 CONSTRAINT check_b CHECK (b >= 0) 694) FOR VALUES IN ('b'); 695-- conislocal should be false for any merged constraints, true otherwise 696SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount; 697 698-- Once check_b is added to the parent, it should be made non-local for part_b 699ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0); 700SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; 701 702-- Neither check_a nor check_b are droppable from part_b 703ALTER TABLE part_b DROP CONSTRAINT check_a; 704ALTER TABLE part_b DROP CONSTRAINT check_b; 705 706-- And dropping it from parted should leave no trace of them on part_b, unlike 707-- traditional inheritance where they will be left behind, because they would 708-- be local constraints. 709ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b; 710SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; 711 712-- specify PARTITION BY for a partition 713CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); 714CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b)); 715 716-- create a level-2 partition 717CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10); 718 719-- check that NOT NULL and default value are inherited correctly 720create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a); 721create table parted_notnull_inh_test1 partition of parted_notnull_inh_test (a not null, b default 1) for values in (1); 722insert into parted_notnull_inh_test (b) values (null); 723-- note that while b's default is overriden, a's default is preserved 724\d parted_notnull_inh_test1 725drop table parted_notnull_inh_test; 726 727-- check for a conflicting COLLATE clause 728create table parted_collate_must_match (a text collate "C", b text collate "C") 729 partition by range (a); 730-- on the partition key 731create table parted_collate_must_match1 partition of parted_collate_must_match 732 (a collate "POSIX") for values from ('a') to ('m'); 733-- on another column 734create table parted_collate_must_match2 partition of parted_collate_must_match 735 (b collate "POSIX") for values from ('m') to ('z'); 736drop table parted_collate_must_match; 737 738-- check that specifying incompatible collations for partition bound 739-- expressions fails promptly 740 741create table test_part_coll_posix (a text) partition by range (a collate "POSIX"); 742-- fail 743create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "C") to ('g'); 744-- ok 745create table test_part_coll partition of test_part_coll_posix for values from ('a' collate "POSIX") to ('g'); 746-- ok 747create table test_part_coll2 partition of test_part_coll_posix for values from ('g') to ('m'); 748 749-- using a cast expression uses the target type's default collation 750 751-- fail 752create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "C") to ('s'); 753-- ok 754create table test_part_coll_cast partition of test_part_coll_posix for values from (name 'm' collate "POSIX") to ('s'); 755-- ok; partition collation silently overrides the default collation of type 'name' 756create table test_part_coll_cast2 partition of test_part_coll_posix for values from (name 's') to ('z'); 757 758drop table test_part_coll_posix; 759 760-- Partition bound in describe output 761\d+ part_b 762 763-- Both partition bound and partition key in describe output 764\d+ part_c 765 766-- a level-2 partition's constraint will include the parent's expressions 767\d+ part_c_1_10 768 769-- Show partition count in the parent's describe output 770-- Tempted to include \d+ output listing partitions with bound info but 771-- output could vary depending on the order in which partition oids are 772-- returned. 773\d parted 774\d hash_parted 775 776-- check that we get the expected partition constraints 777CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c); 778CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE); 779\d+ unbounded_range_part 780DROP TABLE unbounded_range_part; 781CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE); 782\d+ range_parted4_1 783CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE); 784\d+ range_parted4_2 785CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE); 786\d+ range_parted4_3 787DROP TABLE range_parted4; 788 789-- user-defined operator class in partition key 790CREATE FUNCTION my_int4_sort(int4,int4) RETURNS int LANGUAGE sql 791 AS $$ SELECT CASE WHEN $1 = $2 THEN 0 WHEN $1 > $2 THEN 1 ELSE -1 END; $$; 792CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING btree AS 793 OPERATOR 1 < (int4,int4), OPERATOR 2 <= (int4,int4), 794 OPERATOR 3 = (int4,int4), OPERATOR 4 >= (int4,int4), 795 OPERATOR 5 > (int4,int4), FUNCTION 1 my_int4_sort(int4,int4); 796CREATE TABLE partkey_t (a int4) PARTITION BY RANGE (a test_int4_ops); 797CREATE TABLE partkey_t_1 PARTITION OF partkey_t FOR VALUES FROM (0) TO (1000); 798INSERT INTO partkey_t VALUES (100); 799INSERT INTO partkey_t VALUES (200); 800 801-- cleanup 802DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3; 803DROP TABLE partkey_t, hash_parted, hash_parted2; 804DROP OPERATOR CLASS test_int4_ops USING btree; 805DROP FUNCTION my_int4_sort(int4,int4); 806 807-- comments on partitioned tables columns 808CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a); 809COMMENT ON TABLE parted_col_comment IS 'Am partitioned table'; 810COMMENT ON COLUMN parted_col_comment.a IS 'Partition key'; 811SELECT obj_description('parted_col_comment'::regclass); 812\d+ parted_col_comment 813DROP TABLE parted_col_comment; 814 815-- list partitioning on array type column 816CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a); 817CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}'); 818\d+ arrlp12 819DROP TABLE arrlp; 820 821-- partition on boolean column 822create table boolspart (a bool) partition by list (a); 823create table boolspart_t partition of boolspart for values in (true); 824create table boolspart_f partition of boolspart for values in (false); 825\d+ boolspart 826drop table boolspart; 827 828-- partitions mixing temporary and permanent relations 829create table perm_parted (a int) partition by list (a); 830create temporary table temp_parted (a int) partition by list (a); 831create table perm_part partition of temp_parted default; -- error 832create temp table temp_part partition of perm_parted default; -- error 833create temp table temp_part partition of temp_parted default; -- ok 834drop table perm_parted cascade; 835drop table temp_parted cascade; 836 837-- check that adding partitions to a table while it is being used is prevented 838create table tab_part_create (a int) partition by list (a); 839create or replace function func_part_create() returns trigger 840 language plpgsql as $$ 841 begin 842 execute 'create table tab_part_create_1 partition of tab_part_create for values in (1)'; 843 return null; 844 end $$; 845create trigger trig_part_create before insert on tab_part_create 846 for each statement execute procedure func_part_create(); 847insert into tab_part_create values (1); 848drop table tab_part_create; 849drop function func_part_create(); 850 851-- test using a volatile expression as partition bound 852create table volatile_partbound_test (partkey timestamp) partition by range (partkey); 853create table volatile_partbound_test1 partition of volatile_partbound_test for values from (minvalue) to (current_timestamp); 854create table volatile_partbound_test2 partition of volatile_partbound_test for values from (current_timestamp) to (maxvalue); 855-- this should go into the partition volatile_partbound_test2 856insert into volatile_partbound_test values (current_timestamp); 857select tableoid::regclass from volatile_partbound_test; 858drop table volatile_partbound_test; 859