7 CREATE TABLE hobbies_r (
11 CREATE TABLE equipment_r (
77 ) INHERITS (person) WITH OIDS;
78 CREATE TABLE student (
81 CREATE TABLE stud_emp (
83 ) INHERITS (emp, student);
84 NOTICE: merging multiple inherited definitions of column "name"
85 NOTICE: merging multiple inherited definitions of column "age"
86 NOTICE: merging multiple inherited definitions of column "location"
96 CREATE TABLE slow_emp4000 (
99 CREATE TABLE fast_emp4000 (
106 CREATE TABLE ihighway () INHERITS (road);
107 CREATE TABLE shighway (
110 CREATE TABLE real_city (
116 -- test the "star" operators a bit more thoroughly -- this time,
117 -- throw in lots of NULL fields...
119 -- a is the type root
120 -- b and c inherit from a (one-level single inheritance)
121 -- d inherits from b and c (two-level multiple inheritance)
122 -- e inherits from c (two-level single inheritance)
123 -- f inherits from e (three-level single inheritance)
125 CREATE TABLE a_star (
129 CREATE TABLE b_star (
132 CREATE TABLE c_star (
135 CREATE TABLE d_star (
137 ) INHERITS (b_star, c_star);
138 NOTICE: merging multiple inherited definitions of column "class"
139 NOTICE: merging multiple inherited definitions of column "a"
140 CREATE TABLE e_star (
143 CREATE TABLE f_star (
146 CREATE TABLE aggtest (
150 CREATE TABLE hash_i4_heap (
154 CREATE TABLE hash_name_heap (
158 CREATE TABLE hash_txt_heap (
162 CREATE TABLE hash_f8_heap (
166 -- don't include the hash_ovfl_heap stuff in the distribution
167 -- the data set is too large for what it's worth
169 -- CREATE TABLE hash_ovfl_heap (
173 CREATE TABLE bt_i4_heap (
177 CREATE TABLE bt_name_heap (
181 CREATE TABLE bt_txt_heap (
185 CREATE TABLE bt_f8_heap (
189 CREATE TABLE array_op_test (
194 CREATE TABLE array_index_op_test (
199 CREATE TABLE testjsonb (
202 CREATE TABLE unknowntab (
205 ERROR: column "u" has pseudo-type unknown
206 CREATE TYPE unknown_comptype AS (
209 ERROR: column "u" has pseudo-type unknown
210 CREATE TABLE IF NOT EXISTS test_tsvector(
214 CREATE TABLE IF NOT EXISTS test_tsvector(
217 NOTICE: relation "test_tsvector" already exists, skipping
218 CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK
219 CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK
220 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
221 relname | relkind | relpersistence
222 ----------------+---------+----------------
224 unlogged1_pkey | i | u
226 unlogged2_pkey | i | t
229 REINDEX INDEX unlogged1_pkey;
230 REINDEX INDEX unlogged2_pkey;
231 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname;
232 relname | relkind | relpersistence
233 ----------------+---------+----------------
235 unlogged1_pkey | i | u
237 unlogged2_pkey | i | t
240 DROP TABLE unlogged2;
241 INSERT INTO unlogged1 VALUES (42);
242 CREATE UNLOGGED TABLE public.unlogged2 (a int primary key); -- also OK
243 CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); -- not OK
244 ERROR: only temporary relations may be created in temporary schemas
245 LINE 1: CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key);
247 CREATE TABLE pg_temp.implicitly_temp (a int primary key); -- OK
248 CREATE TEMP TABLE explicitly_temp (a int primary key); -- also OK
249 CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key); -- also OK
250 CREATE TEMP TABLE public.temp_to_perm (a int primary key); -- not OK
251 ERROR: cannot create temporary relation in non-temporary schema
252 LINE 1: CREATE TEMP TABLE public.temp_to_perm (a int primary key);
254 DROP TABLE unlogged1, public.unlogged2;
255 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
256 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
257 ERROR: relation "as_select1" already exists
258 CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
259 NOTICE: relation "as_select1" already exists, skipping
260 DROP TABLE as_select1;
261 -- check that the oid column is added before the primary key is checked
262 CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS;
265 -- Partitioned tables
267 -- cannot combine INHERITS and PARTITION BY (although grammar allows)
268 CREATE TABLE partitioned (
270 ) INHERITS (some_table) PARTITION BY LIST (a);
271 ERROR: cannot create partitioned table as inheritance child
272 -- cannot use more than 1 column as partition key for list partitioned table
273 CREATE TABLE partitioned (
276 ) PARTITION BY LIST (a1, a2); -- fail
277 ERROR: cannot list partition using more than one column
278 -- unsupported constraint type for partitioned tables
279 CREATE TABLE partitioned (
281 ) PARTITION BY RANGE (a);
282 ERROR: primary key constraints are not supported on partitioned tables
283 LINE 2: a int PRIMARY KEY
288 CREATE TABLE partitioned (
289 a int REFERENCES pkrel(a)
290 ) PARTITION BY RANGE (a);
291 ERROR: foreign key constraints are not supported on partitioned tables
292 LINE 2: a int REFERENCES pkrel(a)
295 CREATE TABLE partitioned (
297 ) PARTITION BY RANGE (a);
298 ERROR: unique constraints are not supported on partitioned tables
301 CREATE TABLE partitioned (
303 EXCLUDE USING gist (a WITH &&)
304 ) PARTITION BY RANGE (a);
305 ERROR: exclusion constraints are not supported on partitioned tables
306 LINE 3: EXCLUDE USING gist (a WITH &&)
308 -- prevent column from being used twice in the partition key
309 CREATE TABLE partitioned (
311 ) PARTITION BY RANGE (a, a);
312 ERROR: column "a" appears more than once in partition key
313 -- prevent using prohibited expressions in the key
314 CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
315 CREATE TABLE partitioned (
317 ) PARTITION BY RANGE (retset(a));
318 ERROR: set-returning functions are not allowed in partition key expression
319 DROP FUNCTION retset(int);
320 CREATE TABLE partitioned (
322 ) PARTITION BY RANGE ((avg(a)));
323 ERROR: aggregate functions are not allowed in partition key expression
324 CREATE TABLE partitioned (
327 ) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b)));
328 ERROR: window functions are not allowed in partition key expression
329 CREATE TABLE partitioned (
331 ) PARTITION BY LIST ((a LIKE (SELECT 1)));
332 ERROR: cannot use subquery in partition key expression
333 CREATE TABLE partitioned (
335 ) PARTITION BY RANGE (('a'));
336 ERROR: cannot use constant expression as partition key
337 CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
338 CREATE TABLE partitioned (
340 ) PARTITION BY RANGE (const_func());
341 ERROR: cannot use constant expression as partition key
342 DROP FUNCTION const_func();
343 -- only accept "list" and "range" as partitioning strategy
344 CREATE TABLE partitioned (
346 ) PARTITION BY HASH (a);
347 ERROR: unrecognized partitioning strategy "hash"
348 -- specified column must be present in the table
349 CREATE TABLE partitioned (
351 ) PARTITION BY RANGE (b);
352 ERROR: column "b" named in partition key does not exist
353 -- cannot use system columns in partition key
354 CREATE TABLE partitioned (
356 ) PARTITION BY RANGE (xmin);
357 ERROR: cannot use system column "xmin" in partition key
358 -- functions in key must be immutable
359 CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL;
360 CREATE TABLE partitioned (
362 ) PARTITION BY RANGE (immut_func(a));
363 ERROR: functions in partition key expression must be marked IMMUTABLE
364 DROP FUNCTION immut_func(int);
365 -- cannot contain whole-row references
366 CREATE TABLE partitioned (
368 ) PARTITION BY RANGE ((partitioned));
369 ERROR: partition key expressions cannot contain whole-row references
370 -- prevent using columns of unsupported types in key (type must have a btree operator class)
371 CREATE TABLE partitioned (
373 ) PARTITION BY LIST (a);
374 ERROR: data type point has no default btree operator class
375 HINT: You must specify a btree operator class or define a default btree operator class for the data type.
376 CREATE TABLE partitioned (
378 ) PARTITION BY LIST (a point_ops);
379 ERROR: operator class "point_ops" does not exist for access method "btree"
380 CREATE TABLE partitioned (
382 ) PARTITION BY RANGE (a);
383 ERROR: data type point has no default btree operator class
384 HINT: You must specify a btree operator class or define a default btree operator class for the data type.
385 CREATE TABLE partitioned (
387 ) PARTITION BY RANGE (a point_ops);
388 ERROR: operator class "point_ops" does not exist for access method "btree"
389 -- cannot add NO INHERIT constraints to partitioned tables
390 CREATE TABLE partitioned (
392 CONSTRAINT check_a CHECK (a > 0) NO INHERIT
393 ) PARTITION BY RANGE (a);
394 ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned"
395 -- some checks after successful creation of a partitioned table
396 CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL;
397 CREATE TABLE partitioned (
402 ) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "C");
404 SELECT relkind FROM pg_class WHERE relname = 'partitioned';
410 -- check that range partition key columns are marked NOT NULL
411 SELECT attname, attnotnull FROM pg_attribute
412 WHERE attrelid = 'partitioned'::regclass AND attnum > 0
415 ---------+------------
422 -- prevent a function referenced in partition key from being dropped
423 DROP FUNCTION plusone(int);
424 ERROR: cannot drop function plusone(integer) because other objects depend on it
425 DETAIL: table partitioned depends on function plusone(integer)
426 HINT: Use DROP ... CASCADE to drop the dependent objects too.
427 -- partitioned table cannot participate in regular inheritance
428 CREATE TABLE partitioned2 (
430 ) PARTITION BY LIST ((a+1));
431 CREATE TABLE fail () INHERITS (partitioned2);
432 ERROR: cannot inherit from partitioned table "partitioned2"
433 -- Partition key in describe output
435 Table "public.partitioned"
436 Column | Type | Collation | Nullable | Default
437 --------+---------+-----------+----------+---------
438 a | integer | | not null |
440 c | text | | not null |
441 d | text | | not null |
442 Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")
445 Table "public.partitioned2"
446 Column | Type | Collation | Nullable | Default
447 --------+---------+-----------+----------+---------
449 Partition key: LIST ((a + 1))
451 DROP TABLE partitioned, partitioned2;
455 -- check partition bound syntax
456 CREATE TABLE list_parted (
458 ) PARTITION BY LIST (a);
459 -- syntax allows only string literal, numeric literal and null to be
460 -- specified for a partition bound value
461 CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN ('1');
462 CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
463 CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
464 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (int '1');
465 ERROR: syntax error at or near "int"
466 LINE 1: ... fail_part PARTITION OF list_parted FOR VALUES IN (int '1');
468 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
469 ERROR: syntax error at or near "::"
470 LINE 1: ...fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int);
472 -- syntax does not allow empty list of values for list partitions
473 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
474 ERROR: syntax error at or near ")"
475 LINE 1: ...E TABLE fail_part PARTITION OF list_parted FOR VALUES IN ();
477 -- trying to specify range for list partitioned table
478 CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2);
479 ERROR: invalid bound specification for a list partition
480 LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) T...
482 -- specified literal can't be cast to the partition column data type
485 ) PARTITION BY LIST (a);
486 CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
487 ERROR: specified value cannot be cast to type "boolean" of column "a"
488 LINE 1: ...REATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1);
491 CREATE TABLE range_parted (
493 ) PARTITION BY RANGE (a);
494 -- trying to specify list for range partitioned table
495 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
496 ERROR: invalid bound specification for a range partition
497 LINE 1: ...BLE fail_part PARTITION OF range_parted FOR VALUES IN ('a');
499 -- each of start and end bounds must have same number of values as the
500 -- length of the partition key
501 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z');
502 ERROR: FROM must specify exactly one value per partitioning column
503 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1);
504 ERROR: TO must specify exactly one value per partitioning column
505 -- cannot specify null values in range bounds
506 CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded);
507 ERROR: cannot specify NULL in range bound
508 -- check if compatible with the specified parent
509 -- cannot create as partition of a non-partitioned table
510 CREATE TABLE unparted (
513 CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a');
514 ERROR: "unparted" is not partitioned
516 -- cannot create a permanent rel as partition of a temp rel
517 CREATE TEMP TABLE temp_parted (
519 ) PARTITION BY LIST (a);
520 CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a');
521 ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
522 DROP TABLE temp_parted;
523 -- cannot create a table with oids as partition of table without oids
524 CREATE TABLE no_oids_parted (
526 ) PARTITION BY RANGE (a) WITHOUT OIDS;
527 CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10) WITH OIDS;
528 ERROR: cannot create table with OIDs as partition of table without OIDs
529 DROP TABLE no_oids_parted;
530 -- If the partitioned table has oids, then the partition must have them.
531 -- If the WITHOUT OIDS option is specified for partition, it is overridden.
532 CREATE TABLE oids_parted (
534 ) PARTITION BY RANGE (a) WITH OIDS;
535 CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (10) WITHOUT OIDS;
537 Table "public.part_forced_oids"
538 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
539 --------+---------+-----------+----------+---------+---------+--------------+-------------
540 a | integer | | not null | | plain | |
541 Partition of: oids_parted FOR VALUES FROM (1) TO (10)
544 DROP TABLE oids_parted, part_forced_oids;
545 -- check for partition bound overlap and other invalid specifications
546 CREATE TABLE list_parted2 (
548 ) PARTITION BY LIST (a);
549 CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z');
550 CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b');
551 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null);
552 ERROR: partition "fail_part" would overlap partition "part_null_z"
553 CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c');
554 ERROR: partition "fail_part" would overlap partition "part_ab"
555 CREATE TABLE range_parted2 (
557 ) PARTITION BY RANGE (a);
558 -- trying to create range partition with empty range
559 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0);
560 ERROR: cannot create range partition with empty range
561 -- note that the range '[1, 1)' has no elements
562 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1);
563 ERROR: cannot create range partition with empty range
564 CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (1);
565 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (2);
566 ERROR: partition "fail_part" would overlap partition "part0"
567 CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10);
568 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (unbounded);
569 ERROR: partition "fail_part" would overlap partition "part1"
570 CREATE TABLE part2 PARTITION OF range_parted2 FOR VALUES FROM (20) TO (30);
571 CREATE TABLE part3 PARTITION OF range_parted2 FOR VALUES FROM (30) TO (40);
572 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (30);
573 ERROR: partition "fail_part" would overlap partition "part2"
574 CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (10) TO (50);
575 ERROR: partition "fail_part" would overlap partition "part3"
576 -- now check for multi-column range partition key
577 CREATE TABLE range_parted3 (
580 ) PARTITION BY RANGE (a, (b+1));
581 CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, unbounded);
582 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, 1);
583 ERROR: partition "fail_part" would overlap partition "part00"
584 CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, 1);
585 CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10);
586 CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, unbounded);
587 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20);
588 ERROR: partition "fail_part" would overlap partition "part12"
589 -- cannot create a partition that says column b is allowed to range
590 -- from -infinity to +infinity, while there exist partitions that have
591 -- more specific ranges
592 CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded);
593 ERROR: partition "fail_part" would overlap partition "part10"
594 -- check schema propagation from parent
595 CREATE TABLE parted (
597 b int NOT NULL DEFAULT 0,
598 CONSTRAINT check_a CHECK (length(a) > 0)
599 ) PARTITION BY LIST (a);
600 CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a');
601 -- only inherited attributes (never local ones)
602 SELECT attname, attislocal, attinhcount FROM pg_attribute
603 WHERE attrelid = 'part_a'::regclass and attnum > 0
605 attname | attislocal | attinhcount
606 ---------+------------+-------------
611 -- able to specify column default, column constraint, and table constraint
612 CREATE TABLE part_b PARTITION OF parted (
613 b NOT NULL DEFAULT 1 CHECK (b >= 0),
614 CONSTRAINT check_a CHECK (length(a) > 0)
615 ) FOR VALUES IN ('b');
616 NOTICE: merging constraint "check_a" with inherited definition
617 -- conislocal should be false for any merged constraints
618 SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass AND conname = 'check_a';
619 conislocal | coninhcount
620 ------------+-------------
624 -- specify PARTITION BY for a partition
625 CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
626 ERROR: column "c" named in partition key does not exist
627 CREATE TABLE part_c PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE ((b));
628 -- create a level-2 partition
629 CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
630 -- Partition bound in describe output
632 Table "public.part_b"
633 Column | Type | Collation | Nullable | Default
634 --------+---------+-----------+----------+---------
636 b | integer | | not null | 1
637 Partition of: parted FOR VALUES IN ('b')
639 "check_a" CHECK (length(a) > 0)
640 "part_b_b_check" CHECK (b >= 0)
642 -- Both partition bound and partition key in describe output
644 Table "public.part_c"
645 Column | Type | Collation | Nullable | Default
646 --------+---------+-----------+----------+---------
648 b | integer | | not null | 0
649 Partition of: parted FOR VALUES IN ('c')
650 Partition key: RANGE (b)
652 "check_a" CHECK (length(a) > 0)
653 Number of partitions: 1 (Use \d+ to list them.)
655 -- Show partition count in the parent's describe output
656 -- Tempted to include \d+ output listing partitions with bound info but
657 -- output could vary depending on the order in which partition oids are
660 Table "public.parted"
661 Column | Type | Collation | Nullable | Default
662 --------+---------+-----------+----------+---------
664 b | integer | | not null | 0
665 Partition key: LIST (a)
667 "check_a" CHECK (length(a) > 0)
668 Number of partitions: 3 (Use \d+ to list them.)
671 DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
672 -- comments on partitioned tables columns
673 CREATE TABLE parted_col_comment (a int, b text) PARTITION BY LIST (a);
674 COMMENT ON TABLE parted_col_comment IS 'Am partitioned table';
675 COMMENT ON COLUMN parted_col_comment.a IS 'Partition key';
676 SELECT obj_description('parted_col_comment'::regclass);
678 ----------------------
682 \d+ parted_col_comment
683 Table "public.parted_col_comment"
684 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
685 --------+---------+-----------+----------+---------+----------+--------------+---------------
686 a | integer | | | | plain | | Partition key
687 b | text | | | | extended | |
688 Partition key: LIST (a)
690 DROP TABLE parted_col_comment;