create_table_like.out 21.2 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
/* Test inheritance of structure (LIKE) */
CREATE TABLE inhx (xx text DEFAULT 'text');
/*
 * Test double inheritance
 *
 * Ensure that defaults are NOT included unless
 * INCLUDING DEFAULTS is specified
 */
CREATE TABLE ctla (aa TEXT);
CREATE TABLE ctlb (bb TEXT) INHERITS (ctla);
11 12 13 14
CREATE TABLE foo (LIKE nonexistent);
ERROR:  relation "nonexistent" does not exist
LINE 1: CREATE TABLE foo (LIKE nonexistent);
                               ^
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
CREATE TABLE inhe (ee text, LIKE inhx) inherits (ctlb);
INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4');
SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */
   aa    |   bb    | ee |   xx    
---------+---------+----+---------
 ee-col1 | ee-col2 |    | ee-col4
(1 row)

SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */
 xx 
----
(0 rows)

SELECT * FROM ctlb; /* Has ee entry */
   aa    |   bb    
---------+---------
 ee-col1 | ee-col2
(1 row)

SELECT * FROM ctla; /* Has ee entry */
   aa    
---------
 ee-col1
(1 row)

CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */
ERROR:  column "xx" specified more than once
CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
INSERT INTO inhf DEFAULT VALUES;
SELECT * FROM inhf; /* Single entry with value 'text' */
  xx  
------
 text
(1 row)

ALTER TABLE inhx add constraint foo CHECK (xx = 'text');
ALTER TABLE inhx ADD PRIMARY KEY (xx);
CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */
INSERT INTO inhg VALUES ('foo');
DROP TABLE inhg;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
ERROR:  new row for relation "inhg" violates check constraint "foo"
DETAIL:  Failing row contains (x, foo, y).
SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
 x |  xx  | y 
---+------+---
 x | text | y
 x | text | y
(2 rows)

DROP TABLE inhg;
69
CREATE TABLE test_like_id_1 (a bigint GENERATED ALWAYS AS IDENTITY, b text);
Peter Eisentraut's avatar
Peter Eisentraut committed
70 71
\d test_like_id_1
                     Table "public.test_like_id_1"
72 73 74 75
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 a      | bigint |           | not null | generated always as identity
 b      | text   |           |          | 
Peter Eisentraut's avatar
Peter Eisentraut committed
76 77 78 79 80 81 82 83 84 85

INSERT INTO test_like_id_1 (b) VALUES ('b1');
SELECT * FROM test_like_id_1;
 a | b  
---+----
 1 | b1
(1 row)

CREATE TABLE test_like_id_2 (LIKE test_like_id_1);
\d test_like_id_2
86 87 88 89 90
          Table "public.test_like_id_2"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 a      | bigint |           | not null | 
 b      | text   |           |          | 
Peter Eisentraut's avatar
Peter Eisentraut committed
91 92

INSERT INTO test_like_id_2 (b) VALUES ('b2');
93
ERROR:  null value in column "a" of relation "test_like_id_2" violates not-null constraint
Peter Eisentraut's avatar
Peter Eisentraut committed
94 95 96 97 98 99 100 101 102
DETAIL:  Failing row contains (null, b2).
SELECT * FROM test_like_id_2;  -- identity was not copied
 a | b 
---+---
(0 rows)

CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
\d test_like_id_3
                     Table "public.test_like_id_3"
103 104 105 106
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 a      | bigint |           | not null | generated always as identity
 b      | text   |           |          | 
Peter Eisentraut's avatar
Peter Eisentraut committed
107 108 109 110 111 112 113 114 115

INSERT INTO test_like_id_3 (b) VALUES ('b3');
SELECT * FROM test_like_id_3;  -- identity was copied and applied
 a | b  
---+----
 1 | b3
(1 row)

DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
Peter Eisentraut's avatar
Peter Eisentraut committed
116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161
CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
\d test_like_gen_1
                        Table "public.test_like_gen_1"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 2) stored

INSERT INTO test_like_gen_1 (a) VALUES (1);
SELECT * FROM test_like_gen_1;
 a | b 
---+---
 1 | 2
(1 row)

CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
\d test_like_gen_2
          Table "public.test_like_gen_2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

INSERT INTO test_like_gen_2 (a) VALUES (1);
SELECT * FROM test_like_gen_2;
 a | b 
---+---
 1 |  
(1 row)

CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
\d test_like_gen_3
                        Table "public.test_like_gen_3"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 2) stored

INSERT INTO test_like_gen_3 (a) VALUES (1);
SELECT * FROM test_like_gen_3;
 a | b 
---+---
 1 | 2
(1 row)

DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
162
-- also test generated column with a "forward" reference (bug #16342)
163 164 165
CREATE TABLE test_like_4 (b int DEFAULT 42,
  c int GENERATED ALWAYS AS (a * 2) STORED,
  a int CHECK (a > 0));
166 167 168 169 170 171
\d test_like_4
                          Table "public.test_like_4"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 b      | integer |           |          | 42
 c      | integer |           |          | generated always as (a * 2) stored
172
 a      | integer |           |          | 
173 174
Check constraints:
    "test_like_4_a_check" CHECK (a > 0)
175 176 177 178 179 180 181 182 183 184 185

CREATE TABLE test_like_4a (LIKE test_like_4);
CREATE TABLE test_like_4b (LIKE test_like_4 INCLUDING DEFAULTS);
CREATE TABLE test_like_4c (LIKE test_like_4 INCLUDING GENERATED);
CREATE TABLE test_like_4d (LIKE test_like_4 INCLUDING DEFAULTS INCLUDING GENERATED);
\d test_like_4a
            Table "public.test_like_4a"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 b      | integer |           |          | 
 c      | integer |           |          | 
186
 a      | integer |           |          | 
187

188 189
INSERT INTO test_like_4a (a) VALUES(11);
SELECT a, b, c FROM test_like_4a;
190 191 192 193 194 195 196 197 198 199 200
 a  | b | c 
----+---+---
 11 |   |  
(1 row)

\d test_like_4b
            Table "public.test_like_4b"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 b      | integer |           |          | 42
 c      | integer |           |          | 
201
 a      | integer |           |          | 
202

203 204
INSERT INTO test_like_4b (a) VALUES(11);
SELECT a, b, c FROM test_like_4b;
205 206 207 208 209 210 211 212 213 214 215
 a  | b  | c 
----+----+---
 11 | 42 |  
(1 row)

\d test_like_4c
                         Table "public.test_like_4c"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 b      | integer |           |          | 
 c      | integer |           |          | generated always as (a * 2) stored
216
 a      | integer |           |          | 
217

218 219
INSERT INTO test_like_4c (a) VALUES(11);
SELECT a, b, c FROM test_like_4c;
220 221 222 223 224 225 226 227 228 229 230
 a  | b | c  
----+---+----
 11 |   | 22
(1 row)

\d test_like_4d
                         Table "public.test_like_4d"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 b      | integer |           |          | 42
 c      | integer |           |          | generated always as (a * 2) stored
231
 a      | integer |           |          | 
232

233 234
INSERT INTO test_like_4d (a) VALUES(11);
SELECT a, b, c FROM test_like_4d;
235 236 237 238 239
 a  | b  | c  
----+----+----
 11 | 42 | 22
(1 row)

240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
-- Test renumbering of Vars when combining LIKE with inheritance
CREATE TABLE test_like_5 (x point, y point, z point);
CREATE TABLE test_like_5x (p int CHECK (p > 0),
   q int GENERATED ALWAYS AS (p * 2) STORED);
CREATE TABLE test_like_5c (LIKE test_like_4 INCLUDING ALL)
  INHERITS (test_like_5, test_like_5x);
\d test_like_5c
                         Table "public.test_like_5c"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 x      | point   |           |          | 
 y      | point   |           |          | 
 z      | point   |           |          | 
 p      | integer |           |          | 
 q      | integer |           |          | generated always as (p * 2) stored
 b      | integer |           |          | 42
 c      | integer |           |          | generated always as (a * 2) stored
 a      | integer |           |          | 
Check constraints:
    "test_like_4_a_check" CHECK (a > 0)
    "test_like_5x_p_check" CHECK (p > 0)
Inherits: test_like_5,
          test_like_5x

264
DROP TABLE test_like_4, test_like_4a, test_like_4b, test_like_4c, test_like_4d;
265
DROP TABLE test_like_5, test_like_5x, test_like_5c;
266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
ERROR:  duplicate key value violates unique constraint "inhg_pkey"
DETAIL:  Key (xx)=(10) already exists.
DROP TABLE inhg;
/* Multiple primary keys creation should fail */
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */
ERROR:  multiple primary keys for table "inhg" are not allowed
CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE);
CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test';
/* Ok to create multiple unique indexes */
CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES);
INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10);
INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15);
INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
ERROR:  duplicate key value violates unique constraint "inhg_x_key"
DETAIL:  Key (x)=(15) already exists.
DROP TABLE inhg;
285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300
DROP TABLE inhz;
/* Use primary key imported by LIKE for self-referential FK constraint */
CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
\d inhz
              Table "public.inhz"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 x      | text |           |          | 
 xx     | text |           | not null | 
