public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lakshmi N <[email protected]>
To: [email protected]
To: [email protected]
To: [email protected]
Subject: Show VIRTUAL keyword for virtual generated columns in pg_dump and psql
Date: Mon, 13 Apr 2026 01:22:07 -0700
Message-ID: <CA+3i_M8Rj0yEA5rRbWjobNbcZsB0MExfZbKV-mkWwFeyRvanCw@mail.gmail.com> (raw)
Hi,
Adding Peter and Dean based on the recent commit history for generated
columns.
pg_dump and psql's \d currently display stored and virtual generated
columns asymmetrically:
s_total integer GENERATED ALWAYS AS ((a + b)) STORED
v_total integer GENERATED ALWAYS AS ((a + b))
Since VIRTUAL is default most likely this was omitted but with this
puts the burden on the reader to identify whether it is STORED or
VIRTUAL since both kinds coexist in v19.
The attached patch adds the VIRTUAL keyword to the output of both
pg_dump and psql \d, so the display becomes:
s_total integer GENERATED ALWAYS AS ((a + b)) STORED
v_total integer GENERATED ALWAYS AS ((a + b)) VIRTUAL
The fix is three one-line changes:
- src/bin/pg_dump/pg_dump.c: append " VIRTUAL" instead of bare ")"
- src/bin/psql/describe.c: same for \d display
- src/bin/pg_dump/t/002_pg_dump.pl: update TAP test regex
The parser already accepts the VIRTUAL keyword, so pg_dump output
round-trips correctly (dump -> restore -> \d shows identical schema).
I verified this with a CTAS + dump + restore cycle.
Regards,
Lakshmi
Attachments:
[application/octet-stream] show-virtual-keyword.patch (29.2K, 3-show-virtual-keyword.patch)
download | inline diff:
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1ca03d6b..d36e733a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17553,7 +17553,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s) STORED",
tbinfo->attrdefs[j]->adef_expr);
else if (tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_VIRTUAL)
- appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s)",
+ appendPQExpBuffer(q, " GENERATED ALWAYS AS (%s) VIRTUAL",
tbinfo->attrdefs[j]->adef_expr);
else
appendPQExpBuffer(q, " DEFAULT %s",
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 3bc8e515..2e69ddde 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3852,7 +3852,7 @@ my %tests = (
\QCREATE TABLE dump_test.test_table_generated (\E\n
\s+\Qcol1 integer NOT NULL,\E\n
\s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2)) STORED,\E\n
- \s+\Qcol3 integer GENERATED ALWAYS AS ((col1 * 3))\E\n
+ \s+\Qcol3 integer GENERATED ALWAYS AS ((col1 * 3)) VIRTUAL\E\n
\);
/xms,
like =>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 871bf70d..04028b48 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2264,7 +2264,7 @@ describeOneTableDetails(const char *schemaname,
}
else if (generated[0] == ATTRIBUTE_GENERATED_VIRTUAL)
{
- default_str = psprintf("generated always as (%s)",
+ default_str = psprintf("generated always as (%s) virtual",
PQgetvalue(res, i, attrdef_col));
mustfree = true;
}
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 5720d160..67ac2609 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -116,11 +116,11 @@ DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (a * 3) VIRTUAL);
\d test_like_gen_1
Table "public.test_like_gen_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
b | integer | | | generated always as (a * 2) stored
- c | integer | | | generated always as (a * 3)
+ c | integer | | | generated always as (a * 3) virtual
INSERT INTO test_like_gen_1 (a) VALUES (1);
SELECT * FROM test_like_gen_1;
@@ -148,11 +148,11 @@ SELECT * FROM test_like_gen_2;
CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
\d test_like_gen_3
Table "public.test_like_gen_3"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------------
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
b | integer | | | generated always as (a * 2) stored
- c | integer | | | generated always as (a * 3)
+ c | integer | | | generated always as (a * 3) virtual
INSERT INTO test_like_gen_3 (a) VALUES (1);
SELECT * FROM test_like_gen_3;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 234061fa..790dc99c 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -20,11 +20,11 @@ SELECT table_name, column_name, dependent_column FROM information_schema.column_
(1 row)
\d gtest1
- Table "generated_virtual_tests.gtest1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
Indexes:
"gtest1_pkey" PRIMARY KEY, btree (a)
@@ -313,11 +313,11 @@ SELECT * FROM gtest1_1;
(0 rows)
\d gtest1_1
- Table "generated_virtual_tests.gtest1_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest1_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
Inherits:
gtest1
@@ -360,12 +360,12 @@ DETAIL: Parent column is VIRTUAL, child column is STORED.
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) VIRTUAL) INHERITS (gtest1); -- ok, overrides parent
NOTICE: merging column "b" with inherited definition
\d+ gtestx
- Table "generated_virtual_tests.gtestx"
- Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+------------------------------+---------+--------------+-------------
- a | integer | | not null | | plain | |
- b | integer | | | generated always as (a * 22) | plain | |
- x | integer | | | | plain | |
+ Table "generated_virtual_tests.gtestx"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------------------------------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | generated always as (a * 22) virtual | plain | |
+ x | integer | | | | plain | |
Not-null constraints:
"gtest1_a_not_null" NOT NULL "a" (inherited)
Inherits:
@@ -414,11 +414,11 @@ NOTICE: merging multiple inherited definitions of column "b"
NOTICE: moving and merging column "b" with inherited definition
DETAIL: User-specified column moved to the position of the inherited column.
\d gtest1_y
- Table "generated_virtual_tests.gtest1_y"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest1_y"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | not null |
- b | integer | | | generated always as (x + 1)
+ b | integer | | | generated always as (x + 1) virtual
x | integer | | |
Inherits:
gtest1
@@ -655,11 +655,11 @@ SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file;
(1 row)
\d gtest20
- Table "generated_virtual_tests.gtest20"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest20"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 4)
+ b | integer | | | generated always as (a * 4) virtual
c | integer | | | 11
Indexes:
"gtest20_pkey" PRIMARY KEY, btree (a)
@@ -891,30 +891,30 @@ DROP TABLE gtest_child3;
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
\d gtest_child
- Table "generated_virtual_tests.gtest_child"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2)
+ f3 | bigint | | | generated always as (f2 * 2) virtual
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "generated_virtual_tests.gtest_child2"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 22)
+ f3 | bigint | | | generated always as (f2 * 22) virtual
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "generated_virtual_tests.gtest_child3"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 33)
+ f3 | bigint | | | generated always as (f2 * 33) virtual
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
@@ -959,40 +959,40 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
\d gtest_parent
- Partitioned table "generated_virtual_tests.gtest_parent"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
+ Partitioned table "generated_virtual_tests.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 4)
+ f3 | bigint | | | generated always as (f2 * 4) virtual
Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)
\d gtest_child
- Table "generated_virtual_tests.gtest_child"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 10)
+ f3 | bigint | | | generated always as (f2 * 10) virtual
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "generated_virtual_tests.gtest_child2"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 22)
+ f3 | bigint | | | generated always as (f2 * 22) virtual
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "generated_virtual_tests.gtest_child3"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-------------------------------
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 33)
+ f3 | bigint | | | generated always as (f2 * 33) virtual
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
@@ -1015,40 +1015,40 @@ ALTER TABLE gtest_parent DROP CONSTRAINT cc;
-- alter generation expression of parent and all its children altogether
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_parent
- Partitioned table "generated_virtual_tests.gtest_parent"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
+ Partitioned table "generated_virtual_tests.gtest_parent"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2)
+ f3 | bigint | | | generated always as (f2 * 2) virtual
Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)
\d gtest_child
- Table "generated_virtual_tests.gtest_child"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
+ Table "generated_virtual_tests.gtest_child"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2)
+ f3 | bigint | | | generated always as (f2 * 2) virtual
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
\d gtest_child2
- Table "generated_virtual_tests.gtest_child2"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
+ Table "generated_virtual_tests.gtest_child2"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2)
+ f3 | bigint | | | generated always as (f2 * 2) virtual
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
\d gtest_child3
- Table "generated_virtual_tests.gtest_child3"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+------------------------------
+ Table "generated_virtual_tests.gtest_child3"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+--------------------------------------
f1 | date | | not null |
f2 | bigint | | |
- f3 | bigint | | | generated always as (f2 * 2)
+ f3 | bigint | | | generated always as (f2 * 2) virtual
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
@@ -1115,15 +1115,15 @@ SELECT * FROM gtest25 ORDER BY a;
(2 rows)
\d gtest25
- Table "generated_virtual_tests.gtest25"
- Column | Type | Collation | Nullable | Default
---------+------------------+-----------+----------+-----------------------------------------------
+ Table "generated_virtual_tests.gtest25"
+ Column | Type | Collation | Nullable | Default
+--------+------------------+-----------+----------+-------------------------------------------------------
a | integer | | not null |
- b | integer | | | generated always as (a * 3)
+ b | integer | | | generated always as (a * 3) virtual
c | integer | | | 42
- x | integer | | | generated always as (c * 4)
+ x | integer | | | generated always as (c * 4) virtual
d | double precision | | | 101
- y | double precision | | | generated always as (d * 4::double precision)
+ y | double precision | | | generated always as (d * 4::double precision) virtual
Indexes:
"gtest25_pkey" PRIMARY KEY, btree (a)
@@ -1139,12 +1139,12 @@ ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "a" is used by generated column "x".
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
\d gtest27
- Table "generated_virtual_tests.gtest27"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-------------------------------------
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------------------------------------------
a | integer | | |
b | integer | | |
- x | numeric | | | generated always as (((a + b) * 2))
+ x | numeric | | | generated always as (((a + b) * 2)) virtual
SELECT * FROM gtest27;
a | b | x
@@ -1176,12 +1176,12 @@ ALTER TABLE gtest27
ALTER COLUMN b TYPE bigint,
ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
\d gtest27
- Table "generated_virtual_tests.gtest27"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-----------------------------------
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------------
a | bigint | | |
b | bigint | | |
- x | bigint | | | generated always as ((a + b) * 2)
+ x | bigint | | | generated always as ((a + b) * 2) virtual
-- Ideally you could just do this, but not today (and should x change type?):
ALTER TABLE gtest27
@@ -1190,12 +1190,12 @@ ALTER TABLE gtest27
ERROR: cannot alter type of a column used by a generated column
DETAIL: Column "a" is used by generated column "x".
\d gtest27
- Table "generated_virtual_tests.gtest27"
- Column | Type | Collation | Nullable | Default
---------+--------+-----------+----------+-----------------------------------
+ Table "generated_virtual_tests.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+-------------------------------------------
a | bigint | | |
b | bigint | | |
- x | bigint | | | generated always as ((a + b) * 2)
+ x | bigint | | | generated always as ((a + b) * 2) virtual
SELECT * FROM gtest27;
a | b | x
@@ -1218,11 +1218,11 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "generated_virtual_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
ERROR: column "a" of relation "gtest29" is not a generated column
@@ -1240,11 +1240,11 @@ SELECT * FROM gtest29;
(2 rows)
\d gtest29
- Table "generated_virtual_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 3)
+ b | integer | | | generated always as (a * 3) virtual
ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION; -- not supported
ERROR: ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns
@@ -1262,11 +1262,11 @@ SELECT * FROM gtest29;
(3 rows)
\d gtest29
- Table "generated_virtual_tests.gtest29"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest29"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 3)
+ b | integer | | | generated always as (a * 3) virtual
-- check that dependencies between columns have also been removed
--ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
@@ -1281,19 +1281,19 @@ ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
ERROR: ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns
DETAIL: Column "b" of relation "gtest30" is a virtual generated column.
\d gtest30
- Table "generated_virtual_tests.gtest30"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest30"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "generated_virtual_tests.gtest30_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest30_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
Inherits:
gtest30
@@ -1307,19 +1307,19 @@ CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
\d gtest30
- Table "generated_virtual_tests.gtest30"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest30"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
Number of child tables: 1 (Use \d+ to list them.)
\d gtest30_1
- Table "generated_virtual_tests.gtest30_1"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest30_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
a | integer | | |
- b | integer | | | generated always as (a * 2)
+ b | integer | | | generated always as (a * 2) virtual
Inherits:
gtest30
@@ -1506,19 +1506,19 @@ CREATE TABLE gtest28a (
ALTER TABLE gtest28a DROP COLUMN a;
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
\d gtest28*
- Table "generated_virtual_tests.gtest28a"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest28a"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
b | integer | | |
c | integer | | |
- x | integer | | | generated always as (b * 2)
+ x | integer | | | generated always as (b * 2) virtual
- Table "generated_virtual_tests.gtest28b"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+-----------------------------
+ Table "generated_virtual_tests.gtest28b"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------------
b | integer | | |
c | integer | | |
- x | integer | | | generated always as (b * 2)
+ x | integer | | | generated always as (b * 2) virtual
-- sanity check of system catalog
SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
--
2.43.0
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Show VIRTUAL keyword for virtual generated columns in pg_dump and psql
In-Reply-To: <CA+3i_M8Rj0yEA5rRbWjobNbcZsB0MExfZbKV-mkWwFeyRvanCw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox