On tis, 2011-11-08 at 21:49 +0000, Thom Brown wrote:
> I found the following error message misleading:
>
> test=# create table cows2 (LIKE cows);
> ERROR: inherited relation "cows" is not a table
> STATEMENT: create table cows2 (LIKE cows);
>
> I'm not trying to inherit a relation, I'm trying to base a table on
> it.
It's not only the error message that's misleading, but the whole code,
because the entire code for CREATE TABLE ... (LIKE ...) claims to do
"inheritance" based on an ancient understanding of the SQL standard. I
know this has confused me many times already, so I decided to clean this
up and rename all the internal parser structures, split up the
regression tests for real inheritance and CREATE TABLE LIKE, and adjust
the error messages. Patch attached.
> As it happens, "cows" is a foreign table, which *is* a table,
> just not a regular table. It might be useful to add support to clone
> foreign tables into regular tables, the use-case being that you may
> wish to import all the data locally into a table of the same
> structure.
This is easy to fix, and I mangled it into my big renaming patch, which
I shouldn't have. Anyway, one question that's perhaps worth discussing
is whether we should allow and disallow the various INCLUDING options
depending on the relation type. For example, views don't have indexes,
so should we disallow INCLUDING INDEXES or just assume they don't have
any?
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 ] <replaceable class="PARAMETER">table_name</replaceable> ( [
{ <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
| <replaceable>table_constraint</replaceable>
- | LIKE <replaceable>parent_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
+ | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
] )
[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
@@ -312,7 +312,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
- <term><literal>LIKE <replaceable>parent_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
+ <term><literal>LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term>
<listitem>
<para>
The <literal>LIKE</literal> 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 <subtable> portion of a CREATE TABLE statement into
+ * Change the LIKE <srctable> portion of a CREATE TABLE statement into
* column definitions which recreate the user defined column portions of
- * <subtable>.
+ * <srctable>.
*/
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