Indexes:
    "inhz_pkey" PRIMARY KEY, btree (xx)
Foreign-key constraints:
    "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
Referenced by:
    TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)

301 302 303 304 305
DROP TABLE inhz;
-- including storage and comments
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);
CREATE INDEX ctlt1_b_key ON ctlt1 (b);
CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b));
306
CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1;
307
CREATE STATISTICS ctlt1_expr_stat ON (a || b) FROM ctlt1;
308
COMMENT ON STATISTICS ctlt1_a_b_stat IS 'ab stats';
309
COMMENT ON STATISTICS ctlt1_expr_stat IS 'ab expr stats';
310 311 312 313 314 315 316 317 318
COMMENT ON COLUMN ctlt1.a IS 'A';
COMMENT ON COLUMN ctlt1.b IS 'B';
COMMENT ON CONSTRAINT ctlt1_a_check ON ctlt1 IS 't1_a_check';
COMMENT ON INDEX ctlt1_pkey IS 'index pkey';
COMMENT ON INDEX ctlt1_b_key IS 'index b_key';
ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN;
CREATE TABLE ctlt2 (c text);
ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL;
COMMENT ON COLUMN ctlt2.c IS 'C';
319
CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7));
320 321
ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL;
ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN;
322
CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c));
323 324 325 326 327 328 329
COMMENT ON COLUMN ctlt3.a IS 'A3';
COMMENT ON COLUMN ctlt3.c IS 'C';
COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check';
CREATE TABLE ctlt4 (a text, c text);
ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL;
CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE);
\d+ ctlt12_storage
330 331 332 333 334 335
                             Table "public.ctlt12_storage"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | 
 b      | text |           |          |         | extended |              | 
 c      | text |           |          |         | external |              | 
336 337 338

CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS);
\d+ ctlt12_comments
339 340 341 342 343 344
                             Table "public.ctlt12_comments"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | extended |              | A
 b      | text |           |          |         | extended |              | B
 c      | text |           |          |         | extended |              | C
345 346 347 348 349 350

CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
NOTICE:  merging column "a" with inherited definition
NOTICE:  merging column "b" with inherited definition
NOTICE:  merging constraint "ctlt1_a_check" with inherited definition
\d+ ctlt1_inh
351 352 353 354 355
                                Table "public.ctlt1_inh"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A
 b      | text |           |          |         | extended |              | B
356 357 358 359 360 361 362 363 364 365 366 367 368
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
Inherits: ctlt1

SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass;
 description 
-------------
 t1_a_check
(1 row)

CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
NOTICE:  merging multiple inherited definitions of column "a"
\d+ ctlt13_inh
369 370 371 372 373 374
                               Table "public.ctlt13_inh"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | 
 b      | text |           |          |         | extended |              | 
 c      | text |           |          |         | external |              | 
375 376 377
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
    "ctlt3_a_check" CHECK (length(a) < 5)
378
    "ctlt3_c_check" CHECK (length(c) < 7)
379 380 381
Inherits: ctlt1,
          ctlt3

382
CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
383 384
NOTICE:  merging column "a" with inherited definition
\d+ ctlt13_like
385 386 387 388 389 390
                               Table "public.ctlt13_like"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A3
 b      | text |           |          |         | extended |              | 
 c      | text |           |          |         | external |              | C
391 392
Indexes:
    "ctlt13_like_expr_idx" btree ((a || c))
393 394 395
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
    "ctlt3_a_check" CHECK (length(a) < 5)
396
    "ctlt3_c_check" CHECK (length(c) < 7)
397 398 399 400 401 402 403 404 405 406
Inherits: ctlt1

SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass;
 description 
-------------
 t3_a_check
(1 row)

CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
\d+ ctlt_all
407 408 409 410 411
                                Table "public.ctlt_all"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A
 b      | text |           |          |         | extended |              | B
412 413 414 415 416 417
Indexes:
    "ctlt_all_pkey" PRIMARY KEY, btree (a)
    "ctlt_all_b_idx" btree (b)
    "ctlt_all_expr_idx" btree ((a || b))
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
418
Statistics objects:
419
    "public.ctlt_all_a_b_stat" ON a, b FROM ctlt_all
420
    "public.ctlt_all_expr_stat" ON (a || b) FROM ctlt_all
