0001-hidden-column-v7.patch
text/x-patch
Filename: 0001-hidden-column-v7.patch
Type: text/x-patch
Part: 0
Patch
Format: unified
Series: patch v7-0001
| File | + | − |
|---|---|---|
| contrib/test_decoding/expected/ddl.out | 25 | 25 |
| doc/src/sgml/catalogs.sgml | 12 | 0 |
| doc/src/sgml/ddl.sgml | 203 | 0 |
| doc/src/sgml/ref/alter_table.sgml | 21 | 0 |
| doc/src/sgml/ref/create_index.sgml | 1 | 1 |
| doc/src/sgml/ref/create_table.sgml | 12 | 2 |
| doc/src/sgml/ref/psql-ref.sgml | 1 | 1 |
| src/backend/access/common/tupdesc.c | 6 | 0 |
| src/backend/catalog/heap.c | 2 | 0 |
| src/backend/commands/copy.c | 2 | 2 |
| src/backend/commands/tablecmds.c | 186 | 0 |
| src/backend/parser/gram.y | 22 | 1 |
| src/backend/parser/parse_relation.c | 6 | 0 |
| src/backend/parser/parse_target.c | 1 | 2 |
| src/backend/parser/parse_utilcmd.c | 7 | 0 |
| src/backend/utils/cache/relcache.c | 1 | 0 |
| src/bin/pg_dump/pg_dump.c | 22 | 0 |
| src/bin/pg_dump/pg_dump.h | 1 | 0 |
| src/bin/psql/describe.c | 18 | 2 |
| src/bin/psql/tab-complete.c | 2 | 2 |
| src/include/catalog/pg_attribute.h | 8 | 1 |
| src/include/nodes/parsenodes.h | 6 | 1 |
| src/include/parser/kwlist.h | 1 | 0 |
| src/test/regress/expected/alter_table.out | 9 | 9 |
| src/test/regress/expected/compression.out | 44 | 44 |
| src/test/regress/expected/copy2.out | 4 | 4 |
| src/test/regress/expected/create_table_like.out | 44 | 44 |
| src/test/regress/expected/create_table.out | 71 | 71 |
| src/test/regress/expected/domain.out | 8 | 8 |
| src/test/regress/expected/foreign_data.out | 129 | 129 |
| src/test/regress/expected/identity.out | 8 | 8 |
| src/test/regress/expected/inherit.out | 69 | 69 |
| src/test/regress/expected/insert.out | 59 | 59 |
| src/test/regress/expected/psql.out | 20 | 20 |
| src/test/regress/expected/publication.out | 20 | 20 |
| src/test/regress/expected/replica_identity.out | 7 | 7 |
| src/test/regress/expected/rowsecurity.out | 8 | 8 |
| src/test/regress/expected/rules.out | 15 | 15 |
| src/test/regress/expected/stats_ext.out | 5 | 5 |
| src/test/regress/expected/triggers.out | 4 | 4 |
| src/test/regress/expected/unexpanded.out | 580 | 0 |
| src/test/regress/expected/update.out | 8 | 8 |
| src/test/regress/output/tablespace.source | 8 | 8 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/unexpanded.sql | 278 | 0 |
diff --git a/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out
index 4ff0044c78..e08898060a 100644
--- a/contrib/test_decoding/expected/ddl.out
+++ b/contrib/test_decoding/expected/ddl.out
@@ -438,12 +438,12 @@ CREATE TABLE replication_metadata (
WITH (user_catalog_table = true)
;
\d+ replication_metadata
- Table "public.replication_metadata"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+--------------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | |
- relation | name | | not null | | plain | |
- options | text[] | | | | extended | |
+ Table "public.replication_metadata"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+----------+---------+-----------+----------+--------------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | | plain | |
+ relation | name | | not null | | | plain | |
+ options | text[] | | | | | extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
Options: user_catalog_table=true
@@ -452,12 +452,12 @@ INSERT INTO replication_metadata(relation, options)
VALUES ('foo', ARRAY['a', 'b']);
ALTER TABLE replication_metadata RESET (user_catalog_table);
\d+ replication_metadata
- Table "public.replication_metadata"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+--------------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | |
- relation | name | | not null | | plain | |
- options | text[] | | | | extended | |
+ Table "public.replication_metadata"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+----------+---------+-----------+----------+--------------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | | plain | |
+ relation | name | | not null | | | plain | |
+ options | text[] | | | | | extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
@@ -465,12 +465,12 @@ INSERT INTO replication_metadata(relation, options)
VALUES ('bar', ARRAY['a', 'b']);
ALTER TABLE replication_metadata SET (user_catalog_table = true);
\d+ replication_metadata
- Table "public.replication_metadata"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+--------------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | |
- relation | name | | not null | | plain | |
- options | text[] | | | | extended | |
+ Table "public.replication_metadata"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+----------+---------+-----------+----------+--------------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | | plain | |
+ relation | name | | not null | | | plain | |
+ options | text[] | | | | | extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
Options: user_catalog_table=true
@@ -483,13 +483,13 @@ ALTER TABLE replication_metadata ALTER COLUMN rewritemeornot TYPE text;
ERROR: cannot rewrite table "replication_metadata" used as a catalog table
ALTER TABLE replication_metadata SET (user_catalog_table = false);
\d+ replication_metadata
- Table "public.replication_metadata"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+---------+-----------+----------+--------------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | |
- relation | name | | not null | | plain | |
- options | text[] | | | | extended | |
- rewritemeornot | integer | | | | plain | |
+ Table "public.replication_metadata"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+----------------+---------+-----------+----------+--------------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | | plain | |
+ relation | name | | not null | | | plain | |
+ options | text[] | | | | | extended | |
+ rewritemeornot | integer | | | | | plain | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
Options: user_catalog_table=false
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c1d11be73f..6ee068a813 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1417,6 +1417,18 @@
the column is null.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>attisunexpanded</structfield> <type>bool</type>
+ </para>
+ <para>
+ This column is not expanded in the resulting target list of a
+ <literal>SELECT *</literal> or in an <literal>INSERT</literal> without
+ destination column list. An unexpanded column can still be used, but it
+ must be explicitly referenced.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 94f745aed0..2ab75d9fce 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -377,6 +377,209 @@ CREATE TABLE people (
</para>
</sect1>
+ <sect1 id="ddl-unexpanded-columns">
+ <title>Unexpanded Columns</title>
+
+ <indexterm zone="ddl-unexpanded-columns">
+ <primary>Unexpanded column</primary>
+ </indexterm>
+
+ <para>
+ An unexpanded column is just like a normal column except that it is not available
+ unless it is explicitly referenced. Columns with the <literal>UNEXEPANDED</literal>
+ attribute will not be part of the star expansion such that <literal>SELECT * FROM</literal> table doesn't
+ return a value for the columns. The same behavior applies to the <literal>COPY ... TO</literal>
+ statements when no columns are specified.
+ In order to be used, unexpanded columns must be explicitly included in the query.
+ Unexpanded column can always be referenced explicitly wherever a column name can
+ be specified, for example in a <literal>JOIN</literal>, a <literal>GROUP BY</literal>
+ or an <literal>ORDER BY</literal> clause.
+ </para>
+ <para>
+ When inserting data into a table, an <literal>INSERT</literal> statement without a target column
+ list does not expect values for any unexpanded columns. In such cases, if the
+ input includes a value for a unexpanded column, that value does not have a target
+ column and an error is returned. Because an <literal>INSERT</literal> statement without a
+ column list does not include values for unexpanded columns, any columns that are
+ defined as hidden and <literal>NOT NULL</literal> must have a default value.
+ The same behavior with unexpanded column applies to the <literal>COPY ... FROM</literal>
+ statements when no destination columns are specified.
+ </para>
+ <para>
+ The <command>ALTER TABLE</command> statement can be used to set the unexpanded attribute
+ to a column or to remove it.
+<programlisting>
+ALTER TABLE people ALTER COLUMN rowid <emphasis>SET UNEXPANDED</emphasis>;
+ALTER TABLE people ALTER COLUMN rowid <emphasis>DROP UNEXPANDED</emphasis>;
+</programlisting>
+ </para>
+
+ <para>
+ If a table is created using a <command>CREATE TABLE</command> statement with
+ the LIKE clause, any unexpanded columns in the source table is copied to the
+ new table but by default the unexpanded attribute is not set.
+<programlisting>
+CREATE TABLE foo (LIKE t1);
+</programlisting>
+ To copied the <literal>UNEXPANDED</literal> attribute it must be explicitely
+ included.
+<programlisting>
+CREATE TABLE foo (LIKE t1 <emphasis>INCLUDING UNEXPANDED</emphasis>);
+</programlisting>
+ </para>
+
+ <para>
+ Since <literal>SELECT *</literal> does not return the unexpanded columns,
+ new tables or views created in this manner will have no trace of the
+ unexpanded columns. If explicitely referenced in the <literal>SELECT</literal>
+ statement, the columns will be brought into the view/new table, but the
+ <literal>UNEXPANDED</literal> attribute will not.
+<programlisting>
+db=# \d+ t1
+ Table "public.t1"
+ Column | Type | Collation | Nullable | Expanded | Default
+--------+---------+-----------+----------+------------+---------
+ col1 | integer | | | unexpanded | 13
+ col2 | text | | not null | |
+
+test=# CREATE TABLE t2 AS SELECT * FROM t1;
+SELECT 2
+db=# \d t2
+ Table "public.t2"
+ Column | Type | Collation | Nullable | Expanded | Default
+--------+------+-----------+----------+--------+---------
+ col2 | text | | | |
+
+test=# CREATE TABLE t3 AS SELECT col1, col2 FROM t1;
+SELECT 2
+db=# \d t2
+ Table "public.t2"
+ Column | Type | Collation | Nullable | Expanded | Default
+--------+---------+-----------+----------+----------+---------
+ col1 | integer | | | | 13
+ col2 | text | | | |
+</programlisting>
+ </para>
+
+ <para>
+ Several other points apply to the definition of unexpanded columns and tables
+ involving such columns:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Unexpanded columns are also supported in created temporary or unlogged tables
+ but not in foreign table.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Unexpanded columns support the usual column attributes as well as all
+ constraints.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ An unexpanded column can be explicitly referenced in a
+ <command>CREATE INDEX</command> statement or <command>ALTER TABLE</command>
+ statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Foreign key constraints can be defined on unexpanded columns and unexpanded columns
+ can be referenced in foreign key constraints.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Virtual columns can be flagged unexpanded as well as identity columns.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A table can be partitioned by an unexpanded column.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ User-defined types can not contain unexpanded attributes.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A table can have all columns unexpanded, PostgreSQL support table without column.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Unexpanded column are inherited.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Changing a column's unexpanded attribute after defining a view that
+ references the column does not change the view behavior.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The unexpanded column attribute has no effect when the whole-row or star is used in a function. For example:
+<programlisting>
+SELECT row_to_json(t) FROM htest0 t;
+ row_to_json
+--------------------------
+ {"a":1,"b":"one"}
+ {"a":2,"b":"two"}
+
+SELECT row_to_json(t.*) FROM htest0 t;
+ row_to_json
+--------------------------
+ {"a":1,"b":"one"}
+ {"a":2,"b":"two"}
+</programlisting>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Insert without column list with values retrieved from a query using the
+ wild-card star (<literal>INSERT INTO t2 SELECT * FROM t1;</literal>) will
+ not include unexpanded columns from the selected table.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The unexpanded column will not be part of the values returned by a
+ <literal>RETURNING *</literal>. For example:
+<programlisting>
+CREATE TABLE htest1 (a bigserial PRIMARY KEY, b text);
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+INSERT INTO htest1 VALUES ('htest1 one');
+SELECT a, b FROM htest1;
+ a | b
+---+------------
+ 1 | htest1 one
+
+WITH cte AS (
+ DELETE FROM htest1 RETURNING *
+) SELECT * FROM cte;
+ b
+------------
+ htest1 one
+</programlisting>
+ Note that the presence of an unexpanded column prevent the use of ROWTYPE in plpgsql code with <literal>RETURNING * INTO</literal>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Information about whether a column is expanded or not is available from
+ the <structfield>attisunexpanded</structfield> column of the
+ <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>
+ catalog table.
+ </para>
+ </sect1>
+
<sect1 id="ddl-constraints">
<title>Constraints</title>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 81291577f8..940a6066ab 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -46,6 +46,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } UNEXPANDED
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
@@ -245,6 +246,26 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET</literal>/<literal>DROP UNEXPANDED</literal></term>
+ <listitem>
+ <para>
+ When a column is defined with the <literal>UNEXPANDED</literal> attribute,
+ it is not available unless it is explicitly referenced. For example,
+ if a SELECT * FROM table is executed, unexpanded columns are not returned
+ in the resulting rows. Same, if an INSERT statement without a target
+ columns list is executed it does not expect values for any unexpanded columns.
+ An unexpanded column can always be referenced explicitly wherever a column
+ name can be specified, for example in an ORDER BY or a JOIN clause.
+ (See <xref linkend="ddl-unexpanded-columns"/> for more information on unexpanded column).
+ </para>
+ <para>
+ The unexpansion behavior will only apply in subsequent SELECT or INSERT commands;
+ it does not cause running queries behavior to change.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index cc484d5b39..d24bb67ddc 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -635,7 +635,7 @@ postgres=# \d tab
Table "public.tab"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
- col | integer | | |
+ col | integer | | |
Indexes:
"idx" btree (col) INVALID
</programlisting>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 473a0a4aeb..ea8b11a8e2 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -87,7 +87,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | UNEXPANDED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -676,6 +676,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>INCLUDING UNEXPANDED</literal></term>
+ <listitem>
+ <para>
+ Any unexpanded attribute of copied column definitions will be
+ copied. By default, new columns will be part of the star expansion.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>INCLUDING IDENTITY</literal></term>
<listitem>
@@ -737,6 +747,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
</listitem>
</varlistentry>
+
</variablelist>
</para>
@@ -1349,7 +1360,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
</listitem>
</varlistentry>
-
</variablelist>
<refsect2 id="sql-createtable-storage-parameters" xreflabel="Storage Parameters">
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 48248f750e..6d6becf646 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4908,7 +4908,7 @@ testdb=> <userinput>\d my_table</userinput>
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
first | integer | | not null | 0
- second | text | | |
+ second | text | | |
</programlisting>
Now we change the prompt to something more interesting:
<programlisting>
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 4c63bd4dc6..c6b6a05448 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -133,6 +133,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
att->atthasmissing = false;
att->attidentity = '\0';
att->attgenerated = '\0';
+ att->attisunexpanded = false;
}
/* We can copy the tuple type identification, too */
@@ -463,6 +464,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->attcollation != attr2->attcollation)
return false;
+ if (attr1->attisunexpanded != attr2->attisunexpanded)
+ return false;
/* variable-length fields are not even present... */
}
@@ -644,6 +647,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attstorage = typeForm->typstorage;
att->attcompression = InvalidCompressionMethod;
att->attcollation = typeForm->typcollation;
+ att->attisunexpanded = false;
ReleaseSysCache(tuple);
}
@@ -691,6 +695,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
+ att->attisunexpanded = false;
/* attacl, attoptions and attfdwoptions are not present in tupledescs */
att->atttypid = oidtypeid;
@@ -839,6 +844,7 @@ BuildDescForRelation(List *schema)
has_not_null |= entry->is_not_null;
att->attislocal = entry->is_local;
att->attinhcount = entry->inhcount;
+ att->attisunexpanded = entry->is_unexpanded;
}
if (has_not_null)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 81cc39fb70..33646c3d7f 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -792,7 +792,9 @@ InsertPgAttributeTuples(Relation pg_attribute_rel,
slot[slotCount]->tts_values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(attrs->attisdropped);
slot[slotCount]->tts_values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(attrs->attislocal);
slot[slotCount]->tts_values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(attrs->attinhcount);
+ slot[slotCount]->tts_values[Anum_pg_attribute_attisunexpanded - 1] = BoolGetDatum(attrs->attisunexpanded);
slot[slotCount]->tts_values[Anum_pg_attribute_attcollation - 1] = ObjectIdGetDatum(attrs->attcollation);
+
if (attoptions && attoptions[natts] != (Datum) 0)
slot[slotCount]->tts_values[Anum_pg_attribute_attoptions - 1] = attoptions[natts];
else
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 53f4853141..42c54ffade 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -637,7 +637,7 @@ ProcessCopyOptions(ParseState *pstate,
*
* The input attnamelist is either the user-specified column list,
* or NIL if there was none (in which case we want all the non-dropped
- * columns).
+ * and not hidden columns).
*
* We don't include generated columns in the generated full list and we don't
* allow them to be specified explicitly. They don't make sense for COPY
@@ -659,7 +659,7 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List *attnamelist)
for (i = 0; i < attr_count; i++)
{
- if (TupleDescAttr(tupDesc, i)->attisdropped)
+ if (TupleDescAttr(tupDesc, i)->attisdropped || TupleDescAttr(tupDesc, i)->attisunexpanded)
continue;
if (TupleDescAttr(tupDesc, i)->attgenerated)
continue;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 857cc5ce6e..043b2c4494 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -602,6 +602,10 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, char *compression);
+static ObjectAddress ATExecDropUnexpanded(Relation rel, const char *colName,
+ LOCKMODE lockmode);
+static ObjectAddress ATExecSetUnexpanded(Relation rel, const char *colName,
+ LOCKMODE lockmode);
/* ----------------------------------------------------------------
@@ -647,6 +651,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
LOCKMODE parentLockmode;
const char *accessMethod = NULL;
Oid accessMethodId = InvalidOid;
+ bool has_visible_col = false;
/*
* Truncate relname to appropriate length (probably a waste of time, as
@@ -897,11 +902,25 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->generated)
attr->attgenerated = colDef->generated;
+ if (colDef->is_unexpanded)
+ attr->attisunexpanded = true;
+ else
+ has_visible_col = true;
+
if (colDef->compression)
attr->attcompression = GetAttributeCompression(attr->atttypid,
colDef->compression);
}
+ /*
+ * Verify that we have at least one visible column
+ * when there is hidden ones
+ */
+ if (attnum > 0 && !has_visible_col)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("a table must have at least one visible column")));
+
/*
* If the statement hasn't specified an access method, but we're defining
* a type of relation that needs one, use the default.
@@ -2340,6 +2359,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
coldef->cooked_default = restdef->cooked_default;
coldef->constraints = restdef->constraints;
coldef->is_from_type = false;
+ coldef->is_unexpanded = restdef->is_unexpanded;
schema = list_delete_nth_cell(schema, restpos);
}
else
@@ -2565,6 +2585,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("inherited column \"%s\" has a generation conflict",
attributeName)));
+ /* Merge of UNEXPANDED attribute = OR 'em together */
+ def->is_unexpanded |= attribute->attisunexpanded;
}
else
{
@@ -2592,6 +2614,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
pstrdup(GetCompressionMethodName(attribute->attcompression));
else
def->compression = NULL;
+ def->is_unexpanded = attribute->attisunexpanded;
inhSchema = lappend(inhSchema, def);
newattmap->attnums[parent_attno - 1] = ++child_attno;
}
@@ -2857,6 +2880,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->is_local = true;
/* Merge of NOT NULL constraints = OR 'em together */
def->is_not_null |= newdef->is_not_null;
+ /* Merge of UNEXPANDED attribute = OR 'em together */
+ def->is_unexpanded |= newdef->is_unexpanded;
/*
* Check for conflicts related to generated columns.
@@ -2951,6 +2976,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
{
found = true;
coldef->is_not_null |= restdef->is_not_null;
+ coldef->is_unexpanded |= restdef->is_unexpanded;
/*
* Override the parent's default value for this column
@@ -4207,6 +4233,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetIdentity:
case AT_DropExpression:
case AT_SetCompression:
+ case AT_DropUnexpanded:
+ case AT_SetUnexpanded:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -4495,6 +4523,16 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* This command never recurses */
pass = AT_PASS_DROP;
break;
+ case AT_SetUnexpanded:
+ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
+ case AT_DropUnexpanded:
+ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
+ /* This command never recurses */
+ pass = AT_PASS_DROP;
+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATPrepDropNotNull(rel, recurse, recursing);
@@ -4893,6 +4931,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_DropIdentity:
address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode);
break;
+ case AT_SetUnexpanded: /* ALTER COLUMN SET UNEXPANDED */
+ address = ATExecSetUnexpanded(rel, cmd->name, lockmode);
+ break;
+ case AT_DropUnexpanded: /* ALTER COLUMN DROP UNEXPANDED */
+ address = ATExecDropUnexpanded(rel, cmd->name, lockmode);
+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
address = ATExecDropNotNull(rel, cmd->name, lockmode);
break;
@@ -6135,6 +6179,10 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... DROP IDENTITY";
case AT_ReAddStatistics:
return NULL; /* not real grammar */
+ case AT_DropUnexpanded:
+ return "ALTER COLUMN ... DROP UNEXPANDED";
+ case AT_SetUnexpanded:
+ return "ALTER COLUMN ... SET UNEXPANDED";
}
return NULL;
@@ -6756,6 +6804,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
+ attribute.attisunexpanded = colDef->is_unexpanded;
attribute.attcollation = collOid;
/* attribute.attacl is handled by InsertPgAttributeTuples() */
@@ -7101,6 +7150,143 @@ ATPrepDropNotNull(Relation rel, bool recurse, bool recursing)
}
}
+/*
+ * Return the address of the modified column. If the column was already
+ * part of star expansion, InvalidObjectAddress is returned.
+ */
+static ObjectAddress
+ATExecDropUnexpanded(Relation rel, const char *colName, LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ Form_pg_attribute attTup;
+ AttrNumber attnum;
+ Relation attr_rel;
+ ObjectAddress address;
+
+ /*
+ * lookup the attribute
+ */
+ attr_rel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+ attTup = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = attTup->attnum;
+
+ /* Prevent them from altering a system attribute */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ /* If rel is partition, shouldn't drop UNEXPANDED if parent has the same */
+ if (rel->rd_rel->relispartition)
+ {
+ Oid parentId = get_partition_parent(RelationGetRelid(rel), false);
+ Relation parent = table_open(parentId, AccessShareLock);
+ TupleDesc tupDesc = RelationGetDescr(parent);
+ AttrNumber parent_attnum;
+
+ parent_attnum = get_attnum(parentId, colName);
+ if (TupleDescAttr(tupDesc, parent_attnum - 1)->attisunexpanded)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("column \"%s\" is marked UNEXPANDED in parent table",
+ colName)));
+ table_close(parent, AccessShareLock);
+ }
+
+ /*
+ * Okay, actually perform the catalog change ... if needed
+ */
+ if (attTup->attisunexpanded)
+ {
+ attTup->attisunexpanded = false;
+
+ CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
+
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ }
+ else
+ address = InvalidObjectAddress;
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ table_close(attr_rel, RowExclusiveLock);
+
+ return address;
+}
+
+/*
+ * Return the address of the modified column. If the column was already
+ * UNEXPANDED, InvalidObjectAddress is returned.
+ */
+static ObjectAddress
+ATExecSetUnexpanded(Relation rel, const char *colName, LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ AttrNumber attnum;
+ Relation attr_rel;
+ ObjectAddress address;
+
+ if (rel->rd_rel->reloftype)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot set UNEXPANDED attribute on a column of a typed table")));
+
+ attr_rel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ /*
+ * lookup the attribute
+ */
+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
+
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum;
+
+ /* Prevent them from altering a system attribute */
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ /*
+ * Okay, actually perform the catalog change ... if needed
+ */
+ if (!((Form_pg_attribute) GETSTRUCT(tuple))->attisunexpanded)
+ {
+ ((Form_pg_attribute) GETSTRUCT(tuple))->attisunexpanded = true;
+
+ /* Now we can update the catalog */
+ CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
+
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ }
+ else
+ address = InvalidObjectAddress;
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ table_close(attr_rel, RowExclusiveLock);
+
+ return address;
+}
+
/*
* Return the address of the modified column. If the column was already
* nullable, InvalidObjectAddress is returned.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d0eb80e69c..b22c9f60c1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -720,7 +720,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
- UESCAPE UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
+ UESCAPE UNBOUNDED UNCOMMITTED UNENCRYPTED UNEXPANDED UNION UNIQUE UNKNOWN
UNLISTEN UNLOGGED UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
@@ -2239,6 +2239,22 @@ alter_table_cmd:
n->name = $3;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP UNEXPANDED */
+ | ALTER opt_column ColId DROP UNEXPANDED
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DropUnexpanded;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET UNEXPANDED */
+ | ALTER opt_column ColId SET UNEXPANDED
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetUnexpanded;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP EXPRESSION */
| ALTER opt_column ColId DROP EXPRESSION
{
@@ -3493,6 +3509,7 @@ columnDef: ColId Typename opt_column_compression create_generic_options ColQualL
n->fdwoptions = $4;
SplitColQualList($5, &n->constraints, &n->collClause,
yyscanner);
+ n->is_unexpanded = false;
n->location = @1;
$$ = (Node *)n;
}
@@ -3513,6 +3530,7 @@ columnOptions: ColId ColQualList
n->collOid = InvalidOid;
SplitColQualList($2, &n->constraints, &n->collClause,
yyscanner);
+ n->is_unexpanded = false;
n->location = @1;
$$ = (Node *)n;
}
@@ -3777,6 +3795,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | UNEXPANDED { $$ = CREATE_TABLE_LIKE_UNEXPANDED; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -15828,6 +15847,7 @@ unreserved_keyword:
| UNBOUNDED
| UNCOMMITTED
| UNENCRYPTED
+ | UNEXPANDED
| UNKNOWN
| UNLISTEN
| UNLOGGED
@@ -16429,6 +16449,7 @@ bare_label_keyword:
| UNBOUNDED
| UNCOMMITTED
| UNENCRYPTED
+ | UNEXPANDED
| UNIQUE
| UNKNOWN
| UNLISTEN
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index c5c3f26ecf..40c99fb3aa 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1256,6 +1256,12 @@ buildNSItemFromTupleDesc(RangeTblEntry *rte, Index rtindex, TupleDesc tupdesc)
nscolumns[varattno].p_varcollid = attr->attcollation;
nscolumns[varattno].p_varnosyn = rtindex;
nscolumns[varattno].p_varattnosyn = varattno + 1;
+ /*
+ * For an hidden column, the entry will not
+ * be included in star expansion.
+ */
+ if (attr->attisunexpanded)
+ nscolumns[varattno].p_dontexpand = true;
}
/* ... and build the nsitem */
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 9ce3a0de96..912a846bf5 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1028,7 +1028,7 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos)
attr = TupleDescAttr(pstate->p_target_relation->rd_att, i);
- if (attr->attisdropped)
+ if (attr->attisdropped || attr->attisunexpanded)
continue;
col = makeNode(ResTarget);
@@ -1303,7 +1303,6 @@ ExpandAllTables(ParseState *pstate, int location)
Assert(!nsitem->p_lateral_only);
/* Remember we found a p_cols_visible item */
found_table = true;
-
target = list_concat(target,
expandNSItemAttrs(pstate,
nsitem,
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 313d7b6ff0..6a06749119 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1084,6 +1084,12 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
else
def->storage = 0;
+ /* Likewise, copy hidden if requested */
+ if (table_like_clause->options & CREATE_TABLE_LIKE_UNEXPANDED)
+ def->is_unexpanded = attribute->attisunexpanded;
+ else
+ def->is_unexpanded = false;
+
/* Likewise, copy compression if requested */
if ((table_like_clause->options & CREATE_TABLE_LIKE_COMPRESSION) != 0
&& CompressionMethodIsValid(attribute->attcompression))
@@ -1482,6 +1488,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
n->collOid = attr->attcollation;
n->constraints = NIL;
n->location = -1;
+ n->is_unexpanded = false;
cxt->columns = lappend(cxt->columns, n);
}
DecrTupleDescRefCount(tupdesc);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 9fa9e671a1..c28847b300 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3543,6 +3543,7 @@ RelationBuildLocalRelation(const char *relname,
datt->attidentity = satt->attidentity;
datt->attgenerated = satt->attgenerated;
datt->attnotnull = satt->attnotnull;
+ datt->attisunexpanded = satt->attisunexpanded;
has_not_null |= satt->attnotnull;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d1842edde0..42de05d0a3 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8541,6 +8541,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attoptions;
int i_attcollation;
int i_attcompression;
+ int i_attisunexpanded;
int i_attfdwoptions;
int i_attmissingval;
int i_atthasdef;
@@ -8617,6 +8618,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
appendPQExpBuffer(q,
"'' AS attcompression,\n");
+ if (fout->remoteVersion >= 150000)
+ appendPQExpBuffer(q,
+ "a.attisunexpanded,\n");
+ else
+ appendPQExpBuffer(q,
+ "'f' AS attisunexpanded,\n");
+
if (fout->remoteVersion >= 90200)
appendPQExpBufferStr(q,
"pg_catalog.array_to_string(ARRAY("
@@ -8680,6 +8688,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attoptions = (char **) pg_malloc(ntups * sizeof(char *));
tbinfo->attcollation = (Oid *) pg_malloc(ntups * sizeof(Oid));
tbinfo->attcompression = (char *) pg_malloc(ntups * sizeof(char));
+ tbinfo->attisunexpanded = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->attfdwoptions = (char **) pg_malloc(ntups * sizeof(char *));
tbinfo->attmissingval = (char **) pg_malloc(ntups * sizeof(char *));
tbinfo->notnull = (bool *) pg_malloc(ntups * sizeof(bool));
@@ -8704,6 +8713,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attoptions = PQfnumber(res, "attoptions");
i_attcollation = PQfnumber(res, "attcollation");
i_attcompression = PQfnumber(res, "attcompression");
+ i_attisunexpanded = PQfnumber(res, "attisunexpanded");
i_attfdwoptions = PQfnumber(res, "attfdwoptions");
i_attmissingval = PQfnumber(res, "attmissingval");
i_atthasdef = PQfnumber(res, "atthasdef");
@@ -8730,6 +8740,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, j, i_attoptions));
tbinfo->attcollation[j] = atooid(PQgetvalue(res, j, i_attcollation));
tbinfo->attcompression[j] = *(PQgetvalue(res, j, i_attcompression));
+ tbinfo->attisunexpanded[j] = (PQgetvalue(res, j, i_attisunexpanded)[0] == 't');
tbinfo->attfdwoptions[j] = pg_strdup(PQgetvalue(res, j, i_attfdwoptions));
tbinfo->attmissingval[j] = pg_strdup(PQgetvalue(res, j, i_attmissingval));
tbinfo->attrdefs[j] = NULL; /* fix below */
@@ -16308,6 +16319,16 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
foreign, qualrelname,
fmtId(tbinfo->attnames[j]));
+ /*
+ * Dump per-column unexpanded information. We only issue an ALTER
+ * TABLE statement if the attisunexpanded entry for this column is
+ * true (i.e. it's not the default value)
+ */
+ if (tbinfo->attisunexpanded[j])
+ appendPQExpBuffer(q, "ALTER %sTABLE ONLY %s ALTER COLUMN %s SET UNEXPANDED;\n",
+ foreign, qualrelname,
+ fmtId(tbinfo->attnames[j]));
+
/*
* Dump per-column statistics information. We only issue an ALTER
* TABLE statement if the attstattarget entry for this column is
@@ -16401,6 +16422,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
qualrelname,
fmtId(tbinfo->attnames[j]),
tbinfo->attfdwoptions[j]);
+
} /* end loop over columns */
if (ftoptions)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f9af14b793..27617b881e 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -322,6 +322,7 @@ typedef struct _tableInfo
char **attoptions; /* per-attribute options */
Oid *attcollation; /* per-attribute collation selection */
char *attcompression; /* per-attribute compression method */
+ bool *attisunexpanded; /* hidden column */
char **attfdwoptions; /* per-attribute fdw options */
char **attmissingval; /* per attribute missing value */
bool *notnull; /* NOT NULL constraints on attributes */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 006661412e..e9d06dfbbc 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1628,6 +1628,7 @@ describeOneTableDetails(const char *schemaname,
atttype_col = -1,
attrdef_col = -1,
attnotnull_col = -1,
+ attisunexpanded_col = -1,
attcoll_col = -1,
attidentity_col = -1,
attgenerated_col = -1,
@@ -2091,6 +2092,14 @@ describeOneTableDetails(const char *schemaname,
appendPQExpBufferStr(&buf, ",\n pg_catalog.col_description(a.attrelid, a.attnum)");
attdescr_col = cols++;
}
+
+ /* column visibility in a SELECT *, if relevant to relkind */
+ if (tableinfo.relkind == RELKIND_RELATION ||
+ tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ appendPQExpBufferStr(&buf, ",\n a.attisunexpanded AS attisunexpanded");
+ attisunexpanded_col = cols++;
+ }
}
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
@@ -2183,6 +2192,8 @@ describeOneTableDetails(const char *schemaname,
headers[cols++] = gettext_noop("Nullable");
headers[cols++] = gettext_noop("Default");
}
+ if (attisunexpanded_col >= 0)
+ headers[cols++] = gettext_noop("Expanded");
if (isindexkey_col >= 0)
headers[cols++] = gettext_noop("Key?");
if (indexdef_col >= 0)
@@ -2215,7 +2226,7 @@ describeOneTableDetails(const char *schemaname,
/* Type */
printTableAddCell(&cont, PQgetvalue(res, i, atttype_col), false, false);
- /* Collation, Nullable, Default */
+ /* Collation, Nullable, Unexpanded, Default */
if (show_column_details)
{
char *identity;
@@ -2228,7 +2239,6 @@ describeOneTableDetails(const char *schemaname,
printTableAddCell(&cont,
strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "",
false, false);
-
identity = PQgetvalue(res, i, attidentity_col);
generated = PQgetvalue(res, i, attgenerated_col);
@@ -2258,6 +2268,12 @@ describeOneTableDetails(const char *schemaname,
if (fdwopts_col >= 0)
printTableAddCell(&cont, PQgetvalue(res, i, fdwopts_col), false, false);
+ /* Column unexpanded in SELECT *, if relevant */
+ if (attisunexpanded_col >= 0)
+ printTableAddCell(&cont,
+ strcmp(PQgetvalue(res, i, attisunexpanded_col), "t") == 0 ? "unexpanded" : "",
+ false, false);
+
/* Storage mode, if relevant */
if (attstorage_col >= 0)
{
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8e01f54500..4ab83d7500 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2172,7 +2172,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> SET */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
- COMPLETE_WITH("(", "COMPRESSION", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE");
+ COMPLETE_WITH("(", "COMPRESSION", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE", "UNEXPANDED");
/* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "(") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
@@ -2190,7 +2190,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> DROP */
else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "DROP") ||
Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "DROP"))
- COMPLETE_WITH("DEFAULT", "EXPRESSION", "IDENTITY", "NOT NULL");
+ COMPLETE_WITH("DEFAULT", "EXPRESSION", "IDENTITY", "NOT NULL", "UNEXPANDED");
else if (Matches("ALTER", "TABLE", MatchAny, "CLUSTER"))
COMPLETE_WITH("ON");
else if (Matches("ALTER", "TABLE", MatchAny, "CLUSTER", "ON"))
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 5c1ec9313e..0ca6fb0978 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -167,8 +167,15 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(75,
/* Number of times inherited from direct parent relation(s) */
int32 attinhcount BKI_DEFAULT(0);
+ /*
+ * This flag specifies whether this column is expendable in
+ * a SELECT *, an INSERT without column list, or not. It is true when
+ * a column is defined with the HIDDEN attribute, false otherwise.
+ */
+ bool attisunexpanded BKI_DEFAULT(f);
+
/* attribute's collation, if any */
- Oid attcollation BKI_LOOKUP_OPT(pg_collation);
+ Oid attcollation BKI_LOOKUP_OPT(pg_collation);
#ifdef CATALOG_VARLEN /* variable-length fields start here */
/* NOTE: The following fields are not present in tuple descriptors. */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 49123e28a4..1f582006d7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -686,6 +686,8 @@ typedef struct ColumnDef
List *constraints; /* other constraints on column */
List *fdwoptions; /* per-column FDW options */
int location; /* parse location, or -1 if none/unknown */
+ bool is_unexpanded; /* column is not included in star expansion?
+ (unexpanded column) */
} ColumnDef;
/*
@@ -710,6 +712,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_UNEXPANDED = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -1947,7 +1950,9 @@ typedef enum AlterTableType
AT_AddIdentity, /* ADD IDENTITY */
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
- AT_ReAddStatistics /* internal to commands/tablecmds.c */
+ AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_DropUnexpanded, /* alter column drop unexpanded */
+ AT_SetUnexpanded /* alter column set unexpanded */
} AlterTableType;
typedef struct ReplicaIdentityStmt
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f836acf876..230af7aa11 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -434,6 +434,7 @@ PG_KEYWORD("uescape", UESCAPE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unbounded", UNBOUNDED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("uncommitted", UNCOMMITTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unencrypted", UNENCRYPTED, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("unexpanded", UNEXPANDED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("union", UNION, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("unique", UNIQUE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unknown", UNKNOWN, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 24d1c7cd28..d58b6257bb 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2247,11 +2247,11 @@ where oid = 'test_storage'::regclass;
create index test_storage_idx on test_storage (b, a);
alter table test_storage alter column a set storage external;
\d+ test_storage
- Table "public.test_storage"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | external | |
- b | integer | | | 0 | plain | |
+ Table "public.test_storage"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | external | |
+ b | integer | | | 0 | | plain | |
Indexes:
"test_storage_idx" btree (b, a)
@@ -4235,10 +4235,10 @@ DROP TABLE part_rpd;
-- works fine
ALTER TABLE range_parted2 DETACH PARTITION part_rp CONCURRENTLY;
\d+ range_parted2
- Partitioned table "public.range_parted2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
+ Partitioned table "public.range_parted2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
Partition key: RANGE (a)
Number of partitions: 0
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 4c997e2602..668d50f642 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -6,20 +6,20 @@ CREATE TABLE cmdata(f1 text COMPRESSION pglz);
CREATE INDEX idx ON cmdata(f1);
INSERT INTO cmdata VALUES(repeat('1234567890', 1000));
\d+ cmdata
- Table "public.cmdata"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | pglz | |
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | text | | | | | extended | pglz | |
Indexes:
"idx" btree (f1)
CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4);
INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004));
\d+ cmdata1
- Table "public.cmdata1"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | lz4 | |
+ Table "public.cmdata1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | text | | | | | extended | lz4 | |
-- verify stored compression method in the data
SELECT pg_column_compression(f1) FROM cmdata;
@@ -50,10 +50,10 @@ SELECT SUBSTR(f1, 2000, 50) FROM cmdata1;
-- copy with table creation
SELECT * INTO cmmove1 FROM cmdata;
\d+ cmmove1
- Table "public.cmmove1"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | | |
+ Table "public.cmmove1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | text | | | | | extended | | |
SELECT pg_column_compression(f1) FROM cmmove1;
pg_column_compression
@@ -75,10 +75,10 @@ SELECT pg_column_compression(f1) FROM cmmove3;
-- test LIKE INCLUDING COMPRESSION
CREATE TABLE cmdata2 (LIKE cmdata1 INCLUDING COMPRESSION);
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | lz4 | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | text | | | | | extended | lz4 | |
DROP TABLE cmdata2;
-- try setting compression for incompressible data type
@@ -136,41 +136,41 @@ DROP TABLE cmdata2;
--test column type update varlena/non-varlena
CREATE TABLE cmdata2 (f1 int);
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
- f1 | integer | | | | plain | | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+-------------+--------------+-------------
+ f1 | integer | | | | | plain | | |
ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar;
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | character varying | | | | extended | | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | character varying | | | | | extended | | |
ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE int USING f1::integer;
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
- f1 | integer | | | | plain | | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+-------------+--------------+-------------
+ f1 | integer | | | | | plain | | |
--changing column storage should not impact the compression method
--but the data should not be compressed
ALTER TABLE cmdata2 ALTER COLUMN f1 TYPE varchar;
ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION pglz;
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | character varying | | | | extended | pglz | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | character varying | | | | | extended | pglz | |
ALTER TABLE cmdata2 ALTER COLUMN f1 SET STORAGE plain;
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+-------------------+-----------+----------+---------+---------+-------------+--------------+-------------
- f1 | character varying | | | | plain | pglz | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+---------+-------------+--------------+-------------
+ f1 | character varying | | | | | plain | pglz | |
INSERT INTO cmdata2 VALUES (repeat('123456789', 800));
SELECT pg_column_compression(f1) FROM cmdata2;
@@ -245,10 +245,10 @@ SET default_toast_compression = 'pglz';
ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
INSERT INTO cmdata VALUES (repeat('123456789', 4004));
\d+ cmdata
- Table "public.cmdata"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+----------+-------------+--------------+-------------
- f1 | text | | | | extended | lz4 | |
+ Table "public.cmdata"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+-------------+--------------+-------------
+ f1 | text | | | | | extended | lz4 | |
Indexes:
"idx" btree (f1)
@@ -261,10 +261,10 @@ SELECT pg_column_compression(f1) FROM cmdata;
ALTER TABLE cmdata2 ALTER COLUMN f1 SET COMPRESSION default;
\d+ cmdata2
- Table "public.cmdata2"
- Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+-------------------+-----------+----------+---------+---------+-------------+--------------+-------------
- f1 | character varying | | | | plain | | |
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Compression | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+---------+-------------+--------------+-------------
+ f1 | character varying | | | | | plain | | |
-- test alter compression method for materialized views
ALTER MATERIALIZED VIEW compressmv ALTER COLUMN x SET COMPRESSION lz4;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 5f3685e9ef..517a7abbfa 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -513,10 +513,10 @@ begin
end $$ language plpgsql immutable;
alter table check_con_tbl add check (check_con_function(check_con_tbl.*));
\d+ check_con_tbl
- Table "public.check_con_tbl"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- f1 | integer | | | | plain | |
+ Table "public.check_con_tbl"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ f1 | integer | | | | | plain | |
Check constraints:
"check_con_tbl_check" CHECK (check_con_function(check_con_tbl.*))
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index a958b84979..70fd168543 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -498,11 +498,11 @@ Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")
Number of partitions: 0
\d+ partitioned2
- Partitioned table "public.partitioned2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | integer | | | | plain | |
- b | text | | | | extended | |
+ Partitioned table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
Partition key: RANGE (((a + 1)), substr(b, 1, 5))
Number of partitions: 0
@@ -511,11 +511,11 @@ ERROR: no partition of relation "partitioned2" found for row
DETAIL: Partition key of the failing row contains ((a + 1), substr(b, 1, 5)) = (2, hello).
CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc');
\d+ part2_1
- Table "public.part2_1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | integer | | | | plain | |
- b | text | | | | extended | |
+ Table "public.part2_1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | text | | | | | extended | |
Partition of: partitioned2 FOR VALUES FROM ('-1', 'aaaaa') TO (100, 'ccccc')
Partition constraint: (((a + 1) IS NOT NULL) AND (substr(b, 1, 5) IS NOT NULL) AND (((a + 1) > '-1'::integer) OR (((a + 1) = '-1'::integer) AND (substr(b, 1, 5) >= 'aaaaa'::text))) AND (((a + 1) < 100) OR (((a + 1) = 100) AND (substr(b, 1, 5) < 'ccccc'::text))))
@@ -552,11 +552,11 @@ select * from partitioned where partitioned = '(1,2)'::partitioned;
(2 rows)
\d+ partitioned1
- Table "public.partitioned1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
+ Table "public.partitioned1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | integer | | | | | plain | |
Partition of: partitioned FOR VALUES IN ('(1,2)')
Partition constraint: (((partitioned1.*)::partitioned IS DISTINCT FROM NULL) AND ((partitioned1.*)::partitioned = '(1,2)'::partitioned))
@@ -609,10 +609,10 @@ CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2);
CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1));
CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null);
\d+ list_parted
- Partitioned table "public.list_parted"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
+ Partitioned table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
Partition key: LIST (a)
Partitions: part_null FOR VALUES IN (NULL),
part_p1 FOR VALUES IN (1),
@@ -1057,21 +1057,21 @@ create table test_part_coll_cast2 partition of test_part_coll_posix for values f
drop table test_part_coll_posix;
-- Partition bound in describe output
\d+ part_b
- Table "public.part_b"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | not null | 1 | plain | |
+ Table "public.part_b"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | not null | 1 | | plain | |
Partition of: parted FOR VALUES IN ('b')
Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
-- Both partition bound and partition key in describe output
\d+ part_c
- Partitioned table "public.part_c"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | not null | 0 | plain | |
+ Partitioned table "public.part_c"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | not null | 0 | | plain | |
Partition of: parted FOR VALUES IN ('c')
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
Partition key: RANGE (b)
@@ -1079,11 +1079,11 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
-- a level-2 partition's constraint will include the parent's expressions
\d+ part_c_1_10
- Table "public.part_c_1_10"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | not null | 0 | plain | |
+ Table "public.part_c_1_10"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | not null | 0 | | plain | |
Partition of: part_c FOR VALUES FROM (1) TO (10)
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
@@ -1112,46 +1112,46 @@ Number of partitions: 4 (Use \d+ to list them.)
CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE);
\d+ unbounded_range_part
- Table "public.unbounded_range_part"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- c | integer | | | | plain | |
+ Table "public.unbounded_range_part"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ c | integer | | | | | plain | |
Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL))
DROP TABLE unbounded_range_part;
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE);
\d+ range_parted4_1
- Table "public.range_parted4_1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- c | integer | | | | plain | |
+ Table "public.range_parted4_1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ c | integer | | | | | plain | |
Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1))
CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE);
\d+ range_parted4_2
- Table "public.range_parted4_2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- c | integer | | | | plain | |
+ Table "public.range_parted4_2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ c | integer | | | | | plain | |
Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))
CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE);
\d+ range_parted4_3
- Table "public.range_parted4_3"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
- c | integer | | | | plain | |
+ Table "public.range_parted4_3"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ c | integer | | | | | plain | |
Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE)
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))
@@ -1183,11 +1183,11 @@ SELECT obj_description('parted_col_comment'::regclass);
(1 row)
\d+ parted_col_comment
- Partitioned table "public.parted_col_comment"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+---------------
- a | integer | | | | plain | | Partition key
- b | text | | | | extended | |
+ Partitioned table "public.parted_col_comment"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+---------------
+ a | integer | | | | | plain | | Partition key
+ b | text | | | | | extended | |
Partition key: LIST (a)
Number of partitions: 0
@@ -1196,10 +1196,10 @@ DROP TABLE parted_col_comment;
CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a);
CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}');
\d+ arrlp12
- Table "public.arrlp12"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+-----------+-----------+----------+---------+----------+--------------+-------------
- a | integer[] | | | | extended | |
+ Table "public.arrlp12"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+-----------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | integer[] | | | | | extended | |
Partition of: arrlp FOR VALUES IN ('{1}', '{2}')
Partition constraint: ((a IS NOT NULL) AND ((a = '{1}'::integer[]) OR (a = '{2}'::integer[])))
@@ -1209,10 +1209,10 @@ create table boolspart (a bool) partition by list (a);
create table boolspart_t partition of boolspart for values in (true);
create table boolspart_f partition of boolspart for values in (false);
\d+ boolspart
- Partitioned table "public.boolspart"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | boolean | | | | plain | |
+ Partitioned table "public.boolspart"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | boolean | | | | | plain | |
Partition key: LIST (a)
Partitions: boolspart_f FOR VALUES IN (false),
boolspart_t FOR VALUES IN (true)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 0ed94f1d2f..99f44dc342 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -327,32 +327,32 @@ CREATE TABLE ctlt4 (a text, c text);
ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL;
CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE);
\d+ ctlt12_storage
- Table "public.ctlt12_storage"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | |
- b | text | | | | extended | |
- c | text | | | | external | |
+ Table "public.ctlt12_storage"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | not null | | | main | |
+ b | text | | | | | extended | |
+ c | text | | | | | external | |
CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS);
\d+ ctlt12_comments
- Table "public.ctlt12_comments"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | extended | | A
- b | text | | | | extended | | B
- c | text | | | | extended | | C
+ Table "public.ctlt12_comments"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | not null | | | extended | | A
+ b | text | | | | | extended | | B
+ c | text | | | | | extended | | C
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "b" with inherited definition
NOTICE: merging constraint "ctlt1_a_check" with inherited definition
\d+ ctlt1_inh
- Table "public.ctlt1_inh"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | | A
- b | text | | | | extended | | B
+ Table "public.ctlt1_inh"
+ Column | Type | Collation | Nullable | Default | Expanded | 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
@@ -366,12 +366,12 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con
CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
NOTICE: merging multiple inherited definitions of column "a"
\d+ ctlt13_inh
- Table "public.ctlt13_inh"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | |
- b | text | | | | extended | |
- c | text | | | | external | |
+ Table "public.ctlt13_inh"
+ Column | Type | Collation | Nullable | Default | Expanded | 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)
@@ -382,12 +382,12 @@ Inherits: ctlt1,
CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
\d+ ctlt13_like
- Table "public.ctlt13_like"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | | A3
- b | text | | | | extended | |
- c | text | | | | external | | C
+ Table "public.ctlt13_like"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | not null | | | main | | A3
+ b | text | | | | | extended | |
+ c | text | | | | | external | | C
Indexes:
"ctlt13_like_expr_idx" btree ((a || c))
Check constraints:
@@ -404,11 +404,11 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con
CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
\d+ ctlt_all
- Table "public.ctlt_all"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | | A
- b | text | | | | extended | | B
+ Table "public.ctlt_all"
+ Column | Type | Collation | Nullable | Default | Expanded | 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)
@@ -444,11 +444,11 @@ DETAIL: MAIN versus EXTENDED
-- Check that LIKE isn't confused by a system catalog of the same name
CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL);
\d+ public.pg_attrdef
- Table "public.pg_attrdef"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | | A
- b | text | | | | extended | | B
+ Table "public.pg_attrdef"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | not null | | | main | | A
+ b | text | | | | | extended | | B
Indexes:
"pg_attrdef_pkey" PRIMARY KEY, btree (a)
"pg_attrdef_b_idx" btree (b)
@@ -466,11 +466,11 @@ CREATE SCHEMA ctl_schema;
SET LOCAL search_path = ctl_schema, public;
CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL);
\d+ ctlt1
- Table "ctl_schema.ctlt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------+-----------+----------+---------+----------+--------------+-------------
- a | text | | not null | | main | | A
- b | text | | | | extended | | B
+ Table "ctl_schema.ctlt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | not null | | | main | | A
+ b | text | | | | | extended | | B
Indexes:
"ctlt1_pkey" PRIMARY KEY, btree (a)
"ctlt1_b_idx" btree (b)
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index a04bd00ac6..e7ff0c0255 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -266,10 +266,10 @@ explain (verbose, costs off)
create rule silly as on delete to dcomptable do instead
update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
\d+ dcomptable
- Table "public.dcomptable"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+-----------+-----------+----------+---------+----------+--------------+-------------
- d1 | dcomptype | | | | extended | |
+ Table "public.dcomptable"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+-----------+-----------+----------+---------+----------+----------+--------------+-------------
+ d1 | dcomptype | | | | | extended | |
Indexes:
"dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1)
Rules:
@@ -403,10 +403,10 @@ create rule silly as on delete to dcomptable do instead
update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1
where d1[1].i > 0;
\d+ dcomptable
- Table "public.dcomptable"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------------+-----------+----------+---------+----------+--------------+-------------
- d1 | dcomptypea | | | | extended | |
+ Table "public.dcomptable"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+------------+-----------+----------+---------+----------+----------+--------------+-------------
+ d1 | dcomptypea | | | | | extended | |
Indexes:
"dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1)
Rules:
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index a6a68d1fa2..bcfad26c62 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1392,12 +1392,12 @@ CREATE TABLE fd_pt1 (
CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1)
SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Child tables: ft2
\d+ ft2
@@ -1413,12 +1413,12 @@ Inherits: fd_pt1
DROP FOREIGN TABLE ft2;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
CREATE FOREIGN TABLE ft2 (
c1 integer NOT NULL,
@@ -1437,12 +1437,12 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value')
ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Child tables: ft2
\d+ ft2
@@ -1480,12 +1480,12 @@ Child tables: ct3,
ft3
\d+ ct3
- Table "public.ct3"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.ct3"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Inherits: ft2
\d+ ft3
@@ -1505,17 +1505,17 @@ ALTER TABLE fd_pt1 ADD COLUMN c6 integer;
ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL;
ALTER TABLE fd_pt1 ADD COLUMN c8 integer;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
- c4 | integer | | | | plain | |
- c5 | integer | | | 0 | plain | |
- c6 | integer | | | | plain | |
- c7 | integer | | not null | | plain | |
- c8 | integer | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
+ c4 | integer | | | | | plain | |
+ c5 | integer | | | 0 | | plain | |
+ c6 | integer | | | | | plain | |
+ c7 | integer | | not null | | | plain | |
+ c8 | integer | | | | | plain | |
Child tables: ft2
\d+ ft2
@@ -1537,17 +1537,17 @@ Child tables: ct3,
ft3
\d+ ct3
- Table "public.ct3"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
- c4 | integer | | | | plain | |
- c5 | integer | | | 0 | plain | |
- c6 | integer | | | | plain | |
- c7 | integer | | not null | | plain | |
- c8 | integer | | | | plain | |
+ Table "public.ct3"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
+ c4 | integer | | | | | plain | |
+ c5 | integer | | | 0 | | plain | |
+ c6 | integer | | | | | plain | |
+ c7 | integer | | not null | | | plain | |
+ c8 | integer | | | | | plain | |
Inherits: ft2
\d+ ft3
@@ -1579,17 +1579,17 @@ ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100);
ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1;
ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | 10000 |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
- c4 | integer | | | 0 | plain | |
- c5 | integer | | | | plain | |
- c6 | integer | | not null | | plain | |
- c7 | integer | | | | plain | |
- c8 | text | | | | external | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | 10000 |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
+ c4 | integer | | | 0 | | plain | |
+ c5 | integer | | | | | plain | |
+ c6 | integer | | not null | | | plain | |
+ c7 | integer | | | | | plain | |
+ c8 | text | | | | | external | |
Child tables: ft2
\d+ ft2
@@ -1617,12 +1617,12 @@ ALTER TABLE fd_pt1 DROP COLUMN c6;
ALTER TABLE fd_pt1 DROP COLUMN c7;
ALTER TABLE fd_pt1 DROP COLUMN c8;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | 10000 |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | 10000 |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Child tables: ft2
\d+ ft2
@@ -1654,12 +1654,12 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
-- child does not inherit NO INHERIT constraints
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | 10000 |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | 10000 |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
@@ -1701,12 +1701,12 @@ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> '');
ALTER FOREIGN TABLE ft2 INHERIT fd_pt1;
-- child does not inherit NO INHERIT constraints
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | 10000 |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | 10000 |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Check constraints:
"fd_pt1chk1" CHECK (c1 > 0) NO INHERIT
"fd_pt1chk2" CHECK (c2 <> ''::text)
@@ -1732,12 +1732,12 @@ ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE;
INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date);
ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | 10000 |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | 10000 |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID
Child tables: ft2
@@ -1759,12 +1759,12 @@ Inherits: fd_pt1
-- VALIDATE CONSTRAINT need do nothing on foreign tables
ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | 10000 |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | 10000 |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Check constraints:
"fd_pt1chk3" CHECK (c2 <> ''::text)
Child tables: ft2
@@ -1790,12 +1790,12 @@ ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3;
-- changes name of a constraint recursively
ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check;
\d+ fd_pt1
- Table "public.fd_pt1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- f1 | integer | | not null | | plain | 10000 |
- f2 | text | | | | extended | |
- f3 | date | | | | plain | |
+ Table "public.fd_pt1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ f1 | integer | | not null | | | plain | 10000 |
+ f2 | text | | | | | extended | |
+ f3 | date | | | | | plain | |
Check constraints:
"f2_check" CHECK (f2 <> ''::text)
Child tables: ft2
@@ -1854,12 +1854,12 @@ CREATE TABLE fd_pt2 (
CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1)
SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
\d+ fd_pt2
- Partitioned table "public.fd_pt2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Partitioned table "public.fd_pt2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Partition key: LIST (c1)
Partitions: fd_pt2_1 FOR VALUES IN (1)
@@ -1899,12 +1899,12 @@ ERROR: table "fd_pt2_1" contains column "c4" not found in parent "fd_pt2"
DETAIL: The new partition may contain only the columns present in parent.
DROP FOREIGN TABLE fd_pt2_1;
\d+ fd_pt2
- Partitioned table "public.fd_pt2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Partitioned table "public.fd_pt2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Partition key: LIST (c1)
Number of partitions: 0
@@ -1926,12 +1926,12 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value')
-- no attach partition validation occurs for foreign tables
ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
\d+ fd_pt2
- Partitioned table "public.fd_pt2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Partitioned table "public.fd_pt2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Partition key: LIST (c1)
Partitions: fd_pt2_1 FOR VALUES IN (1)
@@ -1954,12 +1954,12 @@ ERROR: cannot add column to a partition
ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL;
ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
\d+ fd_pt2
- Partitioned table "public.fd_pt2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | | | extended | |
- c3 | date | | | | plain | |
+ Partitioned table "public.fd_pt2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
Partition key: LIST (c1)
Partitions: fd_pt2_1 FOR VALUES IN (1)
@@ -1984,12 +1984,12 @@ ERROR: column "c1" is marked NOT NULL in parent table
ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1;
ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL;
\d+ fd_pt2
- Partitioned table "public.fd_pt2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | not null | | extended | |
- c3 | date | | | | plain | |
+ Partitioned table "public.fd_pt2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | not null | | | extended | |
+ c3 | date | | | | | plain | |
Partition key: LIST (c1)
Number of partitions: 0
@@ -2012,12 +2012,12 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1);
ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1;
ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0);
\d+ fd_pt2
- Partitioned table "public.fd_pt2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- c1 | integer | | not null | | plain | |
- c2 | text | | not null | | extended | |
- c3 | date | | | | plain | |
+ Partitioned table "public.fd_pt2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | not null | | | extended | |
+ c3 | date | | | | | plain | |
Partition key: LIST (c1)
Check constraints:
"fd_pt2chk1" CHECK (c1 > 0)
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 99811570b7..bcb246456a 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -498,14 +498,14 @@ TABLE itest8;
(2 rows)
\d+ itest8
- Table "public.itest8"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+----------------------------------+---------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | integer | | not null | generated always as identity | plain | |
- f3 | integer | | not null | generated by default as identity | plain | |
- f4 | bigint | | not null | generated always as identity | plain | |
- f5 | bigint | | | | plain | |
+ Table "public.itest8"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+----------------------------------+----------+---------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | integer | | not null | generated always as identity | | plain | |
+ f3 | integer | | not null | generated by default as identity | | plain | |
+ f4 | bigint | | not null | generated always as identity | | plain | |
+ f5 | bigint | | | | | plain | |
\d itest8_f2_seq
Sequence "public.itest8_f2_seq"
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 2d49e765de..6c7ba8612b 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1050,13 +1050,13 @@ ALTER TABLE inhts RENAME aa TO aaa; -- to be failed
ERROR: cannot rename inherited column "aa"
ALTER TABLE inhts RENAME d TO dd;
\d+ inhts
- Table "public.inhts"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- aa | integer | | | | plain | |
- b | integer | | | | plain | |
- c | integer | | | | plain | |
- dd | integer | | | | plain | |
+ Table "public.inhts"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ aa | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ c | integer | | | | | plain | |
+ dd | integer | | | | | plain | |
Inherits: inht1,
inhs1
@@ -1069,14 +1069,14 @@ NOTICE: merging multiple inherited definitions of column "aa"
NOTICE: merging multiple inherited definitions of column "b"
ALTER TABLE inht1 RENAME aa TO aaa;
\d+ inht4
- Table "public.inht4"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- aaa | integer | | | | plain | |
- b | integer | | | | plain | |
- x | integer | | | | plain | |
- y | integer | | | | plain | |
- z | integer | | | | plain | |
+ Table "public.inht4"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ aaa | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ x | integer | | | | | plain | |
+ y | integer | | | | | plain | |
+ z | integer | | | | | plain | |
Inherits: inht2,
inht3
@@ -1086,14 +1086,14 @@ ALTER TABLE inht1 RENAME aaa TO aaaa;
ALTER TABLE inht1 RENAME b TO bb; -- to be failed
ERROR: cannot rename inherited column "b"
\d+ inhts
- Table "public.inhts"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- aaaa | integer | | | | plain | |
- b | integer | | | | plain | |
- x | integer | | | | plain | |
- c | integer | | | | plain | |
- d | integer | | | | plain | |
+ Table "public.inhts"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ aaaa | integer | | | | | plain | |
+ b | integer | | | | | plain | |
+ x | integer | | | | | plain | |
+ c | integer | | | | | plain | |
+ d | integer | | | | | plain | |
Inherits: inht2,
inhs1
@@ -1133,33 +1133,33 @@ drop cascades to table inht4
CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
\d+ test_constraints
- Table "public.test_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+-------------------+-----------+----------+---------+----------+--------------+-------------
- id | integer | | | | plain | |
- val1 | character varying | | | | extended | |
- val2 | integer | | | | plain | |
+ Table "public.test_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+----------+--------------+-------------
+ id | integer | | | | | plain | |
+ val1 | character varying | | | | | extended | |
+ val2 | integer | | | | | plain | |
Indexes:
"test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
Child tables: test_constraints_inh
ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
\d+ test_constraints
- Table "public.test_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+-------------------+-----------+----------+---------+----------+--------------+-------------
- id | integer | | | | plain | |
- val1 | character varying | | | | extended | |
- val2 | integer | | | | plain | |
+ Table "public.test_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+----------+--------------+-------------
+ id | integer | | | | | plain | |
+ val1 | character varying | | | | | extended | |
+ val2 | integer | | | | | plain | |
Child tables: test_constraints_inh
\d+ test_constraints_inh
- Table "public.test_constraints_inh"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+-------------------+-----------+----------+---------+----------+--------------+-------------
- id | integer | | | | plain | |
- val1 | character varying | | | | extended | |
- val2 | integer | | | | plain | |
+ Table "public.test_constraints_inh"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+----------+--------------+-------------
+ id | integer | | | | | plain | |
+ val1 | character varying | | | | | extended | |
+ val2 | integer | | | | | plain | |
Inherits: test_constraints
DROP TABLE test_constraints_inh;
@@ -1170,27 +1170,27 @@ CREATE TABLE test_ex_constraints (
);
CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
\d+ test_ex_constraints
- Table "public.test_ex_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+--------+-----------+----------+---------+---------+--------------+-------------
- c | circle | | | | plain | |
+ Table "public.test_ex_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+----------+---------+--------------+-------------
+ c | circle | | | | | plain | |
Indexes:
"test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
Child tables: test_ex_constraints_inh
ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
\d+ test_ex_constraints
- Table "public.test_ex_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+--------+-----------+----------+---------+---------+--------------+-------------
- c | circle | | | | plain | |
+ Table "public.test_ex_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+----------+---------+--------------+-------------
+ c | circle | | | | | plain | |
Child tables: test_ex_constraints_inh
\d+ test_ex_constraints_inh
- Table "public.test_ex_constraints_inh"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+--------+-----------+----------+---------+---------+--------------+-------------
- c | circle | | | | plain | |
+ Table "public.test_ex_constraints_inh"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+----------+---------+--------------+-------------
+ c | circle | | | | | plain | |
Inherits: test_ex_constraints
DROP TABLE test_ex_constraints_inh;
@@ -1200,37 +1200,37 @@ CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
\d+ test_primary_constraints
- Table "public.test_primary_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- id | integer | | not null | | plain | |
+ Table "public.test_primary_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ id | integer | | not null | | | plain | |
Indexes:
"test_primary_constraints_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
\d+ test_foreign_constraints
- Table "public.test_foreign_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- id1 | integer | | | | plain | |
+ Table "public.test_foreign_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ id1 | integer | | | | | plain | |
Foreign-key constraints:
"test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
Child tables: test_foreign_constraints_inh
ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
\d+ test_foreign_constraints
- Table "public.test_foreign_constraints"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- id1 | integer | | | | plain | |
+ Table "public.test_foreign_constraints"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ id1 | integer | | | | | plain | |
Child tables: test_foreign_constraints_inh
\d+ test_foreign_constraints_inh
- Table "public.test_foreign_constraints_inh"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- id1 | integer | | | | plain | |
+ Table "public.test_foreign_constraints_inh"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ id1 | integer | | | | | plain | |
Inherits: test_foreign_constraints
DROP TABLE test_foreign_constraints_inh;
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 5063a3dc22..717b6c6467 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -163,11 +163,11 @@ create rule irule3 as on insert to inserttest2 do also
insert into inserttest (f4[1].if1, f4[1].if2[2])
select new.f1, new.f2;
\d+ inserttest2
- Table "public.inserttest2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+--------+-----------+----------+---------+----------+--------------+-------------
- f1 | bigint | | | | plain | |
- f2 | text | | | | extended | |
+ Table "public.inserttest2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+----------+----------+--------------+-------------
+ f1 | bigint | | | | | plain | |
+ f2 | text | | | | | extended | |
Rules:
irule1 AS
ON INSERT TO inserttest2 DO INSERT INTO inserttest (f3.if2[1], f3.if2[2])
@@ -469,11 +469,11 @@ from hash_parted order by part;
-- test \d+ output on a table which has both partitioned and unpartitioned
-- partitions
\d+ list_parted
- Partitioned table "public.list_parted"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Partitioned table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition key: LIST (lower(a))
Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'),
part_cc_dd FOR VALUES IN ('cc', 'dd'),
@@ -491,10 +491,10 @@ drop table hash_parted;
create table list_parted (a int) partition by list (a);
create table part_default partition of list_parted default;
\d+ part_default
- Table "public.part_default"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
+ Table "public.part_default"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
Partition of: list_parted DEFAULT
No partition constraint
@@ -874,11 +874,11 @@ create table mcrparted6_common_ge_10 partition of mcrparted for values from ('co
create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue);
create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue);
\d+ mcrparted
- Partitioned table "public.mcrparted"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Partitioned table "public.mcrparted"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition key: RANGE (a, b)
Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE),
mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE),
@@ -890,74 +890,74 @@ Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVAL
mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE)
\d+ mcrparted1_lt_b
- Table "public.mcrparted1_lt_b"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted1_lt_b"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text))
\d+ mcrparted2_b
- Table "public.mcrparted2_b"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted2_b"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'b'::text) AND (a < 'c'::text))
\d+ mcrparted3_c_to_common
- Table "public.mcrparted3_c_to_common"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted3_c_to_common"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'c'::text) AND (a < 'common'::text))
\d+ mcrparted4_common_lt_0
- Table "public.mcrparted4_common_lt_0"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted4_common_lt_0"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('common', MINVALUE) TO ('common', 0)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b < 0))
\d+ mcrparted5_common_0_to_10
- Table "public.mcrparted5_common_0_to_10"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted5_common_0_to_10"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('common', 0) TO ('common', 10)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 0) AND (b < 10))
\d+ mcrparted6_common_ge_10
- Table "public.mcrparted6_common_ge_10"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted6_common_ge_10"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 10))
\d+ mcrparted7_gt_common_lt_d
- Table "public.mcrparted7_gt_common_lt_d"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted7_gt_common_lt_d"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::text) AND (a < 'd'::text))
\d+ mcrparted8_ge_d
- Table "public.mcrparted8_ge_d"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | integer | | | | plain | |
+ Table "public.mcrparted8_ge_d"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | integer | | | | | plain | |
Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text))
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 930ce8597a..6678d2fce0 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2813,34 +2813,34 @@ CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap;
CREATE VIEW view_heap_psql AS SELECT f1 from tbl_heap_psql;
CREATE MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from tbl_heap_psql;
\d+ tbl_heap_psql
- Table "tableam_display.tbl_heap_psql"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+----------------+-----------+----------+---------+----------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | character(100) | | | | extended | |
+ Table "tableam_display.tbl_heap_psql"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+----------------+-----------+----------+---------+----------+----------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | character(100) | | | | | extended | |
\d+ tbl_heap
- Table "tableam_display.tbl_heap"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+----------------+-----------+----------+---------+----------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | character(100) | | | | extended | |
+ Table "tableam_display.tbl_heap"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+----------------+-----------+----------+---------+----------+----------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | character(100) | | | | | extended | |
\set HIDE_TABLEAM off
\d+ tbl_heap_psql
- Table "tableam_display.tbl_heap_psql"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+----------------+-----------+----------+---------+----------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | character(100) | | | | extended | |
+ Table "tableam_display.tbl_heap_psql"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+----------------+-----------+----------+---------+----------+----------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | character(100) | | | | | extended | |
Access method: heap_psql
\d+ tbl_heap
- Table "tableam_display.tbl_heap"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+----------------+-----------+----------+---------+----------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | character(100) | | | | extended | |
+ Table "tableam_display.tbl_heap"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+----------------+-----------+----------+---------+----------+----------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | character(100) | | | | | extended | |
Access method: heap
-- AM is displayed for tables, indexes and materialized views.
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 0f4fe4db8f..ef59172881 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -148,11 +148,11 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
(1 row)
\d+ testpub_tbl2
- Table "public.testpub_tbl2"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | |
- data | text | | | | extended | |
+ Table "public.testpub_tbl2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | | plain | |
+ data | text | | | | | extended | |
Indexes:
"testpub_tbl2_pkey" PRIMARY KEY, btree (id)
Publications:
@@ -285,22 +285,22 @@ ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk;
ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1;
\d+ pub_test.testpub_nopk
- Table "pub_test.testpub_nopk"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- foo | integer | | | | plain | |
- bar | integer | | | | plain | |
+ Table "pub_test.testpub_nopk"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ foo | integer | | | | | plain | |
+ bar | integer | | | | | plain | |
Publications:
"testpib_ins_trunct"
"testpub_default"
"testpub_fortbl"
\d+ testpub_tbl1
- Table "public.testpub_tbl1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
- data | text | | | | extended | |
+ Table "public.testpub_tbl1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | | plain | |
+ data | text | | | | | extended | |
Indexes:
"testpub_tbl1_pkey" PRIMARY KEY, btree (id)
Publications:
@@ -322,11 +322,11 @@ ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk
ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk;
ERROR: relation "testpub_nopk" is not part of the publication
\d+ testpub_tbl1
- Table "public.testpub_tbl1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
- data | text | | | | extended | |
+ Table "public.testpub_tbl1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | | plain | |
+ data | text | | | | | extended | |
Indexes:
"testpub_tbl1_pkey" PRIMARY KEY, btree (id)
Publications:
diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out
index 79002197a7..f1decc955a 100644
--- a/src/test/regress/expected/replica_identity.out
+++ b/src/test/regress/expected/replica_identity.out
@@ -153,13 +153,13 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
(1 row)
\d+ test_replica_identity
- Table "public.test_replica_identity"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------------------------------------------------+----------+--------------+-------------
- id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | |
- keya | text | | not null | | extended | |
- keyb | text | | not null | | extended | |
- nonkey | text | | | | extended | |
+ Table "public.test_replica_identity"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------------------------------------------------+----------+----------+--------------+-------------
+ id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | | plain | |
+ keya | text | | not null | | | extended | |
+ keyb | text | | not null | | | extended | |
+ nonkey | text | | | | | extended | |
Indexes:
"test_replica_identity_pkey" PRIMARY KEY, btree (id)
"test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 89397e41f0..39646c2b98 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -938,14 +938,14 @@ CREATE POLICY pp1 ON part_document AS PERMISSIVE
CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
USING (cid < 55);
\d+ part_document
- Partitioned table "regress_rls_schema.part_document"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------+-----------+----------+---------+----------+--------------+-------------
- did | integer | | | | plain | |
- cid | integer | | | | plain | |
- dlevel | integer | | not null | | plain | |
- dauthor | name | | | | plain | |
- dtitle | text | | | | extended | |
+ Partitioned table "regress_rls_schema.part_document"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+---------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ did | integer | | | | | plain | |
+ cid | integer | | | | | plain | |
+ dlevel | integer | | not null | | | plain | |
+ dauthor | name | | | | | plain | |
+ dtitle | text | | | | | extended | |
Partition key: RANGE (cid)
Policies:
POLICY "pp1"
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2fa00a3c29..a6c4f04609 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3167,11 +3167,11 @@ select * from rules_log;
create rule r3 as on delete to rules_src do notify rules_src_deletion;
\d+ rules_src
- Table "public.rules_src"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | integer | | | | plain | |
+ Table "public.rules_src"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | integer | | | | | plain | |
Rules:
r1 AS
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
@@ -3187,11 +3187,11 @@ Rules:
create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
\d+ rules_src
- Table "public.rules_src"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | integer | | | | plain | |
+ Table "public.rules_src"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | integer | | | | | plain | |
Rules:
r1 AS
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
@@ -3218,11 +3218,11 @@ create rule rr as on update to rule_t1 do instead UPDATE rule_dest trgt
SET (f2[1], f1, tag) = (SELECT new.f2, new.f1, 'updated'::varchar)
WHERE trgt.f1 = new.f1 RETURNING new.*;
\d+ rule_t1
- Table "public.rule_t1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- f1 | integer | | | | plain | |
- f2 | integer | | | | plain | |
+ Table "public.rule_t1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ f1 | integer | | | | | plain | |
+ f2 | integer | | | | | plain | |
Rules:
rr AS
ON UPDATE TO rule_t1 DO INSTEAD UPDATE rule_dest trgt SET (f2[1], f1, tag) = ( SELECT new.f2,
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index c60ba45aba..5d14d46318 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -151,11 +151,11 @@ SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
\d+ ab1
- Table "public.ab1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
- b | integer | | | | plain | |
+ Table "public.ab1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
+ b | integer | | | | | plain | |
Statistics objects:
"public.ab1_a_b_stats" ON a, b FROM ab1
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 5d124cf96f..eb19bb50b1 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3476,10 +3476,10 @@ create trigger parenttrig after insert on child
for each row execute procedure f();
alter trigger parenttrig on parent rename to anothertrig;
\d+ child
- Table "public.child"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
+ Table "public.child"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
Triggers:
parenttrig AFTER INSERT ON child FOR EACH ROW EXECUTE FUNCTION f()
Inherits: parent
diff --git a/src/test/regress/expected/unexpanded.out b/src/test/regress/expected/unexpanded.out
new file mode 100644
index 0000000000..78c0f3aaf2
--- /dev/null
+++ b/src/test/regress/expected/unexpanded.out
@@ -0,0 +1,580 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attisunexpanded FROM pg_attribute WHERE attisunexpanded;
+ attrelid | attname | attisunexpanded
+----------+---------+-----------------
+(0 rows)
+
+CREATE TABLE htest0 (a int PRIMARY KEY, b text NOT NULL);
+ALTER TABLE htest0 ALTER COLUMN b SET UNEXPANDED;
+INSERT INTO htest0 (a, b) VALUES (1, 'htest0 one');
+INSERT INTO htest0 (a, b) VALUES (2, 'htest0 two');
+-- we allow that all columns of a relation be unexpanded
+ALTER TABLE htest0 ALTER COLUMN a SET UNEXPANDED;
+SELECT * FROM htest0;
+--
+(2 rows)
+
+ALTER TABLE htest0 ALTER COLUMN a DROP UNEXPANDED;
+CREATE TABLE htest1 (a bigserial PRIMARY KEY, b text);
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+-- Insert without named column must not include the unexpanded column
+INSERT INTO htest1 VALUES ('htest1 one');
+INSERT INTO htest1 VALUES ('htest1 two');
+-- INSERT + SELECT * should handle the unexpanded column
+CREATE TABLE htest1_1 (a bigserial PRIMARY KEY, b text);
+ALTER TABLE htest1_1 ALTER COLUMN a SET UNEXPANDED;
+INSERT INTO htest1_1 VALUES ('htest1 one');
+WITH cte AS (
+ DELETE FROM htest1_1 RETURNING *
+) SELECT * FROM cte;
+ b
+------------
+ htest1 one
+(1 row)
+
+INSERT INTO htest1_1 SELECT * FROM htest0;
+SELECT a, b FROM htest1_1;
+ a | b
+---+---
+ 2 | 1
+ 3 | 2
+(2 rows)
+
+DROP TABLE htest1_1;
+SELECT attrelid::regclass, attname, attisunexpanded FROM pg_attribute WHERE attisunexpanded;
+ attrelid | attname | attisunexpanded
+----------+---------+-----------------
+ htest0 | b | t
+ htest1 | a | t
+(2 rows)
+
+\d+ htest1
+ Table "public.htest1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+-----------------------------------+------------+----------+--------------+-------------
+ a | bigint | | not null | nextval('htest1_a_seq'::regclass) | unexpanded | plain | |
+ b | text | | | | | extended | |
+Indexes:
+ "htest1_pkey" PRIMARY KEY, btree (a)
+
+-- DROP/SET unexpanded attribute
+ALTER TABLE htest0 ALTER COLUMN b DROP UNEXPANDED;
+\d+ htest0
+ Table "public.htest0"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | integer | | not null | | | plain | |
+ b | text | | not null | | | extended | |
+Indexes:
+ "htest0_pkey" PRIMARY KEY, btree (a)
+
+ALTER TABLE htest0 ALTER COLUMN b SET UNEXPANDED;
+-- Hidden column are not expandable and must not be returned
+SELECT * FROM htest0; -- return only column a
+ a
+---
+ 1
+ 2
+(2 rows)
+
+SELECT t.* FROM htest1 t; -- return only column b
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+-- the whole-row syntax do not take care of the unexpanded attribute
+SELECT t FROM htest1 t; -- return column a and b
+ t
+------------------
+ (1,"htest1 one")
+ (2,"htest1 two")
+(2 rows)
+
+-- CTEs based on SELECT * only have visible column returned
+WITH foo AS (SELECT * FROM htest1) SELECT * FROM foo; -- Only column b is returned here
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+-- Use of wildcard or whole-row in a function do not apply the unexpanded attribute
+SELECT row_to_json(t.*) FROM htest0 t;
+ row_to_json
+--------------------------
+ {"a":1,"b":"htest0 one"}
+ {"a":2,"b":"htest0 two"}
+(2 rows)
+
+SELECT row_to_json(t) FROM htest0 t;
+ row_to_json
+--------------------------
+ {"a":1,"b":"htest0 one"}
+ {"a":2,"b":"htest0 two"}
+(2 rows)
+
+-- inheritance, the unexpanded attribute is inherited
+CREATE TABLE htest1_1 () INHERITS (htest1);
+SELECT * FROM htest1_1;
+ b
+---
+(0 rows)
+
+\d htest1_1
+ Table "public.htest1_1"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-----------------------------------
+ a | bigint | | not null | nextval('htest1_a_seq'::regclass)
+ b | text | | |
+Inherits: htest1
+
+INSERT INTO htest1_1 VALUES ('htest1 three');
+SELECT * FROM htest1_1;
+ b
+--------------
+ htest1 three
+(1 row)
+
+SELECT * FROM htest1;
+ b
+--------------
+ htest1 one
+ htest1 two
+ htest1 three
+(3 rows)
+
+-- unexpanded column must be explicitely named to be returned
+SELECT a,b FROM htest1_1;
+ a | b
+---+--------------
+ 3 | htest1 three
+(1 row)
+
+SELECT a,b FROM htest1;
+ a | b
+---+--------------
+ 1 | htest1 one
+ 2 | htest1 two
+ 3 | htest1 three
+(3 rows)
+
+DROP TABLE htest1_1;
+-- Default CREATE TABLE ... LIKE includes unexpanded columns, and they are not uinexpanded in the new table.
+CREATE TABLE htest_like1 (LIKE htest1);
+\d+ htest_like1
+ Table "public.htest_like1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | bigint | | not null | | | plain | |
+ b | text | | | | | extended | |
+
+-- CREATE TABLE ... LIKE includes unexpanded columns, and they are unexpanded if requested
+CREATE TABLE htest_like2 (LIKE htest1 INCLUDING UNEXPANDED);
+\d+ htest_like2
+ Table "public.htest_like2"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+------------+----------+--------------+-------------
+ a | bigint | | not null | | unexpanded | plain | |
+ b | text | | | | | extended | |
+
+CREATE TABLE htest_like3 (LIKE htest1 INCLUDING ALL);
+\d+ htest_like3
+ Table "public.htest_like3"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+--------+-----------+----------+-----------------------------------+------------+----------+--------------+-------------
+ a | bigint | | not null | nextval('htest1_a_seq'::regclass) | unexpanded | plain | |
+ b | text | | | | | extended | |
+Indexes:
+ "htest_like3_pkey" PRIMARY KEY, btree (a)
+
+DROP TABLE htest_like1, htest_like2, htest_like3;
+-- Insert without named column with and a not null unexpanded column must have a default value
+INSERT INTO htest0 VALUES (3); -- error
+ERROR: null value in column "b" of relation "htest0" violates not-null constraint
+DETAIL: Failing row contains (3, null).
+ALTER TABLE htest0 ALTER COLUMN b SET DEFAULT 'unknown';
+INSERT INTO htest0 VALUES (3);
+-- Same with COPY
+COPY htest0 TO stdout;
+1
+2
+3
+COPY htest0 (a, b) TO stdout;
+1 htest0 one
+2 htest0 two
+3 unknown
+COPY htest0 FROM stdin;
+SELECT a,b FROM htest0;
+ a | b
+---+------------
+ 1 | htest0 one
+ 2 | htest0 two
+ 3 | unknown
+ 4 | unknown
+ 5 | unknown
+(5 rows)
+
+-- same but with drop/add the column between unexpanded columns (virtual columns can be made unexpanded)
+CREATE TABLE htest2 (a serial, b int, c int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE htest2 ALTER COLUMN a SET UNEXPANDED;
+ALTER TABLE htest2 ALTER COLUMN c SET UNEXPANDED;
+SELECT * FROM htest2;
+ b
+---
+(0 rows)
+
+INSERT INTO htest2 VALUES (2);
+SELECT a,b,c FROM htest2;
+ a | b | c
+---+---+---
+ 1 | 2 | 2
+(1 row)
+
+ALTER TABLE htest2 DROP COLUMN b;
+ALTER TABLE htest2 ADD COLUMN b int;
+INSERT INTO htest2 VALUES (4);
+SELECT a,b,c FROM htest2;
+ a | b | c
+---+---+---
+ 1 | | 2
+ 2 | 4 | 4
+(2 rows)
+
+DROP TABLE htest2 CASCADE;
+-- a table can NOT have all columns unexpanded
+CREATE TABLE htest3 (a serial, b int);
+ALTER TABLE htest3
+ ALTER COLUMN a SET UNEXPANDED,
+ ALTER COLUMN b SET UNEXPANDED; -- error
+DROP TABLE htest3;
+-- inheritance with an additional single unexpanded column is possible
+CREATE TABLE htest3 (a serial, b int);
+ALTER TABLE htest3 ALTER COLUMN a SET UNEXPANDED;
+SELECT * FROM htest3;
+ b
+---
+(0 rows)
+
+CREATE TABLE htest3_1 (c int) INHERITS (htest3);
+ALTER TABLE htest3_1 ALTER COLUMN c SET UNEXPANDED;
+SELECT * FROM htest3_1;
+ b
+---
+(0 rows)
+
+\d+ htest3_1
+ Table "public.htest3_1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+-----------------------------------+------------+---------+--------------+-------------
+ a | integer | | not null | nextval('htest3_a_seq'::regclass) | unexpanded | plain | |
+ b | integer | | | | | plain | |
+ c | integer | | | | unexpanded | plain | |
+Inherits: htest3
+
+DROP TABLE htest3_1, htest3;
+-- Ordering do not include the unexpanded column
+CREATE TABLE t1 (col1 integer NOT NULL, col2 integer);
+ALTER TABLE t1 ALTER COLUMN col1 SET UNEXPANDED;
+INSERT INTO t1 (col1, col2) VALUES (1, 6), (3, 4);
+SELECT * FROM t1 ORDER BY 1 DESC;
+ col2
+------
+ 6
+ 4
+(2 rows)
+
+SELECT col1,col2 FROM t1 ORDER BY 2 DESC;
+ col1 | col2
+------+------
+ 1 | 6
+ 3 | 4
+(2 rows)
+
+-- unless it is called explicitly
+SELECT * FROM t1 ORDER BY col1 DESC;
+ col2
+------
+ 4
+ 6
+(2 rows)
+
+DROP TABLE t1;
+-- A table can be partitioned by an unexpanded column
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+ALTER TABLE measurement ALTER COLUMN logdate SET UNEXPANDED;
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2021-01-01') TO ('2021-03-01');
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2021-03-01') TO ('2021-05-01');
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4);
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6);
+EXPLAIN VERBOSE SELECT * FROM measurement;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Append (cost=0.00..75.50 rows=3700 width=12)
+ -> Seq Scan on public.measurement_y2006m02 measurement_1 (cost=0.00..28.50 rows=1850 width=12)
+ Output: measurement_1.city_id, measurement_1.peaktemp, measurement_1.unitsales
+ -> Seq Scan on public.measurement_y2006m03 measurement_2 (cost=0.00..28.50 rows=1850 width=12)
+ Output: measurement_2.city_id, measurement_2.peaktemp, measurement_2.unitsales
+(5 rows)
+
+SELECT * FROM measurement;
+ city_id | peaktemp | unitsales
+---------+----------+-----------
+ 1 | 34 | 4
+ 1 | 42 | 6
+(2 rows)
+
+SELECT city_id, logdate, peaktemp, unitsales FROM measurement;
+ city_id | logdate | peaktemp | unitsales
+---------+------------+----------+-----------
+ 1 | 02-28-2021 | 34 | 4
+ 1 | 04-12-2021 | 42 | 6
+(2 rows)
+
+DROP TABLE measurement CASCADE;
+-- Same but unitsales is unexpanded instead of the partition key
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+ALTER TABLE measurement ALTER COLUMN unitsales SET UNEXPANDED;
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2021-01-01') TO ('2021-03-01');
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2021-03-01') TO ('2021-05-01');
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4);
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6);
+EXPLAIN VERBOSE SELECT * FROM measurement;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Append (cost=0.00..75.50 rows=3700 width=12)
+ -> Seq Scan on public.measurement_y2006m02 measurement_1 (cost=0.00..28.50 rows=1850 width=12)
+ Output: measurement_1.city_id, measurement_1.logdate, measurement_1.peaktemp
+ -> Seq Scan on public.measurement_y2006m03 measurement_2 (cost=0.00..28.50 rows=1850 width=12)
+ Output: measurement_2.city_id, measurement_2.logdate, measurement_2.peaktemp
+(5 rows)
+
+SELECT * FROM measurement;
+ city_id | logdate | peaktemp
+---------+------------+----------
+ 1 | 02-28-2021 | 34
+ 1 | 04-12-2021 | 42
+(2 rows)
+
+SELECT city_id, logdate, peaktemp, unitsales FROM measurement;
+ city_id | logdate | peaktemp | unitsales
+---------+------------+----------+-----------
+ 1 | 02-28-2021 | 34 | 4
+ 1 | 04-12-2021 | 42 | 6
+(2 rows)
+
+SELECT * FROM measurement_y2006m03;
+ city_id | logdate | peaktemp
+---------+------------+----------
+ 1 | 04-12-2021 | 42
+(1 row)
+
+DROP TABLE measurement CASCADE;
+-- Temporary tables can have invisible columns too.
+CREATE TEMPORARY TABLE htest_tmp (col1 integer NOT NULL, col2 integer);
+ALTER TABLE htest_tmp ALTER COLUMN col1 SET UNEXPANDED;
+INSERT INTO htest_tmp (col1, col2) VALUES (1, 6), (3, 4);
+SELECT * FROM htest_tmp ORDER BY 1 DESC;
+ col2
+------
+ 6
+ 4
+(2 rows)
+
+DROP TABLE htest_tmp;
+-- A table can use a composite type as an unexpanded column
+CREATE TYPE compfoo AS (f1 int, f2 text);
+CREATE TABLE htest4 (
+ a int,
+ b compfoo
+);
+ALTER TABLE htest4 ALTER COLUMN b SET UNEXPANDED;
+SELECT * FROM htest4;
+ a
+---
+(0 rows)
+
+DROP TABLE htest4;
+DROP TYPE compfoo;
+-- Foreign key constraints can be defined on unexpanded columns, or unexpanded columns can be referenced.
+CREATE TABLE t1 (col1 integer UNIQUE, col2 integer);
+ALTER TABLE t1 ALTER COLUMN col1 SET UNEXPANDED;
+CREATE TABLE t2 (col1 integer PRIMARY KEY, col2 integer);
+ALTER TABLE t2 ALTER COLUMN col1 SET UNEXPANDED;
+ALTER TABLE t1 ADD CONSTRAINT fk_t1_col1 FOREIGN KEY (col1) REFERENCES t2(col1);
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_col1 FOREIGN KEY (col1) REFERENCES t1(col1);
+DROP TABLE t1, t2 CASCADE;
+-- CHECK constraints can be defined on invisible columns.
+CREATE TABLE t1 (col1 integer CHECK (col1 > 2), col2 integer NOT NULL);
+ALTER TABLE t1 ALTER COLUMN col1 SET UNEXPANDED;
+INSERT INTO t1 (col1, col2) VALUES (1, 6); -- error
+ERROR: new row for relation "t1" violates check constraint "t1_col1_check"
+DETAIL: Failing row contains (1, 6).
+INSERT INTO t1 (col1, col2) VALUES (3, 6);
+-- An index can reference a unexpanded column
+CREATE INDEX ON t1 (col1);
+ALTER TABLE t1
+ ALTER COLUMN col1 TYPE bigint,
+ ALTER COLUMN col1 DROP UNEXPANDED,
+ ALTER COLUMN col2 SET UNEXPANDED;
+\d+ t1
+ Table "public.t1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+------------+---------+--------------+-------------
+ col1 | bigint | | | | | plain | |
+ col2 | integer | | not null | | unexpanded | plain | |
+Indexes:
+ "t1_col1_idx" btree (col1)
+Check constraints:
+ "t1_col1_check" CHECK (col1 > 2)
+
+DROP TABLE t1;
+-- View must not include the unexpanded column when not explicitly listed
+CREATE VIEW viewt1 AS SELECT * FROM htest1;
+\d viewt1
+ View "public.viewt1"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+ b | text | | |
+
+SELECT * FROM viewt1;
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+-- If the unexpanded attribute on the column is removed the view result must not change
+ALTER TABLE htest1 ALTER COLUMN a DROP UNEXPANDED;
+SELECT * FROM viewt1;
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+DROP VIEW viewt1;
+-- Materialized view must include the unexpanded column when explicitly listed
+-- but the column is not unexpanded in the materialized view.
+CREATE VIEW viewt1 AS SELECT a, b FROM htest1;
+\d viewt1
+ View "public.viewt1"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------
+ a | bigint | | |
+ b | text | | |
+
+SELECT * FROM viewt1;
+ a | b
+---+------------
+ 1 | htest1 one
+ 2 | htest1 two
+(2 rows)
+
+-- Materialized view must not include the unexpanded column when not explicitly listed
+CREATE MATERIALIZED VIEW mviewt1 AS SELECT * FROM htest1;
+\d mviewt1
+ Materialized view "public.mviewt1"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+ b | text | | |
+
+REFRESH MATERIALIZED VIEW mviewt1;
+SELECT * FROM mviewt1;
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+DROP MATERIALIZED VIEW mviewt1;
+-- Materialized view must include the unexpanded column when explicitly listed
+-- but the column is not unexpanded in the materialized view.
+CREATE MATERIALIZED VIEW mviewt1 AS SELECT a, b FROM htest1;
+\d mviewt1
+ Materialized view "public.mviewt1"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------
+ a | bigint | | |
+ b | text | | |
+
+REFRESH MATERIALIZED VIEW mviewt1;
+SELECT * FROM mviewt1;
+ a | b
+---+------------
+ 1 | htest1 one
+ 2 | htest1 two
+(2 rows)
+
+-- typed tables with unexpanded column is not supported
+CREATE TYPE htest_type AS (f1 integer, f2 text, f3 bigint);
+CREATE TABLE htest28 OF htest_type (f1 WITH OPTIONS DEFAULT 3);
+ALTER TABLE htest28 ALTER COLUMN f1 SET UNEXPANDED; -- error
+ERROR: cannot set UNEXPANDED attribute on a column of a typed table
+DROP TYPE htest_type CASCADE;
+NOTICE: drop cascades to table htest28
+-- Prepared statements
+PREPARE q1 AS SELECT * FROM htest1 WHERE a > $1;
+EXECUTE q1(0);
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+ALTER TABLE htest1 ALTER COLUMN a DROP UNEXPANDED;
+EXECUTE q1(0); -- error: cached plan change result type
+ERROR: cached plan must not change result type
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+EXECUTE q1(0);
+ b
+------------
+ htest1 one
+ htest1 two
+(2 rows)
+
+DEALLOCATE q1;
+-- SELECT * INTO and RETURNING * INTO has the same
+-- behavior, the unexpanded column is not returned.
+CREATE OR REPLACE PROCEDURE test_plpgsq_returning (p_a integer)
+AS $$
+DECLARE
+ v_lbl text;
+BEGIN
+ SELECT * INTO v_lbl FROM htest1 WHERE a = p_a;
+ RAISE NOTICE 'SELECT INTO Col b : %', v_lbl;
+
+ DELETE FROM htest1 WHERE a = p_a
+ RETURNING * INTO v_lbl;
+ IF FOUND THEN
+ RAISE NOTICE 'RETURNING INTO Col b : %', v_lbl;
+ ELSE
+ RAISE NOTICE 'Noting found';
+ END IF;
+END
+$$
+LANGUAGE plpgsql;
+CALL test_plpgsq_returning(1);
+NOTICE: SELECT INTO Col b : htest1 one
+NOTICE: RETURNING INTO Col b : htest1 one
+-- Cleanup
+DROP TABLE htest0, htest1 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to view viewt1
+drop cascades to materialized view mviewt1
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index c809f88f54..48c0c3fcb3 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -743,14 +743,14 @@ DROP TRIGGER d15_insert_trig ON part_d_15_20;
:init_range_parted;
create table part_def partition of range_parted default;
\d+ part_def
- Table "public.part_def"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+-------------------+-----------+----------+---------+----------+--------------+-------------
- a | text | | | | extended | |
- b | bigint | | | | plain | |
- c | numeric | | | | main | |
- d | integer | | | | plain | |
- e | character varying | | | | extended | |
+ Table "public.part_def"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+-------------------+-----------+----------+---------+----------+----------+--------------+-------------
+ a | text | | | | | extended | |
+ b | bigint | | | | | plain | |
+ c | numeric | | | | | main | |
+ d | integer | | | | | plain | |
+ e | character varying | | | | | extended | |
Partition of: range_parted DEFAULT
Partition constraint: (NOT ((a IS NOT NULL) AND (b IS NOT NULL) AND (((a = 'a'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'a'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'b'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '20'::bigint) AND (b < '30'::bigint)))))
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index e7629d470e..42a38762ad 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -330,10 +330,10 @@ Indexes:
Number of partitions: 2 (Use \d+ to list them.)
\d+ testschema.part
- Partitioned table "testschema.part"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
+ Partitioned table "testschema.part"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
Partition key: LIST (a)
Indexes:
"part_a_idx" btree (a), tablespace "regress_tblspace"
@@ -350,10 +350,10 @@ Indexes:
"part1_a_idx" btree (a), tablespace "regress_tblspace"
\d+ testschema.part1
- Table "testschema.part1"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+---------+--------------+-------------
- a | integer | | | | plain | |
+ Table "testschema.part1"
+ Column | Type | Collation | Nullable | Default | Expanded | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+---------+--------------+-------------
+ a | integer | | | | | plain | |
Partition of: testschema.part FOR VALUES IN (1)
Partition constraint: ((a IS NOT NULL) AND (a = 1))
Indexes:
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7be89178f0..e1ef99df15 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -58,7 +58,7 @@ test: create_index create_index_spgist create_view index_including index_includi
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse
+test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse unexpanded
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/sql/unexpanded.sql b/src/test/regress/sql/unexpanded.sql
new file mode 100644
index 0000000000..fa736b6072
--- /dev/null
+++ b/src/test/regress/sql/unexpanded.sql
@@ -0,0 +1,278 @@
+-- sanity check of system catalog
+SELECT attrelid, attname, attisunexpanded FROM pg_attribute WHERE attisunexpanded;
+
+
+CREATE TABLE htest0 (a int PRIMARY KEY, b text NOT NULL);
+ALTER TABLE htest0 ALTER COLUMN b SET UNEXPANDED;
+INSERT INTO htest0 (a, b) VALUES (1, 'htest0 one');
+INSERT INTO htest0 (a, b) VALUES (2, 'htest0 two');
+-- we allow that all columns of a relation be unexpanded
+ALTER TABLE htest0 ALTER COLUMN a SET UNEXPANDED;
+SELECT * FROM htest0;
+ALTER TABLE htest0 ALTER COLUMN a DROP UNEXPANDED;
+
+CREATE TABLE htest1 (a bigserial PRIMARY KEY, b text);
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+-- Insert without named column must not include the unexpanded column
+INSERT INTO htest1 VALUES ('htest1 one');
+INSERT INTO htest1 VALUES ('htest1 two');
+-- INSERT + SELECT * should handle the unexpanded column
+CREATE TABLE htest1_1 (a bigserial PRIMARY KEY, b text);
+ALTER TABLE htest1_1 ALTER COLUMN a SET UNEXPANDED;
+INSERT INTO htest1_1 VALUES ('htest1 one');
+WITH cte AS (
+ DELETE FROM htest1_1 RETURNING *
+) SELECT * FROM cte;
+INSERT INTO htest1_1 SELECT * FROM htest0;
+SELECT a, b FROM htest1_1;
+DROP TABLE htest1_1;
+
+SELECT attrelid::regclass, attname, attisunexpanded FROM pg_attribute WHERE attisunexpanded;
+
+\d+ htest1
+
+-- DROP/SET unexpanded attribute
+ALTER TABLE htest0 ALTER COLUMN b DROP UNEXPANDED;
+
+\d+ htest0
+
+ALTER TABLE htest0 ALTER COLUMN b SET UNEXPANDED;
+
+-- Hidden column are not expandable and must not be returned
+SELECT * FROM htest0; -- return only column a
+SELECT t.* FROM htest1 t; -- return only column b
+-- the whole-row syntax do not take care of the unexpanded attribute
+SELECT t FROM htest1 t; -- return column a and b
+
+-- CTEs based on SELECT * only have visible column returned
+WITH foo AS (SELECT * FROM htest1) SELECT * FROM foo; -- Only column b is returned here
+
+-- Use of wildcard or whole-row in a function do not apply the unexpanded attribute
+SELECT row_to_json(t.*) FROM htest0 t;
+SELECT row_to_json(t) FROM htest0 t;
+
+-- inheritance, the unexpanded attribute is inherited
+CREATE TABLE htest1_1 () INHERITS (htest1);
+SELECT * FROM htest1_1;
+\d htest1_1
+INSERT INTO htest1_1 VALUES ('htest1 three');
+SELECT * FROM htest1_1;
+SELECT * FROM htest1;
+
+-- unexpanded column must be explicitely named to be returned
+SELECT a,b FROM htest1_1;
+SELECT a,b FROM htest1;
+DROP TABLE htest1_1;
+
+-- Default CREATE TABLE ... LIKE includes unexpanded columns, and they are not uinexpanded in the new table.
+CREATE TABLE htest_like1 (LIKE htest1);
+\d+ htest_like1
+-- CREATE TABLE ... LIKE includes unexpanded columns, and they are unexpanded if requested
+CREATE TABLE htest_like2 (LIKE htest1 INCLUDING UNEXPANDED);
+\d+ htest_like2
+CREATE TABLE htest_like3 (LIKE htest1 INCLUDING ALL);
+\d+ htest_like3
+DROP TABLE htest_like1, htest_like2, htest_like3;
+
+-- Insert without named column with and a not null unexpanded column must have a default value
+INSERT INTO htest0 VALUES (3); -- error
+ALTER TABLE htest0 ALTER COLUMN b SET DEFAULT 'unknown';
+INSERT INTO htest0 VALUES (3);
+-- Same with COPY
+COPY htest0 TO stdout;
+COPY htest0 (a, b) TO stdout;
+COPY htest0 FROM stdin;
+4
+5
+\.
+SELECT a,b FROM htest0;
+
+-- same but with drop/add the column between unexpanded columns (virtual columns can be made unexpanded)
+CREATE TABLE htest2 (a serial, b int, c int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE htest2 ALTER COLUMN a SET UNEXPANDED;
+ALTER TABLE htest2 ALTER COLUMN c SET UNEXPANDED;
+SELECT * FROM htest2;
+INSERT INTO htest2 VALUES (2);
+SELECT a,b,c FROM htest2;
+ALTER TABLE htest2 DROP COLUMN b;
+ALTER TABLE htest2 ADD COLUMN b int;
+INSERT INTO htest2 VALUES (4);
+SELECT a,b,c FROM htest2;
+DROP TABLE htest2 CASCADE;
+
+-- a table can NOT have all columns unexpanded
+CREATE TABLE htest3 (a serial, b int);
+ALTER TABLE htest3
+ ALTER COLUMN a SET UNEXPANDED,
+ ALTER COLUMN b SET UNEXPANDED; -- error
+DROP TABLE htest3;
+
+-- inheritance with an additional single unexpanded column is possible
+CREATE TABLE htest3 (a serial, b int);
+ALTER TABLE htest3 ALTER COLUMN a SET UNEXPANDED;
+SELECT * FROM htest3;
+CREATE TABLE htest3_1 (c int) INHERITS (htest3);
+ALTER TABLE htest3_1 ALTER COLUMN c SET UNEXPANDED;
+SELECT * FROM htest3_1;
+\d+ htest3_1
+DROP TABLE htest3_1, htest3;
+
+-- Ordering do not include the unexpanded column
+CREATE TABLE t1 (col1 integer NOT NULL, col2 integer);
+ALTER TABLE t1 ALTER COLUMN col1 SET UNEXPANDED;
+INSERT INTO t1 (col1, col2) VALUES (1, 6), (3, 4);
+SELECT * FROM t1 ORDER BY 1 DESC;
+SELECT col1,col2 FROM t1 ORDER BY 2 DESC;
+-- unless it is called explicitly
+SELECT * FROM t1 ORDER BY col1 DESC;
+DROP TABLE t1;
+
+-- A table can be partitioned by an unexpanded column
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+ALTER TABLE measurement ALTER COLUMN logdate SET UNEXPANDED;
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2021-01-01') TO ('2021-03-01');
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2021-03-01') TO ('2021-05-01');
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4);
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6);
+EXPLAIN VERBOSE SELECT * FROM measurement;
+SELECT * FROM measurement;
+SELECT city_id, logdate, peaktemp, unitsales FROM measurement;
+DROP TABLE measurement CASCADE;
+-- Same but unitsales is unexpanded instead of the partition key
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+ALTER TABLE measurement ALTER COLUMN unitsales SET UNEXPANDED;
+CREATE TABLE measurement_y2006m02 PARTITION OF measurement
+ FOR VALUES FROM ('2021-01-01') TO ('2021-03-01');
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2021-03-01') TO ('2021-05-01');
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-02-28', 34, 4);
+INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2021-04-12', 42, 6);
+EXPLAIN VERBOSE SELECT * FROM measurement;
+SELECT * FROM measurement;
+SELECT city_id, logdate, peaktemp, unitsales FROM measurement;
+SELECT * FROM measurement_y2006m03;
+DROP TABLE measurement CASCADE;
+
+-- Temporary tables can have invisible columns too.
+CREATE TEMPORARY TABLE htest_tmp (col1 integer NOT NULL, col2 integer);
+ALTER TABLE htest_tmp ALTER COLUMN col1 SET UNEXPANDED;
+INSERT INTO htest_tmp (col1, col2) VALUES (1, 6), (3, 4);
+SELECT * FROM htest_tmp ORDER BY 1 DESC;
+DROP TABLE htest_tmp;
+
+-- A table can use a composite type as an unexpanded column
+CREATE TYPE compfoo AS (f1 int, f2 text);
+CREATE TABLE htest4 (
+ a int,
+ b compfoo
+);
+ALTER TABLE htest4 ALTER COLUMN b SET UNEXPANDED;
+SELECT * FROM htest4;
+DROP TABLE htest4;
+DROP TYPE compfoo;
+
+-- Foreign key constraints can be defined on unexpanded columns, or unexpanded columns can be referenced.
+CREATE TABLE t1 (col1 integer UNIQUE, col2 integer);
+ALTER TABLE t1 ALTER COLUMN col1 SET UNEXPANDED;
+CREATE TABLE t2 (col1 integer PRIMARY KEY, col2 integer);
+ALTER TABLE t2 ALTER COLUMN col1 SET UNEXPANDED;
+ALTER TABLE t1 ADD CONSTRAINT fk_t1_col1 FOREIGN KEY (col1) REFERENCES t2(col1);
+ALTER TABLE t2 ADD CONSTRAINT fk_t2_col1 FOREIGN KEY (col1) REFERENCES t1(col1);
+DROP TABLE t1, t2 CASCADE;
+
+-- CHECK constraints can be defined on invisible columns.
+CREATE TABLE t1 (col1 integer CHECK (col1 > 2), col2 integer NOT NULL);
+ALTER TABLE t1 ALTER COLUMN col1 SET UNEXPANDED;
+INSERT INTO t1 (col1, col2) VALUES (1, 6); -- error
+INSERT INTO t1 (col1, col2) VALUES (3, 6);
+-- An index can reference a unexpanded column
+CREATE INDEX ON t1 (col1);
+ALTER TABLE t1
+ ALTER COLUMN col1 TYPE bigint,
+ ALTER COLUMN col1 DROP UNEXPANDED,
+ ALTER COLUMN col2 SET UNEXPANDED;
+\d+ t1
+DROP TABLE t1;
+
+-- View must not include the unexpanded column when not explicitly listed
+CREATE VIEW viewt1 AS SELECT * FROM htest1;
+\d viewt1
+SELECT * FROM viewt1;
+-- If the unexpanded attribute on the column is removed the view result must not change
+ALTER TABLE htest1 ALTER COLUMN a DROP UNEXPANDED;
+SELECT * FROM viewt1;
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+DROP VIEW viewt1;
+-- Materialized view must include the unexpanded column when explicitly listed
+-- but the column is not unexpanded in the materialized view.
+CREATE VIEW viewt1 AS SELECT a, b FROM htest1;
+\d viewt1
+SELECT * FROM viewt1;
+
+-- Materialized view must not include the unexpanded column when not explicitly listed
+CREATE MATERIALIZED VIEW mviewt1 AS SELECT * FROM htest1;
+\d mviewt1
+REFRESH MATERIALIZED VIEW mviewt1;
+SELECT * FROM mviewt1;
+DROP MATERIALIZED VIEW mviewt1;
+-- Materialized view must include the unexpanded column when explicitly listed
+-- but the column is not unexpanded in the materialized view.
+CREATE MATERIALIZED VIEW mviewt1 AS SELECT a, b FROM htest1;
+\d mviewt1
+REFRESH MATERIALIZED VIEW mviewt1;
+SELECT * FROM mviewt1;
+
+-- typed tables with unexpanded column is not supported
+CREATE TYPE htest_type AS (f1 integer, f2 text, f3 bigint);
+CREATE TABLE htest28 OF htest_type (f1 WITH OPTIONS DEFAULT 3);
+ALTER TABLE htest28 ALTER COLUMN f1 SET UNEXPANDED; -- error
+DROP TYPE htest_type CASCADE;
+
+-- Prepared statements
+PREPARE q1 AS SELECT * FROM htest1 WHERE a > $1;
+EXECUTE q1(0);
+ALTER TABLE htest1 ALTER COLUMN a DROP UNEXPANDED;
+EXECUTE q1(0); -- error: cached plan change result type
+ALTER TABLE htest1 ALTER COLUMN a SET UNEXPANDED;
+EXECUTE q1(0);
+DEALLOCATE q1;
+
+
+-- SELECT * INTO and RETURNING * INTO has the same
+-- behavior, the unexpanded column is not returned.
+CREATE OR REPLACE PROCEDURE test_plpgsq_returning (p_a integer)
+AS $$
+DECLARE
+ v_lbl text;
+BEGIN
+ SELECT * INTO v_lbl FROM htest1 WHERE a = p_a;
+ RAISE NOTICE 'SELECT INTO Col b : %', v_lbl;
+
+ DELETE FROM htest1 WHERE a = p_a
+ RETURNING * INTO v_lbl;
+ IF FOUND THEN
+ RAISE NOTICE 'RETURNING INTO Col b : %', v_lbl;
+ ELSE
+ RAISE NOTICE 'Noting found';
+ END IF;
+END
+$$
+LANGUAGE plpgsql;
+
+CALL test_plpgsq_returning(1);
+
+-- Cleanup
+DROP TABLE htest0, htest1 CASCADE;
+