path: root/src/test/regress/expected/create_table_like.out

/* 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);
CREATE TABLE foo (LIKE nonexistent);
ERROR:  relation "nonexistent" does not exist
LINE 1: CREATE TABLE foo (LIKE nonexistent);
                               ^
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;
CREATE TABLE test_like_id_1 (a bigint GENERATED ALWAYS AS IDENTITY, b text);
\d test_like_id_1
                     Table "public.test_like_id_1"
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 a      | bigint |           | not null | generated always as identity
 b      | text   |           |          | 

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
          Table "public.test_like_id_2"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 a      | bigint |           | not null | 
 b      | text   |           |          | 

INSERT INTO test_like_id_2 (b) VALUES ('b2');
ERROR:  null value in column "a" violates not-null constraint
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"
 Column |  Type  | Collation | Nullable |           Default            
--------+--------+-----------+----------+------------------------------
 a      | bigint |           | not null | generated always as identity
 b      | text   |           |          | 

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;
-- Test renumbering of Vars when combining LIKE with inheritance
CREATE TABLE test_like_4 (b int DEFAULT 42,
  c int NOT NULL,
  a int CHECK (a > 0));
CREATE TABLE test_like_5 (x point, y point, z point);
CREATE TABLE test_like_5x (p int CHECK (p > 0),
   q int DEFAULT 99);
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 |           |          | 99
 b      | integer |           |          | 42
 c      | integer |           | not null | 
 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

DROP TABLE test_like_4;
DROP TABLE test_like_5, test_like_5x, test_like_5c;
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;
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)

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));
CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1;
COMMENT ON STATISTICS ctlt1_a_b_stat IS 'ab stats';
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';
CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7));
ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL;
ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN;
CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c));
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
                             Table "public.ctlt12_storage"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | 
 b      | text |           |          |         | extended |              | 
 c      | text |           |          |         | external |              | 

CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS);
\d+ ctlt12_comments
                             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

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
                                Table "public.ctlt1_inh"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A
 b      | text |           |          |         | extended |              | B
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
                               Table "public.ctlt13_inh"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | 
 b      | text |           |          |         | extended |              | 
 c      | text |           |          |         | external |              | 
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
    "ctlt3_a_check" CHECK (length(a) < 5)
    "ctlt3_c_check" CHECK (length(c) < 7)
Inherits: ctlt1,
          ctlt3

CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE:  merging column "a" with inherited definition
\d+ ctlt13_like
                               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
Indexes:
    "ctlt13_like_expr_idx" btree ((a || c))
Check constraints:
    "ctlt1_a_check" CHECK (length(a) > 2)
    "ctlt3_a_check" CHECK (length(a) < 5)
    "ctlt3_c_check" CHECK (length(c) < 7)
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
                                Table "public.ctlt_all"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------+-----------+----------+---------+----------+--------------+-------------
 a      | text |           | not null |         | main     |              | A
 b      | text |           |          |         | extended |              | B
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)
Statistics objects:
    "public"."ctlt_all_a_b_stat" (ndistinct, dependencies) ON a, b FROM ctlt_all

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)

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;
      stxname      | objsubid | description 
-------------------+----------+-------------
 ctlt_all_a_b_stat |        0 | ab stats
(1 row)

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
-- 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:
    "public"."pg_attrdef_a_b_stat" (ndistinct, dependencies) ON a, b FROM public.pg_attrdef

DROP TABLE public.pg_attrdef;
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
/* LIKE with other relation kinds */
CREATE TABLE ctlt4 (a int, b text);
CREATE SEQUENCE ctlseq1;
CREATE TABLE ctlt10 (LIKE ctlseq1);  -- fail
ERROR:  "ctlseq1" is not a table, view, materialized view, composite type, or foreign table
LINE 1: CREATE TABLE ctlt10 (LIKE ctlseq1);
                                  ^
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);
CREATE TABLE ctlt12 (LIKE ctlty1);
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
/* LIKE WITH OIDS */
CREATE TABLE has_oid (x INTEGER) WITH OIDS;
CREATE TABLE no_oid (y INTEGER);
CREATE TABLE like_test (z INTEGER, LIKE has_oid);
SELECT oid FROM like_test;
 oid 
-----
(0 rows)

CREATE TABLE like_test2 (z INTEGER, LIKE no_oid);
SELECT oid FROM like_test2; -- fail
ERROR:  column "oid" does not exist
LINE 1: SELECT oid FROM like_test2;
               ^
CREATE TABLE like_test3 (z INTEGER, LIKE has_oid, LIKE no_oid);
SELECT oid FROM like_test3;
 oid 
-----
(0 rows)

CREATE TABLE like_test4 (z INTEGER, PRIMARY KEY(oid), LIKE has_oid);
SELECT oid FROM like_test4;
 oid 
-----
(0 rows)

CREATE TABLE like_test5 (z INTEGER, LIKE no_oid) WITH OIDS;
SELECT oid FROM like_test5;
 oid 
-----
(0 rows)

DROP TABLE has_oid, no_oid, like_test, like_test2, like_test3,
  like_test4, like_test5;