421 422 423 424 425 426 427 428

SELECT c.relname, objsubid, description FROM pg_description, pg_index i, pg_class c WHERE classoid = 'pg_class'::regclass AND objoid = i.indexrelid AND c.oid = i.indexrelid AND i.indrelid = 'ctlt_all'::regclass ORDER BY c.relname, objsubid;
    relname     | objsubid | description 
----------------+----------+-------------
 ctlt_all_b_idx |        0 | index b_key
 ctlt_all_pkey  |        0 | index pkey
(2 rows)

429
SELECT s.stxname, objsubid, description FROM pg_description, pg_statistic_ext s WHERE classoid = 'pg_statistic_ext'::regclass AND objoid = s.oid AND s.stxrelid = 'ctlt_all'::regclass ORDER BY s.stxname, objsubid;
430 431 432 433 434
      stxname       | objsubid |  description  
--------------------+----------+---------------
 ctlt_all_a_b_stat  |        0 | ab stats
 ctlt_all_expr_stat |        0 | ab expr stats
(2 rows)
435

436 437 438 439 440 441 442 443
CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4);
NOTICE:  merging multiple inherited definitions of column "a"
ERROR:  inherited column "a" has a storage parameter conflict
DETAIL:  MAIN versus EXTENDED
CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE:  merging column "a" with inherited definition
ERROR:  column "a" has a storage parameter conflict
DETAIL:  MAIN versus EXTENDED
444 445 446 447 448 449 450 451 452 453 454 455 456 457 458
-- Check that LIKE isn't confused by a system catalog of the same name
CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL);
\d+ public.pg_attrdef
                               Table "public.pg_attrdef"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A
 b      | text |           |          |         | extended |              | B
Indexes:
    "pg_attrdef_pkey" PRIMARY KEY, btree (a)
    "pg_attrdef_b_idx" btree (b)
    "pg_attrdef_expr_idx" btree ((a || b))
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
Statistics objects:
459
    "public.pg_attrdef_a_b_stat" ON a, b FROM public.pg_attrdef
460
    "public.pg_attrdef_expr_stat" ON (a || b) FROM public.pg_attrdef
461 462

DROP TABLE public.pg_attrdef;
463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480
-- Check that LIKE isn't confused when new table masks the old, either
BEGIN;
CREATE SCHEMA ctl_schema;
SET LOCAL search_path = ctl_schema, public;
CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL);
\d+ ctlt1
                                Table "ctl_schema.ctlt1"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A
 b      | text |           |          |         | extended |              | B
Indexes:
    "ctlt1_pkey" PRIMARY KEY, btree (a)
    "ctlt1_b_idx" btree (b)
    "ctlt1_expr_idx" btree ((a || b))
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
Statistics objects:
481
    "ctl_schema.ctlt1_a_b_stat" ON a, b FROM ctlt1
482
    "ctl_schema.ctlt1_expr_stat" ON (a || b) FROM ctlt1
483 484

ROLLBACK;
485 486
DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE;
NOTICE:  drop cascades to table inhe
487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502
-- LIKE must respect NO INHERIT property of constraints
CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT);
CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS);
\d noinh_con_copy1
          Table "public.noinh_con_copy1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Check constraints:
    "noinh_con_copy_a_check" CHECK (a > 0) NO INHERIT

-- fail, as partitioned tables don't allow NO INHERIT constraints
CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
  PARTITION BY LIST (a);
ERROR:  cannot add NO INHERIT constraint to partitioned table "noinh_con_copy1_parted"
DROP TABLE noinh_con_copy, noinh_con_copy1;
503 504 505 506
/* LIKE with other relation kinds */
CREATE TABLE ctlt4 (a int, b text);
CREATE SEQUENCE ctlseq1;
CREATE TABLE ctlt10 (LIKE ctlseq1);  -- fail
507
ERROR:  "ctlseq1" is not a table, view, materialized view, composite type, or foreign table
508 509
LINE 1: CREATE TABLE ctlt10 (LIKE ctlseq1);
                                  ^
510 511 512 513
CREATE VIEW ctlv1 AS SELECT * FROM ctlt4;
CREATE TABLE ctlt11 (LIKE ctlv1);
CREATE TABLE ctlt11a (LIKE ctlv1 INCLUDING ALL);
CREATE TYPE ctlty1 AS (a int, b text);
514
CREATE TABLE ctlt12 (LIKE ctlty1);
515 516 517 518 519
DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE:  table "ctlt10" does not exist, skipping