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