diff options
| -rw-r--r-- | doc/src/sgml/ref/create_foreign_table.sgml | 114 | ||||
| -rw-r--r-- | src/backend/parser/parse_utilcmd.c | 24 | ||||
| -rw-r--r-- | src/test/regress/expected/create_table_like.out | 103 | ||||
| -rw-r--r-- | src/test/regress/sql/create_table_like.sql | 42 |
4 files changed, 271 insertions, 12 deletions
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index e0b0e075c2c..d08834ac9d2 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -23,7 +23,8 @@ PostgreSQL documentation <synopsis> CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [ { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ] - | <replaceable>table_constraint</replaceable> } + | <replaceable>table_constraint</replaceable> + | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] } [, ... ] ] ) [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ] @@ -57,6 +58,10 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] } [ ENFORCED | NOT ENFORCED ] +<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase> + +{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL } + <phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase> IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) | @@ -192,6 +197,111 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry> + <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 + the new table automatically copies all column names, their data types, + and their not-null constraints. + </para> + <para> + Unlike <literal>INHERITS</literal>, the new table and original table + are completely decoupled after creation is complete. Changes to the + original table will not be applied to the new table, and it is not + possible to include data of the new table in scans of the original + table. + </para> + <para> + Also unlike <literal>INHERITS</literal>, columns and + constraints copied by <literal>LIKE</literal> are not merged with similarly + named columns and constraints. + If the same name is specified explicitly or in another + <literal>LIKE</literal> clause, an error is signaled. + </para> + <para> + The optional <replaceable>like_option</replaceable> clauses specify + which additional properties of the original table to copy. Specifying + <literal>INCLUDING</literal> copies the property, specifying + <literal>EXCLUDING</literal> omits the property. + <literal>EXCLUDING</literal> is the default. If multiple specifications + are made for the same kind of object, the last one is used. The + available options are: + + <variablelist> + <varlistentry> + <term><literal>INCLUDING COMMENTS</literal></term> + <listitem> + <para> + Comments for the copied columns, constraints, and indexes will be + copied. The default behavior is to exclude comments, resulting in + the copied columns and constraints in the new table having no + comments. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INCLUDING CONSTRAINTS</literal></term> + <listitem> + <para> + <literal>CHECK</literal> constraints will be copied. No distinction + is made between column constraints and table constraints. Not-null + constraints are always copied to the new table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INCLUDING DEFAULTS</literal></term> + <listitem> + <para> + Default expressions for the copied column definitions will be + copied. Otherwise, default expressions are not copied, resulting in + the copied columns in the new table having null defaults. Note that + copying defaults that call database-modification functions, such as + <function>nextval</function>, may create a functional linkage + between the original and new tables. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INCLUDING GENERATED</literal></term> + <listitem> + <para> + Any generation expressions of copied column definitions will be + copied. By default, new columns will be regular base columns. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INCLUDING STATISTICS</literal></term> + <listitem> + <para> + Extended statistics are copied to the new table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INCLUDING ALL</literal></term> + <listitem> + <para> + <literal>INCLUDING ALL</literal> is an abbreviated form selecting + all the available individual options. (It could be useful to write + individual <literal>EXCLUDING</literal> clauses after + <literal>INCLUDING ALL</literal> to select all but some specific + options.) + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term> <listitem> <para> @@ -448,6 +558,8 @@ CREATE FOREIGN TABLE measurement_y2016m07 The ability to specify column default values is also a <productname>PostgreSQL</productname> extension. Table inheritance, in the form defined by <productname>PostgreSQL</productname>, is nonstandard. + The <literal>LIKE</literal> clause, as supported in this command, is + nonstandard. </para> </refsect1> diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index eb7716cd84c..abbe1bb45a3 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1131,6 +1131,10 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) * process at this point, add the TableLikeClause to cxt->likeclauses, which * will cause utility.c to call expandTableLikeClause() after the new * table has been created. + * + * Some options are ignored. For example, as foreign tables have no storage, + * these INCLUDING options have no effect: STORAGE, COMPRESSION, IDENTITY + * and INDEXES. Similarly, INCLUDING INDEXES is ignored from a view. */ static void transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause) @@ -1145,12 +1149,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla setup_parser_errposition_callback(&pcbstate, cxt->pstate, table_like_clause->relation->location); - /* we could support LIKE in many cases, but worry about it another day */ - if (cxt->isforeign) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("LIKE is not supported for creating foreign tables"))); - /* Open the relation referenced by the LIKE clause */ relation = relation_openrv(table_like_clause->relation, AccessShareLock); @@ -1231,7 +1229,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla * Copy identity if requested */ if (attribute->attidentity && - (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY)) + (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY) && + !cxt->isforeign) { Oid seq_relid; List *seq_options; @@ -1250,14 +1249,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla } /* Likewise, copy storage if requested */ - if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) + if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) && + !cxt->isforeign) def->storage = attribute->attstorage; else def->storage = 0; /* Likewise, copy compression if requested */ - if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 - && CompressionMethodIsValid(attribute->attcompression)) + if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0 && + CompressionMethodIsValid(attribute->attcompression) && + !cxt->isforeign) def->compression = pstrdup(GetCompressionMethodName(attribute->attcompression)); else @@ -1536,7 +1537,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) * Process indexes if required. */ if ((table_like_clause->options & CREATE_TABLE_LIKE_INDEXES) && - relation->rd_rel->relhasindex) + relation->rd_rel->relhasindex && + childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) { List *parent_indexes; ListCell *l; diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 2cebe382432..bf34289e984 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -566,3 +566,106 @@ DROP TYPE ctlty1; DROP VIEW ctlv1; DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; NOTICE: table "ctlt10" does not exist, skipping +-- +-- CREATE FOREIGN TABLE LIKE +-- +CREATE FOREIGN DATA WRAPPER ctl_dummy; +CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy; +CREATE TABLE ctl_table(a int PRIMARY KEY, + b varchar COMPRESSION pglz, + c int GENERATED ALWAYS AS (a * 2) STORED, + d bigint GENERATED ALWAYS AS IDENTITY, + e int DEFAULT 1); +CREATE INDEX ctl_table_a_key ON ctl_table(a); +COMMENT ON COLUMN ctl_table.b IS 'Column b'; +CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; +ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text'); +ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN; +\d+ ctl_table + Table "public.ctl_table" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+-------------------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | not null | | plain | | + b | character varying | | | | main | | Column b + c | integer | | | generated always as (a * 2) stored | plain | | + d | bigint | | not null | generated always as identity | plain | | + e | integer | | | 1 | plain | | +Indexes: + "ctl_table_pkey" PRIMARY KEY, btree (a) + "ctl_table_a_key" btree (a) +Check constraints: + "foo" CHECK (b::text = 'text'::text) +Statistics objects: + "public.ctl_table_stat" ON a, b FROM ctl_table +Not-null constraints: + "ctl_table_a_not_null" NOT NULL "a" + "ctl_table_d_not_null" NOT NULL "d" + +-- Test EXCLUDING ALL +CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table1 + Foreign table "public.ctl_foreign_table1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+-------------------+-----------+----------+---------+-------------+----------+--------------+------------- + a | integer | | not null | | | plain | | + b | character varying | | | | | extended | | + c | integer | | | | | plain | | + d | bigint | | not null | | | plain | | + e | integer | | | | | plain | | +Not-null constraints: + "ctl_table_a_not_null" NOT NULL "a" + "ctl_table_d_not_null" NOT NULL "d" +Server: ctl_s0 + +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum; + attname | attcompression +---------+---------------- + a | + b | + c | + d | + e | +(5 rows) + +-- Test INCLUDING ALL +-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table2 + Foreign table "public.ctl_foreign_table2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+------------- + a | integer | | not null | | | plain | | + b | character varying | | | | | extended | | Column b + c | integer | | | generated always as (a * 2) stored | | plain | | + d | bigint | | not null | | | plain | | + e | integer | | | 1 | | plain | | +Check constraints: + "foo" CHECK (b::text = 'text'::text) +Statistics objects: + "public.ctl_foreign_table2_a_b_stat" ON a, b FROM ctl_foreign_table2 +Not-null constraints: + "ctl_table_a_not_null" NOT NULL "a" + "ctl_table_d_not_null" NOT NULL "d" +Server: ctl_s0 + +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum; + attname | attcompression +---------+---------------- + a | + b | + c | + d | + e | +(5 rows) + +DROP TABLE ctl_table; +DROP FOREIGN TABLE ctl_foreign_table1; +DROP FOREIGN TABLE ctl_foreign_table2; +DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE; +NOTICE: drop cascades to server ctl_s0 diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 63a60303659..6e21722aaeb 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -225,3 +225,45 @@ DROP SEQUENCE ctlseq1; DROP TYPE ctlty1; DROP VIEW ctlv1; DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; + +-- +-- CREATE FOREIGN TABLE LIKE +-- +CREATE FOREIGN DATA WRAPPER ctl_dummy; +CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy; + +CREATE TABLE ctl_table(a int PRIMARY KEY, + b varchar COMPRESSION pglz, + c int GENERATED ALWAYS AS (a * 2) STORED, + d bigint GENERATED ALWAYS AS IDENTITY, + e int DEFAULT 1); + +CREATE INDEX ctl_table_a_key ON ctl_table(a); +COMMENT ON COLUMN ctl_table.b IS 'Column b'; +CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; +ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text'); +ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN; + +\d+ ctl_table + +-- Test EXCLUDING ALL +CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table1 +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum; + +-- Test INCLUDING ALL +-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table2 +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum; + +DROP TABLE ctl_table; +DROP FOREIGN TABLE ctl_foreign_table1; +DROP FOREIGN TABLE ctl_foreign_table2; +DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE; |