diff --git i/doc/src/sgml/ref/create_table.sgml w/doc/src/sgml/ref/create_table.sgml index 30e4154..97968bb 100644 --- i/doc/src/sgml/ref/create_table.sgml +++ w/doc/src/sgml/ref/create_table.sgml @@ -24,7 +24,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint - | LIKE parent_table [ like_option ... ] } + | LIKE source_table [ like_option ... ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] @@ -312,7 +312,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI - LIKE parent_table [ like_option ... ] + LIKE source_table [ like_option ... ] The LIKE clause specifies a table from which diff --git i/src/backend/nodes/copyfuncs.c w/src/backend/nodes/copyfuncs.c index dd2dd25..a4d3912 100644 --- i/src/backend/nodes/copyfuncs.c +++ w/src/backend/nodes/copyfuncs.c @@ -2726,10 +2726,10 @@ _copyCreateStmt(const CreateStmt *from) return newnode; } -static InhRelation * -_copyInhRelation(const InhRelation *from) +static TableLikeClause * +_copyTableLikeClause(const TableLikeClause *from) { - InhRelation *newnode = makeNode(InhRelation); + TableLikeClause *newnode = makeNode(TableLikeClause); COPY_NODE_FIELD(relation); COPY_SCALAR_FIELD(options); @@ -4133,8 +4133,8 @@ copyObject(const void *from) case T_CreateStmt: retval = _copyCreateStmt(from); break; - case T_InhRelation: - retval = _copyInhRelation(from); + case T_TableLikeClause: + retval = _copyTableLikeClause(from); break; case T_DefineStmt: retval = _copyDefineStmt(from); diff --git i/src/backend/nodes/equalfuncs.c w/src/backend/nodes/equalfuncs.c index c2fdb2b..c1bf94f 100644 --- i/src/backend/nodes/equalfuncs.c +++ w/src/backend/nodes/equalfuncs.c @@ -1159,7 +1159,7 @@ _equalCreateStmt(const CreateStmt *a, const CreateStmt *b) } static bool -_equalInhRelation(const InhRelation *a, const InhRelation *b) +_equalTableLikeClause(const TableLikeClause *a, const TableLikeClause *b) { COMPARE_NODE_FIELD(relation); COMPARE_SCALAR_FIELD(options); @@ -2676,8 +2676,8 @@ equal(const void *a, const void *b) case T_CreateStmt: retval = _equalCreateStmt(a, b); break; - case T_InhRelation: - retval = _equalInhRelation(a, b); + case T_TableLikeClause: + retval = _equalTableLikeClause(a, b); break; case T_DefineStmt: retval = _equalDefineStmt(a, b); diff --git i/src/backend/nodes/outfuncs.c w/src/backend/nodes/outfuncs.c index cdc2cab..9d14141 100644 --- i/src/backend/nodes/outfuncs.c +++ w/src/backend/nodes/outfuncs.c @@ -2064,9 +2064,9 @@ _outDefElem(StringInfo str, const DefElem *node) } static void -_outInhRelation(StringInfo str, const InhRelation *node) +_outTableLikeClause(StringInfo str, const TableLikeClause *node) { - WRITE_NODE_TYPE("INHRELATION"); + WRITE_NODE_TYPE("TABLELIKECLAUSE"); WRITE_NODE_FIELD(relation); WRITE_UINT_FIELD(options); @@ -3140,8 +3140,8 @@ _outNode(StringInfo str, const void *obj) case T_DefElem: _outDefElem(str, obj); break; - case T_InhRelation: - _outInhRelation(str, obj); + case T_TableLikeClause: + _outTableLikeClause(str, obj); break; case T_LockingClause: _outLockingClause(str, obj); diff --git i/src/backend/parser/gram.y w/src/backend/parser/gram.y index 8943c5b..671a34f 100644 --- i/src/backend/parser/gram.y +++ w/src/backend/parser/gram.y @@ -2718,18 +2718,10 @@ ConstraintAttr: ; -/* - * SQL99 supports wholesale borrowing of a table definition via the LIKE clause. - * This seems to be a poor man's inheritance capability, with the resulting - * tables completely decoupled except for the original commonality in definitions. - * - * This is very similar to CREATE TABLE AS except for the INCLUDING DEFAULTS extension - * which is a part of SQL:2003. - */ TableLikeClause: LIKE qualified_name TableLikeOptionList { - InhRelation *n = makeNode(InhRelation); + TableLikeClause *n = makeNode(TableLikeClause); n->relation = $2; n->options = $3; $$ = (Node *)n; diff --git i/src/backend/parser/parse_utilcmd.c w/src/backend/parser/parse_utilcmd.c index a2a54db..0ac2373 100644 --- i/src/backend/parser/parse_utilcmd.c +++ w/src/backend/parser/parse_utilcmd.c @@ -102,8 +102,8 @@ static void transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column); static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint); -static void transformInhRelation(CreateStmtContext *cxt, - InhRelation *inhrelation); +static void transformTableLikeClause(CreateStmtContext *cxt, + TableLikeClause *table_like_clause); static void transformOfType(CreateStmtContext *cxt, TypeName *ofTypename); static char *chooseIndexName(const RangeVar *relation, IndexStmt *index_stmt); @@ -238,8 +238,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) transformTableConstraint(&cxt, (Constraint *) element); break; - case T_InhRelation: - transformInhRelation(&cxt, (InhRelation *) element); + case T_TableLikeClause: + transformTableLikeClause(&cxt, (TableLikeClause *) element); break; default: @@ -625,14 +625,14 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) } /* - * transformInhRelation + * transformTableLikeClause * - * Change the LIKE portion of a CREATE TABLE statement into + * Change the LIKE portion of a CREATE TABLE statement into * column definitions which recreate the user defined column portions of - * . + * . */ static void -transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) +transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause) { AttrNumber parent_attno; Relation relation; @@ -641,17 +641,19 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) AclResult aclresult; char *comment; - relation = parserOpenTable(cxt->pstate, inhRelation->relation, + relation = parserOpenTable(cxt->pstate, table_like_clause->relation, AccessShareLock); - if (relation->rd_rel->relkind != RELKIND_RELATION) + if (relation->rd_rel->relkind != RELKIND_RELATION + && relation->rd_rel->relkind != RELKIND_VIEW + && relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("inherited relation \"%s\" is not a table", - inhRelation->relation->relname))); + errmsg("LIKE source relation \"%s\" is not a table, view, or foreign table", + table_like_clause->relation->relname))); /* - * Check for SELECT privilages + * Check for SELECT privileges */ aclresult = pg_class_aclcheck(RelationGetRelid(relation), GetUserId(), ACL_SELECT); @@ -708,7 +710,7 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) * Copy default, if present and the default has been requested */ if (attribute->atthasdef && - (inhRelation->options & CREATE_TABLE_LIKE_DEFAULTS)) + (table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS)) { Node *this_default = NULL; AttrDefault *attrdef; @@ -736,13 +738,13 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) } /* Likewise, copy storage if requested */ - if (inhRelation->options & CREATE_TABLE_LIKE_STORAGE) + if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) def->storage = attribute->attstorage; else def->storage = 0; /* Likewise, copy comment if requested */ - if ((inhRelation->options & CREATE_TABLE_LIKE_COMMENTS) && + if ((table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) && (comment = GetComment(attribute->attrelid, RelationRelationId, attribute->attnum)) != NULL) @@ -764,7 +766,7 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) * Copy CHECK constraints if requested, being careful to adjust attribute * numbers */ - if ((inhRelation->options & CREATE_TABLE_LIKE_CONSTRAINTS) && + if ((table_like_clause->options & CREATE_TABLE_LIKE_CONSTRAINTS) && tupleDesc->constr) { AttrNumber *attmap = varattnos_map_schema(tupleDesc, cxt->columns); @@ -787,7 +789,7 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) cxt->ckconstraints = lappend(cxt->ckconstraints, n); /* Copy comment on constraint */ - if ((inhRelation->options & CREATE_TABLE_LIKE_COMMENTS) && + if ((table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) && (comment = GetComment(get_constraint_oid(RelationGetRelid(relation), n->conname, false), ConstraintRelationId, @@ -810,7 +812,7 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) /* * Likewise, copy indexes if requested */ - if ((inhRelation->options & CREATE_TABLE_LIKE_INDEXES) && + if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) && relation->rd_rel->relhasindex) { AttrNumber *attmap = varattnos_map_schema(tupleDesc, cxt->columns); @@ -831,7 +833,7 @@ transformInhRelation(CreateStmtContext *cxt, InhRelation *inhRelation) index_stmt = generateClonedIndexStmt(cxt, parent_index, attmap); /* Copy comment on index */ - if (inhRelation->options & CREATE_TABLE_LIKE_COMMENTS) + if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) { comment = GetComment(parent_index_oid, RelationRelationId, 0); diff --git i/src/include/nodes/nodes.h w/src/include/nodes/nodes.h index f0c6381..7bc2e43 100644 --- i/src/include/nodes/nodes.h +++ w/src/include/nodes/nodes.h @@ -389,7 +389,7 @@ typedef enum NodeTag T_FuncWithArgs, T_AccessPriv, T_CreateOpClassItem, - T_InhRelation, + T_TableLikeClause, T_FunctionParameter, T_LockingClause, T_RowMarkClause, diff --git i/src/include/nodes/parsenodes.h w/src/include/nodes/parsenodes.h index 6e8b110..6d40af7 100644 --- i/src/include/nodes/parsenodes.h +++ w/src/include/nodes/parsenodes.h @@ -504,16 +504,16 @@ typedef struct ColumnDef } ColumnDef; /* - * inhRelation - Relation a CREATE TABLE is to inherit attributes of + * TableLikeClause - CREATE TABLE ( ... LIKE ... ) clause */ -typedef struct InhRelation +typedef struct TableLikeClause { NodeTag type; RangeVar *relation; - bits32 options; /* OR of CreateStmtLikeOption flags */ -} InhRelation; + bits32 options; /* OR of TableLikeOption flags */ +} TableLikeClause; -typedef enum CreateStmtLikeOption +typedef enum TableLikeOption { CREATE_TABLE_LIKE_DEFAULTS = 1 << 0, CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1, @@ -521,7 +521,7 @@ typedef enum CreateStmtLikeOption CREATE_TABLE_LIKE_STORAGE = 1 << 3, CREATE_TABLE_LIKE_COMMENTS = 1 << 4, CREATE_TABLE_LIKE_ALL = 0x7FFFFFFF -} CreateStmtLikeOption; +} TableLikeOption; /* * IndexElem - index parameters (used in CREATE INDEX) diff --git i/src/test/regress/expected/create_table_like.out w/src/test/regress/expected/create_table_like.out index e69de29..8967914 100644 --- i/src/test/regress/expected/create_table_like.out +++ w/src/test/regress/expected/create_table_like.out @@ -0,0 +1,240 @@ +/* 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 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); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "inhx_pkey" for table "inhx" +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 inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "inhg_pkey" for table "inhg" +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); +NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhz_yy_key" for table "inhz" +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); +NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_x_key" for table "inhg" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_yy_key" for table "inhg" +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; +-- including storage and comments +CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt1_pkey" for table "ctlt1" +CREATE INDEX ctlt1_b_key ON ctlt1 (b); +CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); +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); +ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; +ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; +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 | Modifiers | Storage | Stats target | Description +--------+------+-----------+----------+--------------+------------- + a | text | not null | main | | + b | text | | extended | | + c | text | | external | | +Has OIDs: no + +CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); +\d+ ctlt12_comments + Table "public.ctlt12_comments" + Column | Type | Modifiers | Storage | Stats target | Description +--------+------+-----------+----------+--------------+------------- + a | text | not null | extended | | A + b | text | | extended | | B + c | text | | extended | | C +Has OIDs: no + +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 | Modifiers | 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 +Has OIDs: no + +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 | Modifiers | 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) +Inherits: ctlt1, + ctlt3 +Has OIDs: no + +CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); +NOTICE: merging column "a" with inherited definition +\d+ ctlt13_like + Table "public.ctlt13_like" + Column | Type | Modifiers | Storage | Stats target | Description +--------+------+-----------+----------+--------------+------------- + a | text | not null | main | | A3 + b | text | | extended | | + c | text | | external | | C +Check constraints: + "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt3_a_check" CHECK (length(a) < 5) +Inherits: ctlt1 +Has OIDs: no + +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); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ctlt_all_pkey" for table "ctlt_all" +\d+ ctlt_all + Table "public.ctlt_all" + Column | Type | Modifiers | 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) +Has OIDs: no + +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) + +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 +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: LIKE source relation "ctlseq1" is not a table, view, or foreign table +CREATE VIEW ctlv1 AS SELECT * FROM ctlt4; +CREATE TABLE ctlt11 (LIKE ctlv1); +CREATE TYPE ctlty1 AS (a int, b text); +CREATE TABLE ctlt12 (LIKE ctlty1); -- TODO: currently fails +ERROR: "ctlty1" is a composite type +LINE 1: CREATE TABLE ctlt12 (LIKE ctlty1); + ^ +DROP SEQUENCE ctlseq1; +DROP TYPE ctlty1; +DROP VIEW ctlv1; +DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt12; +NOTICE: table "ctlt10" does not exist, skipping +NOTICE: table "ctlt12" does not exist, skipping diff --git i/src/test/regress/expected/inherit.out w/src/test/regress/expected/inherit.out index 309c1db..7e8f572 100644 --- i/src/test/regress/expected/inherit.out +++ w/src/test/regress/expected/inherit.out @@ -587,93 +587,6 @@ CREATE TABLE otherchild (tomorrow date default now()) NOTICE: merging multiple inherited definitions of column "tomorrow" NOTICE: merging column "tomorrow" with inherited definition DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; -/* 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 inhe (ee text, LIKE inhx) inherits (b); -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 b; /* Has ee entry */ - aa | bb ----------+--------- - ee-col1 | ee-col2 -(1 row) - -SELECT * FROM a; /* 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); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "inhx_pkey" for table "inhx" -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 inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "inhg_pkey" for table "inhg" -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); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhz_yy_key" for table "inhz" -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); -NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_x_key" for table "inhg" -NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_yy_key" for table "inhg" -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; -- Test changing the type of inherited columns insert into d values('test','one','two','three'); alter table a alter column aa type integer using bit_length(aa); @@ -963,171 +876,39 @@ drop table pp1 cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table cc1 drop cascades to table cc2 --- including storage and comments -CREATE TABLE t1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" -CREATE INDEX t1_b_key ON t1 (b); -CREATE INDEX t1_fnidx ON t1 ((a || b)); -COMMENT ON COLUMN t1.a IS 'A'; -COMMENT ON COLUMN t1.b IS 'B'; -COMMENT ON CONSTRAINT t1_a_check ON t1 IS 't1_a_check'; -COMMENT ON INDEX t1_pkey IS 'index pkey'; -COMMENT ON INDEX t1_b_key IS 'index b_key'; -ALTER TABLE t1 ALTER COLUMN a SET STORAGE MAIN; -CREATE TABLE t2 (c text); -ALTER TABLE t2 ALTER COLUMN c SET STORAGE EXTERNAL; -COMMENT ON COLUMN t2.c IS 'C'; -CREATE TABLE t3 (a text CHECK (length(a) < 5), c text); -ALTER TABLE t3 ALTER COLUMN c SET STORAGE EXTERNAL; -ALTER TABLE t3 ALTER COLUMN a SET STORAGE MAIN; -COMMENT ON COLUMN t3.a IS 'A3'; -COMMENT ON COLUMN t3.c IS 'C'; -COMMENT ON CONSTRAINT t3_a_check ON t3 IS 't3_a_check'; -CREATE TABLE t4 (a text, c text); -ALTER TABLE t4 ALTER COLUMN c SET STORAGE EXTERNAL; -CREATE TABLE t12_storage (LIKE t1 INCLUDING STORAGE, LIKE t2 INCLUDING STORAGE); -\d+ t12_storage - Table "public.t12_storage" - Column | Type | Modifiers | Storage | Stats target | Description ---------+------+-----------+----------+--------------+------------- - a | text | not null | main | | - b | text | | extended | | - c | text | | external | | -Has OIDs: no - -CREATE TABLE t12_comments (LIKE t1 INCLUDING COMMENTS, LIKE t2 INCLUDING COMMENTS); -\d+ t12_comments - Table "public.t12_comments" - Column | Type | Modifiers | Storage | Stats target | Description ---------+------+-----------+----------+--------------+------------- - a | text | not null | extended | | A - b | text | | extended | | B - c | text | | extended | | C -Has OIDs: no - -CREATE TABLE t1_inh (LIKE t1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (t1); -NOTICE: merging column "a" with inherited definition -NOTICE: merging column "b" with inherited definition -NOTICE: merging constraint "t1_a_check" with inherited definition -\d+ t1_inh - Table "public.t1_inh" - Column | Type | Modifiers | Storage | Stats target | Description ---------+------+-----------+----------+--------------+------------- - a | text | not null | main | | A - b | text | | extended | | B -Check constraints: - "t1_a_check" CHECK (length(a) > 2) -Inherits: t1 -Has OIDs: no - -SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 't1_inh'::regclass; - description -------------- - t1_a_check -(1 row) - -CREATE TABLE t13_inh () INHERITS (t1, t3); -NOTICE: merging multiple inherited definitions of column "a" -\d+ t13_inh - Table "public.t13_inh" - Column | Type | Modifiers | Storage | Stats target | Description ---------+------+-----------+----------+--------------+------------- - a | text | not null | main | | - b | text | | extended | | - c | text | | external | | -Check constraints: - "t1_a_check" CHECK (length(a) > 2) - "t3_a_check" CHECK (length(a) < 5) -Inherits: t1, - t3 -Has OIDs: no - -CREATE TABLE t13_like (LIKE t3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (t1); -NOTICE: merging column "a" with inherited definition -\d+ t13_like - Table "public.t13_like" - Column | Type | Modifiers | Storage | Stats target | Description ---------+------+-----------+----------+--------------+------------- - a | text | not null | main | | A3 - b | text | | extended | | - c | text | | external | | C -Check constraints: - "t1_a_check" CHECK (length(a) > 2) - "t3_a_check" CHECK (length(a) < 5) -Inherits: t1 -Has OIDs: no - -SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 't13_like'::regclass; - description -------------- - t3_a_check -(1 row) - -CREATE TABLE t_all (LIKE t1 INCLUDING ALL); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_all_pkey" for table "t_all" -\d+ t_all - Table "public.t_all" - Column | Type | Modifiers | Storage | Stats target | Description ---------+------+-----------+----------+--------------+------------- - a | text | not null | main | | A - b | text | | extended | | B -Indexes: - "t_all_pkey" PRIMARY KEY, btree (a) - "t_all_b_idx" btree (b) - "t_all_expr_idx" btree ((a || b)) -Check constraints: - "t1_a_check" CHECK (length(a) > 2) -Has OIDs: no - -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 = 't_all'::regclass ORDER BY c.relname, objsubid; - relname | objsubid | description --------------+----------+------------- - t_all_b_idx | 0 | index b_key - t_all_pkey | 0 | index pkey -(2 rows) - -CREATE TABLE inh_error1 () INHERITS (t1, t4); -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 t4 INCLUDING STORAGE) INHERITS (t1); -NOTICE: merging column "a" with inherited definition -ERROR: column "a" has a storage parameter conflict -DETAIL: MAIN versus EXTENDED -DROP TABLE t1, t2, t3, t4, t12_storage, t12_comments, t1_inh, t13_inh, t13_like, t_all; -- Test for renaming in simple multiple inheritance -CREATE TABLE t1 (a int, b int); -CREATE TABLE s1 (b int, c int); -CREATE TABLE ts (d int) INHERITS (t1, s1); +CREATE TABLE inht1 (a int, b int); +CREATE TABLE inhs1 (b int, c int); +CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE t1 RENAME a TO aa; -ALTER TABLE t1 RENAME b TO bb; -- to be failed +ALTER TABLE inht1 RENAME a TO aa; +ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" -ALTER TABLE ts RENAME aa TO aaa; -- to be failed +ALTER TABLE inhts RENAME aa TO aaa; -- to be failed ERROR: cannot rename inherited column "aa" -ALTER TABLE ts RENAME d TO dd; -\d+ ts - Table "public.ts" +ALTER TABLE inhts RENAME d TO dd; +\d+ inhts + Table "public.inhts" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- aa | integer | | plain | | b | integer | | plain | | c | integer | | plain | | dd | integer | | plain | | -Inherits: t1, - s1 +Inherits: inht1, + inhs1 Has OIDs: no -DROP TABLE ts; +DROP TABLE inhts; -- Test for renaming in diamond inheritance -CREATE TABLE t2 (x int) INHERITS (t1); -CREATE TABLE t3 (y int) INHERITS (t1); -CREATE TABLE t4 (z int) INHERITS (t2, t3); +CREATE TABLE inht2 (x int) INHERITS (inht1); +CREATE TABLE inht3 (y int) INHERITS (inht1); +CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); NOTICE: merging multiple inherited definitions of column "aa" NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE t1 RENAME aa TO aaa; -\d+ t4 - Table "public.t4" +ALTER TABLE inht1 RENAME aa TO aaa; +\d+ inht4 + Table "public.inht4" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- aaa | integer | | plain | | @@ -1135,17 +916,17 @@ ALTER TABLE t1 RENAME aa TO aaa; x | integer | | plain | | y | integer | | plain | | z | integer | | plain | | -Inherits: t2, - t3 +Inherits: inht2, + inht3 Has OIDs: no -CREATE TABLE ts (d int) INHERITS (t2, s1); +CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE t1 RENAME aaa TO aaaa; -ALTER TABLE t1 RENAME b TO bb; -- to be failed +ALTER TABLE inht1 RENAME aaa TO aaaa; +ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" -\d+ ts - Table "public.ts" +\d+ inhts + Table "public.inhts" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- aaaa | integer | | plain | | @@ -1153,12 +934,12 @@ ERROR: cannot rename inherited column "b" x | integer | | plain | | c | integer | | plain | | d | integer | | plain | | -Inherits: t2, - s1 +Inherits: inht2, + inhs1 Has OIDs: no WITH RECURSIVE r AS ( - SELECT 't1'::regclass AS inhrelid + SELECT 'inht1'::regclass AS inhrelid UNION ALL SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent ) @@ -1169,26 +950,26 @@ SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected ORDER BY a.attrelid::regclass::name, a.attnum; attrelid | attname | attinhcount | expected ----------+---------+-------------+---------- - t2 | aaaa | 1 | 1 - t2 | b | 1 | 1 - t3 | aaaa | 1 | 1 - t3 | b | 1 | 1 - t4 | aaaa | 2 | 2 - t4 | b | 2 | 2 - t4 | x | 1 | 2 - t4 | y | 1 | 2 - ts | aaaa | 1 | 1 - ts | b | 2 | 1 - ts | x | 1 | 1 - ts | c | 1 | 1 + inht2 | aaaa | 1 | 1 + inht2 | b | 1 | 1 + inht3 | aaaa | 1 | 1 + inht3 | b | 1 | 1 + inht4 | aaaa | 2 | 2 + inht4 | b | 2 | 2 + inht4 | x | 1 | 2 + inht4 | y | 1 | 2 + inhts | aaaa | 1 | 1 + inhts | b | 2 | 1 + inhts | x | 1 | 1 + inhts | c | 1 | 1 (12 rows) -DROP TABLE t1, s1 CASCADE; +DROP TABLE inht1, inhs1 CASCADE; NOTICE: drop cascades to 4 other objects -DETAIL: drop cascades to table t2 -drop cascades to table ts -drop cascades to table t3 -drop cascades to table t4 +DETAIL: drop cascades to table inht2 +drop cascades to table inhts +drop cascades to table inht3 +drop cascades to table inht4 -- -- Test merge-append plans for inheritance trees -- diff --git i/src/test/regress/expected/sanity_check.out w/src/test/regress/expected/sanity_check.out index 9cae9d8..31711d2 100644 --- i/src/test/regress/expected/sanity_check.out +++ w/src/test/regress/expected/sanity_check.out @@ -54,7 +54,6 @@ SELECT relname, relhasindex hobbies_r | f ihighway | t inet_tbl | f - inhe | f inhf | f inhx | t insert_tbl | f @@ -164,7 +163,7 @@ SELECT relname, relhasindex timetz_tbl | f tinterval_tbl | f varchar_tbl | f -(153 rows) +(152 rows) -- -- another sanity check: every system catalog that has OIDs should have diff --git i/src/test/regress/output/misc.source w/src/test/regress/output/misc.source index b57c554..e887773 100644 --- i/src/test/regress/output/misc.source +++ w/src/test/regress/output/misc.source @@ -626,7 +626,6 @@ SELECT user_relns() AS user_relns iexit ihighway inet_tbl - inhe inhf inhx insert_seq @@ -685,7 +684,7 @@ SELECT user_relns() AS user_relns toyemp varchar_tbl xacttest -(107 rows) +(106 rows) SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))); name diff --git i/src/test/regress/parallel_schedule w/src/test/regress/parallel_schedule index b47b08b..3bedad0 100644 --- i/src/test/regress/parallel_schedule +++ w/src/test/regress/parallel_schedule @@ -59,7 +59,7 @@ test: create_index create_view # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_cast constraints triggers inherit typed_table vacuum drop_if_exists +test: create_aggregate create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git i/src/test/regress/serial_schedule w/src/test/regress/serial_schedule index 57806b5..0b64569 100644 --- i/src/test/regress/serial_schedule +++ w/src/test/regress/serial_schedule @@ -61,6 +61,7 @@ test: create_cast test: constraints test: triggers test: inherit +test: create_table_like test: typed_table test: vacuum test: drop_if_exists diff --git i/src/test/regress/sql/create_table_like.sql w/src/test/regress/sql/create_table_like.sql index e69de29..90a6921 100644 --- i/src/test/regress/sql/create_table_like.sql +++ w/src/test/regress/sql/create_table_like.sql @@ -0,0 +1,118 @@ +/* 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 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 */ +SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ +SELECT * FROM ctlb; /* Has ee entry */ +SELECT * FROM ctla; /* Has ee entry */ + +CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ + +CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); +INSERT INTO inhf DEFAULT VALUES; +SELECT * FROM inhf; /* Single entry with value 'text' */ + +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 */ +SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ +DROP TABLE inhg; + +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 +DROP TABLE inhg; +/* Multiple primary keys creation should fail */ +CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ +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 +DROP TABLE inhg; +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)); +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); +ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; +ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; +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 +CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); +\d+ ctlt12_comments +CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); +\d+ ctlt1_inh +SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; +CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); +\d+ ctlt13_inh +CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); +\d+ ctlt13_like +SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; + +CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); +\d+ 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; + +CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4); +CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1); + +DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE; + + +/* LIKE with other relation kinds */ + +CREATE TABLE ctlt4 (a int, b text); + +CREATE SEQUENCE ctlseq1; +CREATE TABLE ctlt10 (LIKE ctlseq1); -- fail + +CREATE VIEW ctlv1 AS SELECT * FROM ctlt4; +CREATE TABLE ctlt11 (LIKE ctlv1); + +CREATE TYPE ctlty1 AS (a int, b text); +CREATE TABLE ctlt12 (LIKE ctlty1); -- TODO: currently fails + +DROP SEQUENCE ctlseq1; +DROP TYPE ctlty1; +DROP VIEW ctlv1; +DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt12; diff --git i/src/test/regress/sql/inherit.sql w/src/test/regress/sql/inherit.sql index 6914404..4dfbc07 100644 --- i/src/test/regress/sql/inherit.sql +++ w/src/test/regress/sql/inherit.sql @@ -133,56 +133,6 @@ CREATE TABLE otherchild (tomorrow date default now()) DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; -/* 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 inhe (ee text, LIKE inhx) inherits (b); -INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4'); -SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */ -SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ -SELECT * FROM b; /* Has ee entry */ -SELECT * FROM a; /* Has ee entry */ - -CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ - -CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); -INSERT INTO inhf DEFAULT VALUES; -SELECT * FROM inhf; /* Single entry with value 'text' */ - -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 */ -SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ -DROP TABLE inhg; - -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 -DROP TABLE inhg; -/* Multiple primary keys creation should fail */ -CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ -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 -DROP TABLE inhg; -DROP TABLE inhz; - -- Test changing the type of inherited columns insert into d values('test','one','two','three'); alter table a alter column aa type integer using bit_length(aa); @@ -302,81 +252,34 @@ alter table pp1 add column a2 int check (a2 > 0); \d cc2 drop table pp1 cascade; --- including storage and comments -CREATE TABLE t1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); -CREATE INDEX t1_b_key ON t1 (b); -CREATE INDEX t1_fnidx ON t1 ((a || b)); -COMMENT ON COLUMN t1.a IS 'A'; -COMMENT ON COLUMN t1.b IS 'B'; -COMMENT ON CONSTRAINT t1_a_check ON t1 IS 't1_a_check'; -COMMENT ON INDEX t1_pkey IS 'index pkey'; -COMMENT ON INDEX t1_b_key IS 'index b_key'; -ALTER TABLE t1 ALTER COLUMN a SET STORAGE MAIN; - -CREATE TABLE t2 (c text); -ALTER TABLE t2 ALTER COLUMN c SET STORAGE EXTERNAL; -COMMENT ON COLUMN t2.c IS 'C'; - -CREATE TABLE t3 (a text CHECK (length(a) < 5), c text); -ALTER TABLE t3 ALTER COLUMN c SET STORAGE EXTERNAL; -ALTER TABLE t3 ALTER COLUMN a SET STORAGE MAIN; -COMMENT ON COLUMN t3.a IS 'A3'; -COMMENT ON COLUMN t3.c IS 'C'; -COMMENT ON CONSTRAINT t3_a_check ON t3 IS 't3_a_check'; - -CREATE TABLE t4 (a text, c text); -ALTER TABLE t4 ALTER COLUMN c SET STORAGE EXTERNAL; - -CREATE TABLE t12_storage (LIKE t1 INCLUDING STORAGE, LIKE t2 INCLUDING STORAGE); -\d+ t12_storage -CREATE TABLE t12_comments (LIKE t1 INCLUDING COMMENTS, LIKE t2 INCLUDING COMMENTS); -\d+ t12_comments -CREATE TABLE t1_inh (LIKE t1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (t1); -\d+ t1_inh -SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 't1_inh'::regclass; -CREATE TABLE t13_inh () INHERITS (t1, t3); -\d+ t13_inh -CREATE TABLE t13_like (LIKE t3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (t1); -\d+ t13_like -SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 't13_like'::regclass; - -CREATE TABLE t_all (LIKE t1 INCLUDING ALL); -\d+ t_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 = 't_all'::regclass ORDER BY c.relname, objsubid; - -CREATE TABLE inh_error1 () INHERITS (t1, t4); -CREATE TABLE inh_error2 (LIKE t4 INCLUDING STORAGE) INHERITS (t1); - -DROP TABLE t1, t2, t3, t4, t12_storage, t12_comments, t1_inh, t13_inh, t13_like, t_all; - -- Test for renaming in simple multiple inheritance -CREATE TABLE t1 (a int, b int); -CREATE TABLE s1 (b int, c int); -CREATE TABLE ts (d int) INHERITS (t1, s1); +CREATE TABLE inht1 (a int, b int); +CREATE TABLE inhs1 (b int, c int); +CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); -ALTER TABLE t1 RENAME a TO aa; -ALTER TABLE t1 RENAME b TO bb; -- to be failed -ALTER TABLE ts RENAME aa TO aaa; -- to be failed -ALTER TABLE ts RENAME d TO dd; -\d+ ts +ALTER TABLE inht1 RENAME a TO aa; +ALTER TABLE inht1 RENAME b TO bb; -- to be failed +ALTER TABLE inhts RENAME aa TO aaa; -- to be failed +ALTER TABLE inhts RENAME d TO dd; +\d+ inhts -DROP TABLE ts; +DROP TABLE inhts; -- Test for renaming in diamond inheritance -CREATE TABLE t2 (x int) INHERITS (t1); -CREATE TABLE t3 (y int) INHERITS (t1); -CREATE TABLE t4 (z int) INHERITS (t2, t3); +CREATE TABLE inht2 (x int) INHERITS (inht1); +CREATE TABLE inht3 (y int) INHERITS (inht1); +CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); -ALTER TABLE t1 RENAME aa TO aaa; -\d+ t4 +ALTER TABLE inht1 RENAME aa TO aaa; +\d+ inht4 -CREATE TABLE ts (d int) INHERITS (t2, s1); -ALTER TABLE t1 RENAME aaa TO aaaa; -ALTER TABLE t1 RENAME b TO bb; -- to be failed -\d+ ts +CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); +ALTER TABLE inht1 RENAME aaa TO aaaa; +ALTER TABLE inht1 RENAME b TO bb; -- to be failed +\d+ inhts WITH RECURSIVE r AS ( - SELECT 't1'::regclass AS inhrelid + SELECT 'inht1'::regclass AS inhrelid UNION ALL SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent ) @@ -386,7 +289,7 @@ SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal ORDER BY a.attrelid::regclass::name, a.attnum; -DROP TABLE t1, s1 CASCADE; +DROP TABLE inht1, inhs1 CASCADE; -- -- Test merge-append plans for inheritance trees