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