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