0001-Try-to-limit-STORED-vs-VIRTUAL-diff-to-just-the-firs.patch.nocfbot
application/octet-stream
Filename: 0001-Try-to-limit-STORED-vs-VIRTUAL-diff-to-just-the-firs.patch.nocfbot
Type: application/octet-stream
Part: 1
From c39fef5806d04743858782f308c9caba7186c3f6 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sun, 21 Sep 2025 20:15:43 -0700
Subject: [PATCH] Try to limit STORED vs VIRTUAL diff to just the first few
lines
---
.../regress/expected/generated_stored.out | 181 ++++++++---------
.../regress/expected/generated_virtual.out | 183 +++++++++---------
src/test/regress/sql/generated_stored.sql | 181 ++++++++---------
src/test/regress/sql/generated_virtual.sql | 183 +++++++++---------
4 files changed, 370 insertions(+), 358 deletions(-)
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 6c70353cd97..747b898a48e 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1,9 +1,12 @@
-- keep these tests aligned with generated_virtual.sql
+-- We can use psql variables to keep the diff to a minimum.
+\set Generated 'STORED'
+\set Other 'VIRTUAL'
CREATE SCHEMA generated_stored_tests;
GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
SET search_path = generated_stored_tests;
-CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
-CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) :Generated);
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
table_name | column_name | column_default | is_nullable | is_generated | generation_expression
------------+-------------+----------------+-------------+--------------+-----------------------
@@ -29,73 +32,73 @@ Indexes:
"gtest1_pkey" PRIMARY KEY, btree (a)
-- duplicate generated
-CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED GENERATED ALWAYS AS (a * 3) STORED);
+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated GENERATED ALWAYS AS (a * 3) :Generated);
ERROR: multiple generation clauses specified for column "b" of table "gtest_err_1"
LINE 1: ...ARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED GENERATED ...
^
-- references to other generated columns, including self-references
-CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) STORED);
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) :Generated);
ERROR: cannot use generated column "b" in column generation expression
LINE 1: ...2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) STO...
^
DETAIL: A generated column cannot reference another generated column.
-CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STORED);
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated, c int GENERATED ALWAYS AS (b * 3) :Generated);
ERROR: cannot use generated column "b" in column generation expression
LINE 1: ...AYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STO...
^
DETAIL: A generated column cannot reference another generated column.
-- a whole-row var is a self-reference on steroids, so disallow that too
CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STORED);
+ b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) :Generated);
ERROR: cannot use whole-row variable in column generation expression
LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STOR...
^
DETAIL: This would cause the generated column to depend on its own value.
-- invalid reference
-CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STORED);
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) :Generated);
ERROR: column "c" does not exist
LINE 1: ..._3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STO...
^
-- generation expression must be immutable
-CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) STORED);
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) :Generated);
ERROR: generation expression is not immutable
-- ... but be sure that the immutability test is accurate
-CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') STORED);
+CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') :Generated);
DROP TABLE gtest2;
-- cannot have default/identity and generated
-CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) :Generated);
ERROR: both default and generation expression specified for column "b" of table "gtest_err_5a"
LINE 1: ... gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ...
^
-CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) :Generated);
ERROR: both identity and generation expression specified for column "b" of table "gtest_err_5b"
LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ...
^
-- reference to system column not allowed in generated column
-- (except tableoid, which we test below)
-CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED);
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) :Generated);
ERROR: cannot use system column "xmin" in column generation expression
LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37...
^
-- various prohibited constructs
-CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) STORED);
+CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) :Generated);
ERROR: aggregate functions are not allowed in column generation expressions
LINE 1: ...7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) ST...
^
-CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) STORED);
+CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) :Generated);
ERROR: window functions are not allowed in column generation expressions
LINE 1: ...7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number...
^
-CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) STORED);
+CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) :Generated);
ERROR: cannot use subquery in column generation expression
LINE 1: ...7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)...
^
-CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) STORED);
+CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) :Generated);
ERROR: set-returning functions are not allowed in column generation expressions
LINE 1: ...7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_s...
^
-- GENERATED BY DEFAULT not allowed
-CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) STORED);
+CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) :Generated);
ERROR: for a generated column, GENERATED ALWAYS must be specified
LINE 1: ...E gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT...
^
@@ -221,8 +224,8 @@ CREATE TABLE gtestm (
id int PRIMARY KEY,
f1 int,
f2 int,
- f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
- f4 int GENERATED ALWAYS AS (f2 * 2) STORED
+ f3 int GENERATED ALWAYS AS (f1 * 2) :Generated,
+ f4 int GENERATED ALWAYS AS (f2 * 2) :Generated
);
INSERT INTO gtestm VALUES (1, 5, 100);
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
@@ -245,7 +248,7 @@ SELECT * FROM gtestm ORDER BY id;
DROP TABLE gtestm;
CREATE TABLE gtestm (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
@@ -342,12 +345,12 @@ SELECT * FROM gtest1;
-- can't have generated column that is a child of normal column
CREATE TABLE gtest_normal (a int, b int);
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); -- error
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated) INHERITS (gtest_normal); -- error
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "b" with inherited definition
ERROR: child column "b" specifies generation expression
HINT: A child table column cannot be generated unless its parent column is.
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
ERROR: column "b" in child table must not be a generated column
DROP TABLE gtest_normal, gtest_normal_child;
@@ -358,11 +361,11 @@ ERROR: column "b" inherits from generated column but specifies default
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
NOTICE: merging column "b" with inherited definition
ERROR: column "b" inherits from generated column but specifies identity
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) VIRTUAL) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Other) INHERITS (gtest1); -- error
NOTICE: merging column "b" with inherited definition
ERROR: column "b" inherits from generated column of different kind
DETAIL: Parent column is STORED, child column is VIRTUAL.
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Generated) INHERITS (gtest1); -- ok, overrides parent
NOTICE: merging column "b" with inherited definition
\d+ gtestx
Table "generated_stored_tests.gtestx"
@@ -393,9 +396,9 @@ SELECT * FROM gtestx;
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
ERROR: column "b" in child table must be a generated column
-CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
-CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
+CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) :Generated, a int NOT NULL);
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
-- test multiple inheritance mismatches
CREATE TABLE gtesty (x int, b int DEFAULT 55);
@@ -408,12 +411,12 @@ CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
NOTICE: merging multiple inherited definitions of column "b"
ERROR: inherited column "b" has a generation conflict
DROP TABLE gtesty;
-CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED);
+CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) :Generated);
CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
NOTICE: merging multiple inherited definitions of column "b"
ERROR: column "b" inherits conflicting generation expressions
HINT: To resolve the conflict, specify a generation expression explicitly.
-CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) STORED) INHERITS (gtest1, gtesty); -- ok
+CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) :Generated) INHERITS (gtest1, gtesty); -- ok
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.
@@ -429,7 +432,7 @@ Inherits: gtest1,
-- test correct handling of GENERATED column that's only in child
CREATE TABLE gtestp (f1 int);
-CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) STORED) INHERITS(gtestp);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) :Generated) INHERITS(gtestp);
INSERT INTO gtestc values(42);
TABLE gtestc;
f1 | f2
@@ -447,7 +450,7 @@ TABLE gtestc;
DROP TABLE gtestp CASCADE;
NOTICE: drop cascades to table gtestc
-- test stored update
-CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
+CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) :Generated);
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
SELECT * FROM gtest3 ORDER BY a;
a | b
@@ -468,7 +471,7 @@ SELECT * FROM gtest3 ORDER BY a;
|
(4 rows)
-CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED);
+CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) :Generated);
INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
SELECT * FROM gtest3a ORDER BY a;
a | b
@@ -533,7 +536,7 @@ SELECT * FROM gtest3 ORDER BY a;
(4 rows)
-- null values
-CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) :Generated);
INSERT INTO gtest2 VALUES (1);
SELECT * FROM gtest2;
a | b
@@ -542,7 +545,7 @@ SELECT * FROM gtest2;
(1 row)
-- simple column reference for varlena types
-CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED);
+CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) :Generated);
INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
INSERT INTO gtest_varlena (a) VALUES(NULL);
SELECT * FROM gtest_varlena ORDER BY a;
@@ -557,7 +560,7 @@ DROP TABLE gtest_varlena;
CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
- b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) :Generated
);
INSERT INTO gtest4 VALUES (1), (6);
SELECT * FROM gtest4;
@@ -572,11 +575,11 @@ DROP TYPE double_int;
-- using tableoid is allowed
CREATE TABLE gtest_tableoid (
a int PRIMARY KEY,
- b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) STORED
+ b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) :Generated
);
INSERT INTO gtest_tableoid VALUES (1), (2);
ALTER TABLE gtest_tableoid ADD COLUMN
- c regclass GENERATED ALWAYS AS (tableoid) STORED;
+ c regclass GENERATED ALWAYS AS (tableoid) :Generated;
SELECT * FROM gtest_tableoid;
a | b | c
---+---+----------------
@@ -585,7 +588,7 @@ SELECT * FROM gtest_tableoid;
(2 rows)
-- drop column behavior
-CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
ALTER TABLE gtest10 DROP COLUMN b; -- fails
ERROR: cannot drop column b of table gtest10 because other objects depend on it
DETAIL: column c of table gtest10 depends on column b of table gtest10
@@ -600,17 +603,17 @@ NOTICE: drop cascades to column c of table gtest10
Indexes:
"gtest10_pkey" PRIMARY KEY, btree (a)
-CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest10a DROP COLUMN b;
INSERT INTO gtest10a (a) VALUES (1);
-- privileges
CREATE USER regress_user11;
-CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
+CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
INSERT INTO gtest11 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c) ON gtest11 TO regress_user11;
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated);
INSERT INTO gtest12 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
@@ -643,7 +646,7 @@ DROP TABLE gtest11, gtest12;
DROP FUNCTION gf1(int);
DROP USER regress_user11;
-- check constraints
-CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED CHECK (b < 50));
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated CHECK (b < 50));
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check"
@@ -651,7 +654,7 @@ DETAIL: Failing row contains (30, 60).
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated by some row
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
-CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
@@ -660,27 +663,27 @@ ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated b
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
-CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20b (a) VALUES (10);
INSERT INTO gtest20b (a) VALUES (30);
ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
ERROR: check constraint "chk" of relation "gtest20b" is violated by some row
-- check with whole-row reference
-CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
DETAIL: Failing row contains (null, null).
-- not-null constraints
-CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint
DETAIL: Failing row contains (0, null).
-- also check with table constraint syntax
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED, CONSTRAINT cc NOT NULL b);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated, CONSTRAINT cc NOT NULL b);
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21ax" violates not-null constraint
DETAIL: Failing row contains (0, null).
@@ -689,13 +692,13 @@ INSERT INTO gtest21ax (a) VALUES (1); --ok
ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
ERROR: column "b" of relation "gtest21ax" contains null values
DROP TABLE gtest21ax;
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21ax" violates not-null constraint
DETAIL: Failing row contains (0, null).
DROP TABLE gtest21ax;
-CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
@@ -710,7 +713,7 @@ INSERT INTO gtest21b (a) VALUES (0); -- ok now
CREATE TABLE gtestnn_parent (
f1 int,
f2 bigint,
- f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED NOT NULL
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) :Generated NOT NULL
) PARTITION BY RANGE (f1);
CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;
@@ -735,25 +738,25 @@ SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
(4 rows)
-- test ALTER TABLE ADD COLUMN
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) STORED; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) :Generated; -- error
ERROR: column "c" of relation "gtestnn_childdef" contains null values
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) STORED; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) :Generated; -- error
ERROR: column "c" of relation "gtestnn_child" contains null values
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) STORED; -- ok
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) :Generated; -- ok
-- index constraints
-CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) :Generated UNIQUE);
INSERT INTO gtest22a VALUES (2);
INSERT INTO gtest22a VALUES (3);
ERROR: duplicate key value violates unique constraint "gtest22a_b_key"
DETAIL: Key (b)=(1) already exists.
INSERT INTO gtest22a VALUES (4);
-CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) STORED, PRIMARY KEY (a, b));
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) :Generated, PRIMARY KEY (a, b));
INSERT INTO gtest22b VALUES (2);
INSERT INTO gtest22b VALUES (2);
ERROR: duplicate key value violates unique constraint "gtest22b_pkey"
DETAIL: Key (a, b)=(2, 1) already exists.
-- indexes
-CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
CREATE INDEX gtest22c_b_idx ON gtest22c (b);
CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
@@ -856,11 +859,11 @@ RESET enable_bitmapscan;
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
ERROR: invalid ON UPDATE action for foreign key constraint containing generated column
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
ERROR: invalid ON DELETE action for foreign key constraint containing generated column
-CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x));
\d gtest23b
Table "generated_stored_tests.gtest23b"
Column | Type | Collation | Nullable | Default
@@ -882,7 +885,7 @@ DETAIL: Key (b)=(5) is not present in table "gtest23a".
ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
DROP TABLE gtest23b;
DROP TABLE gtest23a;
-CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));
+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) :Generated, PRIMARY KEY (y));
INSERT INTO gtest23p VALUES (1), (2), (3);
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
INSERT INTO gtest23q VALUES (1, 2); -- ok
@@ -891,45 +894,45 @@ ERROR: insert or update on table "gtest23q" violates foreign key constraint "gt
DETAIL: Key (b)=(5) is not present in table "gtest23p".
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
-CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest24 (a) VALUES (4); -- ok
INSERT INTO gtest24 (a) VALUES (6); -- error
ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
-CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) STORED);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) :Generated);
INSERT INTO gtest24r (a) VALUES (4); -- ok
INSERT INTO gtest24r (a) VALUES (6); -- error
ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
CREATE TABLE gtest24at (a int PRIMARY KEY);
-ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED; -- ok
-CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated; -- ok
+CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- ok
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
-CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest24nn (a) VALUES (4); -- ok
INSERT INTO gtest24nn (a) VALUES (NULL); -- error
ERROR: value for domain gtestdomainnn violates check constraint "gtestdomainnn_check"
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
-CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);
+CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) :Generated);
ERROR: generated columns are not supported on typed tables
DROP TYPE gtest_type CASCADE;
-- partitioning cases
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) STORED
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) :Generated
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
ERROR: child column "f3" specifies generation expression
HINT: A child table column cannot be generated unless its parent column is.
-CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED);
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
ERROR: column "f3" in child table must not be a generated column
DROP TABLE gtest_parent, gtest_child;
-CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent
FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) STORED -- overrides gen expr
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) :Generated -- overrides gen expr
) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
f3 DEFAULT 42 -- error
@@ -940,7 +943,7 @@ CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
ERROR: identity columns are not supported on partitions
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
- f3 GENERATED ALWAYS AS (f2 * 2) VIRTUAL -- error
+ f3 GENERATED ALWAYS AS (f2 * 2) :Other -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
ERROR: column "f3" inherits from generated column of different kind
DETAIL: Parent column is STORED, child column is VIRTUAL.
@@ -957,12 +960,12 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
ERROR: table "gtest_child3" being attached contains an identity column "f3"
DETAIL: The new partition may not contain an identity column.
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Other);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
ERROR: column "f3" inherits from generated column of different kind
DETAIL: Parent column is STORED, child column is VIRTUAL.
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
\d gtest_child
Table "generated_stored_tests.gtest_child"
@@ -1126,12 +1129,12 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f3);
ERROR: cannot use generated column in partition key
LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
^
DETAIL: Column "f3" is a generated column.
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE ((f3 * 3));
ERROR: cannot use generated column in partition key
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
^
@@ -1139,7 +1142,7 @@ DETAIL: Column "f3" is a generated column.
-- ALTER TABLE ... ADD COLUMN
CREATE TABLE gtest25 (a int PRIMARY KEY);
INSERT INTO gtest25 VALUES (3), (4);
-ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) STORED, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) :Generated, ALTER COLUMN b SET EXPRESSION AS (a * 3);
SELECT * FROM gtest25 ORDER BY a;
a | b
---+----
@@ -1147,16 +1150,16 @@ SELECT * FROM gtest25 ORDER BY a;
4 | 12
(2 rows)
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) :Generated; -- error
ERROR: cannot use generated column "b" in column generation expression
DETAIL: A generated column cannot reference another generated column.
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) :Generated; -- error
ERROR: column "z" does not exist
ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
- ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED;
+ ADD COLUMN x int GENERATED ALWAYS AS (c * 4) :Generated;
ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
- ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) :Generated;
SELECT * FROM gtest25 ORDER BY a;
a | b | c | x | d | y
---+----+----+-----+-----+-----
@@ -1181,7 +1184,7 @@ Indexes:
CREATE TABLE gtest27 (
a int,
b int,
- x int GENERATED ALWAYS AS ((a + b) * 2) STORED
+ x int GENERATED ALWAYS AS ((a + b) * 2) :Generated
);
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
@@ -1217,7 +1220,7 @@ ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED NOT NULL; -- error
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated NOT NULL; -- error
ERROR: column "x" of relation "gtest27" contains null values
DELETE FROM gtest27 WHERE a IS NULL AND b IS NULL;
-- It's possible to alter the column types this way:
@@ -1225,7 +1228,7 @@ ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated;
\d gtest27
Table "generated_stored_tests.gtest27"
Column | Type | Collation | Nullable | Default
@@ -1258,7 +1261,7 @@ SELECT * FROM gtest27;
-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
@@ -1327,7 +1330,7 @@ ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
-- with inheritance
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
@@ -1351,7 +1354,7 @@ DROP TABLE gtest30 CASCADE;
NOTICE: drop cascades to table gtest30_1
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
@@ -1375,7 +1378,7 @@ Inherits: gtest30
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: cannot drop generation expression from inherited column
-- composite type dependencies
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
@@ -1393,7 +1396,7 @@ ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
DROP TABLE gtest31_1, gtest31_2;
-- Check it for a partitioned table, too
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text) PARTITION BY LIST (a);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
@@ -1401,7 +1404,7 @@ DROP TABLE gtest31_1, gtest31_2;
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
LANGUAGE plpgsql
@@ -1553,7 +1556,7 @@ CREATE TABLE gtest28a (
a int,
b int,
c int,
- x int GENERATED ALWAYS AS (b * 2) STORED
+ x int GENERATED ALWAYS AS (b * 2) :Generated
);
ALTER TABLE gtest28a DROP COLUMN a;
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index fbabf52db30..6f3fc17c395 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1,9 +1,12 @@
-- keep these tests aligned with generated_stored.sql
+-- We can use psql variables to keep the diff to a minimum.
+\set Generated 'VIRTUAL'
+\set Other 'STORED'
CREATE SCHEMA generated_virtual_tests;
GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
SET search_path = generated_virtual_tests;
-CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);
-CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) :Generated);
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;
table_name | column_name | column_default | is_nullable | is_generated | generation_expression
------------+-------------+----------------+-------------+--------------+-----------------------
@@ -29,73 +32,73 @@ Indexes:
"gtest1_pkey" PRIMARY KEY, btree (a)
-- duplicate generated
-CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL GENERATED ALWAYS AS (a * 3) VIRTUAL);
+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated GENERATED ALWAYS AS (a * 3) :Generated);
ERROR: multiple generation clauses specified for column "b" of table "gtest_err_1"
LINE 1: ...RY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL GENERATED ...
^
-- references to other generated columns, including self-references
-CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) :Generated);
ERROR: cannot use generated column "b" in column generation expression
LINE 1: ...2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) VIR...
^
DETAIL: A generated column cannot reference another generated column.
-CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIRTUAL);
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated, c int GENERATED ALWAYS AS (b * 3) :Generated);
ERROR: cannot use generated column "b" in column generation expression
LINE 1: ...YS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIR...
^
DETAIL: A generated column cannot reference another generated column.
-- a whole-row var is a self-reference on steroids, so disallow that too
CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRTUAL);
+ b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) :Generated);
ERROR: cannot use whole-row variable in column generation expression
LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRT...
^
DETAIL: This would cause the generated column to depend on its own value.
-- invalid reference
-CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) VIRTUAL);
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) :Generated);
ERROR: column "c" does not exist
LINE 1: ..._3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) VIR...
^
-- generation expression must be immutable
-CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) VIRTUAL);
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) :Generated);
ERROR: generation expression is not immutable
-- ... but be sure that the immutability test is accurate
-CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') VIRTUAL);
+CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') :Generated);
DROP TABLE gtest2;
-- cannot have default/identity and generated
-CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) :Generated);
ERROR: both default and generation expression specified for column "b" of table "gtest_err_5a"
LINE 1: ... gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ...
^
-CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) :Generated);
ERROR: both identity and generation expression specified for column "b" of table "gtest_err_5b"
LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ...
^
-- reference to system column not allowed in generated column
-- (except tableoid, which we test below)
-CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) VIRTUAL);
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) :Generated);
ERROR: cannot use system column "xmin" in column generation expression
LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37...
^
-- various prohibited constructs
-CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) VIRTUAL);
+CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) :Generated);
ERROR: aggregate functions are not allowed in column generation expressions
LINE 1: ...7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) VI...
^
-CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) VIRTUAL);
+CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) :Generated);
ERROR: window functions are not allowed in column generation expressions
LINE 1: ...7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number...
^
-CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) VIRTUAL);
+CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) :Generated);
ERROR: cannot use subquery in column generation expression
LINE 1: ...7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)...
^
-CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) VIRTUAL);
+CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) :Generated);
ERROR: set-returning functions are not allowed in column generation expressions
LINE 1: ...7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_s...
^
-- GENERATED BY DEFAULT not allowed
-CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) :Generated);
ERROR: for a generated column, GENERATED ALWAYS must be specified
LINE 1: ...E gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT...
^
@@ -215,8 +218,8 @@ CREATE TABLE gtestm (
id int PRIMARY KEY,
f1 int,
f2 int,
- f3 int GENERATED ALWAYS AS (f1 * 2) VIRTUAL,
- f4 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+ f3 int GENERATED ALWAYS AS (f1 * 2) :Generated,
+ f4 int GENERATED ALWAYS AS (f2 * 2) :Generated
);
INSERT INTO gtestm VALUES (1, 5, 100);
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
@@ -239,7 +242,7 @@ SELECT * FROM gtestm ORDER BY id;
DROP TABLE gtestm;
CREATE TABLE gtestm (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
@@ -336,12 +339,12 @@ SELECT * FROM gtest1;
-- can't have generated column that is a child of normal column
CREATE TABLE gtest_normal (a int, b int);
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) INHERITS (gtest_normal); -- error
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated) INHERITS (gtest_normal); -- error
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "b" with inherited definition
ERROR: child column "b" specifies generation expression
HINT: A child table column cannot be generated unless its parent column is.
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
ERROR: column "b" in child table must not be a generated column
DROP TABLE gtest_normal, gtest_normal_child;
@@ -352,11 +355,11 @@ ERROR: column "b" inherits from generated column but specifies default
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
NOTICE: merging column "b" with inherited definition
ERROR: column "b" inherits from generated column but specifies identity
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Other) INHERITS (gtest1); -- error
NOTICE: merging column "b" with inherited definition
ERROR: column "b" inherits from generated column of different kind
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
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Generated) INHERITS (gtest1); -- ok, overrides parent
NOTICE: merging column "b" with inherited definition
\d+ gtestx
Table "generated_virtual_tests.gtestx"
@@ -387,9 +390,9 @@ SELECT * FROM gtestx;
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
ERROR: column "b" in child table must be a generated column
-CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
-CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) VIRTUAL, a int NOT NULL);
+CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) :Generated, a int NOT NULL);
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
-- test multiple inheritance mismatches
CREATE TABLE gtesty (x int, b int DEFAULT 55);
@@ -402,12 +405,12 @@ CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
NOTICE: merging multiple inherited definitions of column "b"
ERROR: inherited column "b" has a generation conflict
DROP TABLE gtesty;
-CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) VIRTUAL);
+CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) :Generated);
CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
NOTICE: merging multiple inherited definitions of column "b"
ERROR: column "b" inherits conflicting generation expressions
HINT: To resolve the conflict, specify a generation expression explicitly.
-CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) VIRTUAL) INHERITS (gtest1, gtesty); -- ok
+CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) :Generated) INHERITS (gtest1, gtesty); -- ok
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.
@@ -423,7 +426,7 @@ Inherits: gtest1,
-- test correct handling of GENERATED column that's only in child
CREATE TABLE gtestp (f1 int);
-CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) VIRTUAL) INHERITS(gtestp);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) :Generated) INHERITS(gtestp);
INSERT INTO gtestc values(42);
TABLE gtestc;
f1 | f2
@@ -441,7 +444,7 @@ TABLE gtestc;
DROP TABLE gtestp CASCADE;
NOTICE: drop cascades to table gtestc
-- test update
-CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) VIRTUAL);
+CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) :Generated);
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
SELECT * FROM gtest3 ORDER BY a;
a | b
@@ -462,7 +465,7 @@ SELECT * FROM gtest3 ORDER BY a;
|
(4 rows)
-CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) VIRTUAL);
+CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) :Generated);
INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
SELECT * FROM gtest3a ORDER BY a;
a | b
@@ -527,7 +530,7 @@ SELECT * FROM gtest3 ORDER BY a;
(4 rows)
-- null values
-CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) :Generated);
INSERT INTO gtest2 VALUES (1);
SELECT * FROM gtest2;
a | b
@@ -536,7 +539,7 @@ SELECT * FROM gtest2;
(1 row)
-- simple column reference for varlena types
-CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) VIRTUAL);
+CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) :Generated);
INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
INSERT INTO gtest_varlena (a) VALUES(NULL);
SELECT * FROM gtest_varlena ORDER BY a;
@@ -551,7 +554,7 @@ DROP TABLE gtest_varlena;
CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
- b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) :Generated
); -- fails, user-defined type
ERROR: virtual generated column "b" cannot have a user-defined type
DETAIL: Virtual generated columns that make use of user-defined types are not yet supported.
@@ -562,11 +565,11 @@ DROP TYPE double_int;
-- using tableoid is allowed
CREATE TABLE gtest_tableoid (
a int PRIMARY KEY,
- b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) VIRTUAL
+ b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) :Generated
);
INSERT INTO gtest_tableoid VALUES (1), (2);
ALTER TABLE gtest_tableoid ADD COLUMN
- c regclass GENERATED ALWAYS AS (tableoid) VIRTUAL;
+ c regclass GENERATED ALWAYS AS (tableoid) :Generated;
SELECT * FROM gtest_tableoid;
a | b | c
---+---+----------------
@@ -575,7 +578,7 @@ SELECT * FROM gtest_tableoid;
(2 rows)
-- drop column behavior
-CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
ALTER TABLE gtest10 DROP COLUMN b; -- fails
ERROR: cannot drop column b of table gtest10 because other objects depend on it
DETAIL: column c of table gtest10 depends on column b of table gtest10
@@ -590,17 +593,17 @@ NOTICE: drop cascades to column c of table gtest10
Indexes:
"gtest10_pkey" PRIMARY KEY, btree (a)
-CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest10a DROP COLUMN b;
INSERT INTO gtest10a (a) VALUES (1);
-- privileges
CREATE USER regress_user11;
-CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
INSERT INTO gtest11 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c) ON gtest11 TO regress_user11;
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) VIRTUAL); -- fails, user-defined function
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated); -- fails, user-defined function
ERROR: generation expression uses user-defined function
LINE 1: ...nt PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) VI...
^
@@ -628,7 +631,7 @@ DROP TABLE gtest11;
DROP FUNCTION gf1(int);
DROP USER regress_user11;
-- check constraints
-CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 50));
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated CHECK (b < 50));
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check"
@@ -639,7 +642,7 @@ DETAIL: Column "b" of relation "gtest20" is a virtual generated column.
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported)
ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints
DETAIL: Column "b" of relation "gtest20" is a virtual generated column.
-CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
@@ -648,27 +651,27 @@ ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated b
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
-CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20b (a) VALUES (10);
INSERT INTO gtest20b (a) VALUES (30);
ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
ERROR: check constraint "chk" of relation "gtest20b" is violated by some row
-- check with whole-row reference
-CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
DETAIL: Failing row contains (null, virtual).
-- not-null constraints
-CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint
DETAIL: Failing row contains (0, virtual).
-- also check with table constraint syntax
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL, CONSTRAINT cc NOT NULL b);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated, CONSTRAINT cc NOT NULL b);
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21ax" violates not-null constraint
DETAIL: Failing row contains (0, virtual).
@@ -677,13 +680,13 @@ INSERT INTO gtest21ax (a) VALUES (1); --ok
ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
ERROR: column "b" of relation "gtest21ax" contains null values
DROP TABLE gtest21ax;
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21ax" violates not-null constraint
DETAIL: Failing row contains (0, virtual).
DROP TABLE gtest21ax;
-CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL);
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
@@ -698,7 +701,7 @@ INSERT INTO gtest21b (a) VALUES (0); -- ok now
CREATE TABLE gtestnn_parent (
f1 int,
f2 bigint,
- f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) VIRTUAL NOT NULL
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) :Generated NOT NULL
) PARTITION BY RANGE (f1);
CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;
@@ -723,23 +726,23 @@ SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
(4 rows)
-- test ALTER TABLE ADD COLUMN
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) VIRTUAL; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) :Generated; -- error
ERROR: column "c" of relation "gtestnn_childdef" contains null values
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) VIRTUAL; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) :Generated; -- error
ERROR: column "c" of relation "gtestnn_child" contains null values
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) VIRTUAL; -- ok
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) :Generated; -- ok
-- index constraints
-CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) :Generated UNIQUE);
ERROR: unique constraints on virtual generated columns are not supported
--INSERT INTO gtest22a VALUES (2);
--INSERT INTO gtest22a VALUES (3);
--INSERT INTO gtest22a VALUES (4);
-CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b));
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) :Generated, PRIMARY KEY (a, b));
ERROR: primary keys on virtual generated columns are not supported
--INSERT INTO gtest22b VALUES (2);
--INSERT INTO gtest22b VALUES (2);
-- indexes
-CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
--CREATE INDEX gtest22c_b_idx ON gtest22c (b);
--CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
--CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
@@ -766,11 +769,11 @@ CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
--INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
ERROR: invalid ON UPDATE action for foreign key constraint containing generated column
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
ERROR: invalid ON DELETE action for foreign key constraint containing generated column
-CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x));
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x));
ERROR: foreign key constraints on virtual generated columns are not supported
--\d gtest23b
--INSERT INTO gtest23b VALUES (1); -- ok
@@ -779,7 +782,7 @@ ERROR: foreign key constraints on virtual generated columns are not supported
--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
--DROP TABLE gtest23b;
--DROP TABLE gtest23a;
-CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) :Generated, PRIMARY KEY (y));
ERROR: primary keys on virtual generated columns are not supported
--INSERT INTO gtest23p VALUES (1), (2), (3);
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
@@ -788,47 +791,47 @@ ERROR: relation "gtest23p" does not exist
--INSERT INTO gtest23q VALUES (2, 5); -- error
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
-CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated);
ERROR: virtual generated column "b" cannot have a domain type
--INSERT INTO gtest24 (a) VALUES (4); -- ok
--INSERT INTO gtest24 (a) VALUES (6); -- error
CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
-CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) VIRTUAL);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) :Generated);
ERROR: virtual generated column "b" cannot have a domain type
--INSERT INTO gtest24r (a) VALUES (4); -- ok
--INSERT INTO gtest24r (a) VALUES (6); -- error
CREATE TABLE gtest24at (a int PRIMARY KEY);
-ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL; -- error
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated; -- error
ERROR: virtual generated column "b" cannot have a domain type
-CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- error
ERROR: virtual generated column "b" cannot have a domain type
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
-CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) :Generated);
ERROR: virtual generated column "b" cannot have a domain type
--INSERT INTO gtest24nn (a) VALUES (4); -- ok
--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
-CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) VIRTUAL);
+CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) :Generated);
ERROR: generated columns are not supported on typed tables
DROP TYPE gtest_type CASCADE;
-- partitioning cases
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) :Generated
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
ERROR: child column "f3" specifies generation expression
HINT: A child table column cannot be generated unless its parent column is.
-CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL);
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
ERROR: column "f3" in child table must not be a generated column
DROP TABLE gtest_parent, gtest_child;
-CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent
FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL -- overrides gen expr
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) :Generated -- overrides gen expr
) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
f3 DEFAULT 42 -- error
@@ -839,7 +842,7 @@ CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
ERROR: identity columns are not supported on partitions
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
- f3 GENERATED ALWAYS AS (f2 * 2) STORED -- error
+ f3 GENERATED ALWAYS AS (f2 * 2) :Other -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
ERROR: column "f3" inherits from generated column of different kind
DETAIL: Parent column is VIRTUAL, child column is STORED.
@@ -856,12 +859,12 @@ ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09
ERROR: table "gtest_child3" being attached contains an identity column "f3"
DETAIL: The new partition may not contain an identity column.
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Other);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
ERROR: column "f3" inherits from generated column of different kind
DETAIL: Parent column is VIRTUAL, child column is STORED.
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Generated);
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"
@@ -1025,12 +1028,12 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f3);
ERROR: cannot use generated column in partition key
LINE 1: ...NERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
^
DETAIL: Column "f3" is a generated column.
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE ((f3 * 3));
ERROR: cannot use generated column in partition key
LINE 1: ...D ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
^
@@ -1038,7 +1041,7 @@ DETAIL: Column "f3" is a generated column.
-- ALTER TABLE ... ADD COLUMN
CREATE TABLE gtest25 (a int PRIMARY KEY);
INSERT INTO gtest25 VALUES (3), (4);
-ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) VIRTUAL, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) :Generated, ALTER COLUMN b SET EXPRESSION AS (a * 3);
SELECT * FROM gtest25 ORDER BY a;
a | b
---+----
@@ -1046,16 +1049,16 @@ SELECT * FROM gtest25 ORDER BY a;
4 | 12
(2 rows)
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) VIRTUAL; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) :Generated; -- error
ERROR: cannot use generated column "b" in column generation expression
DETAIL: A generated column cannot reference another generated column.
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) VIRTUAL; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) :Generated; -- error
ERROR: column "z" does not exist
ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
- ADD COLUMN x int GENERATED ALWAYS AS (c * 4) VIRTUAL;
+ ADD COLUMN x int GENERATED ALWAYS AS (c * 4) :Generated;
ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
- ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) :Generated;
SELECT * FROM gtest25 ORDER BY a;
a | b | c | x | d | y
---+----+----+-----+-----+-----
@@ -1080,7 +1083,7 @@ Indexes:
CREATE TABLE gtest27 (
a int,
b int,
- x int GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL
+ x int GENERATED ALWAYS AS ((a + b) * 2) :Generated
);
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
@@ -1115,7 +1118,7 @@ ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL NOT NULL; -- error
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated NOT NULL; -- error
ERROR: column "x" of relation "gtest27" contains null values
DELETE FROM gtest27 WHERE a IS NULL AND b IS NULL;
-- It's possible to alter the column types this way:
@@ -1123,7 +1126,7 @@ ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated;
\d gtest27
Table "generated_virtual_tests.gtest27"
Column | Type | Collation | Nullable | Default
@@ -1156,7 +1159,7 @@ SELECT * FROM gtest27;
-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
a int,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
@@ -1223,7 +1226,7 @@ SELECT * FROM gtest29;
-- with inheritance
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
@@ -1249,7 +1252,7 @@ DROP TABLE gtest30 CASCADE;
NOTICE: drop cascades to table gtest30_1
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
@@ -1273,7 +1276,7 @@ Inherits: gtest30
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: cannot drop generation expression from inherited column
-- composite type dependencies
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
@@ -1288,7 +1291,7 @@ CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b));
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
DROP TABLE gtest31_1, gtest31_2;
-- Check it for a partitioned table, too
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text) PARTITION BY LIST (a);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text) PARTITION BY LIST (a);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
@@ -1296,7 +1299,7 @@ DROP TABLE gtest31_1, gtest31_2;
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
LANGUAGE plpgsql
@@ -1448,7 +1451,7 @@ CREATE TABLE gtest28a (
a int,
b int,
c int,
- x int GENERATED ALWAYS AS (b * 2) VIRTUAL
+ x int GENERATED ALWAYS AS (b * 2) :Generated
);
ALTER TABLE gtest28a DROP COLUMN a;
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
@@ -1479,7 +1482,7 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
-- these tests are specific to generated_virtual.sql
--
-- using user-defined type not yet supported
-CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
+CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) :Generated); -- error
ERROR: generation expression uses user-defined type
LINE 1: ...main1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a...
^
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index e98aef4d3f9..ad27ba217f1 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -1,12 +1,15 @@
-- keep these tests aligned with generated_virtual.sql
+-- We can use psql variables to keep the diff to a minimum.
+\set Generated 'STORED'
+\set Other 'VIRTUAL'
CREATE SCHEMA generated_stored_tests;
GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
SET search_path = generated_stored_tests;
-CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
-CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) :Generated);
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
@@ -15,40 +18,40 @@ SELECT table_name, column_name, dependent_column FROM information_schema.column_
\d gtest1
-- duplicate generated
-CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED GENERATED ALWAYS AS (a * 3) STORED);
+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated GENERATED ALWAYS AS (a * 3) :Generated);
-- references to other generated columns, including self-references
-CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) STORED);
-CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STORED);
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) :Generated);
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated, c int GENERATED ALWAYS AS (b * 3) :Generated);
-- a whole-row var is a self-reference on steroids, so disallow that too
CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STORED);
+ b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) :Generated);
-- invalid reference
-CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STORED);
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) :Generated);
-- generation expression must be immutable
-CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) STORED);
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) :Generated);
-- ... but be sure that the immutability test is accurate
-CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') STORED);
+CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') :Generated);
DROP TABLE gtest2;
-- cannot have default/identity and generated
-CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED);
-CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) :Generated);
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) :Generated);
-- reference to system column not allowed in generated column
-- (except tableoid, which we test below)
-CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED);
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) :Generated);
-- various prohibited constructs
-CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) STORED);
-CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) STORED);
-CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) STORED);
-CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) STORED);
+CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) :Generated);
+CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) :Generated);
+CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) :Generated);
+CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) :Generated);
-- GENERATED BY DEFAULT not allowed
-CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) STORED);
+CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) :Generated);
INSERT INTO gtest1 VALUES (1);
INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok
@@ -94,8 +97,8 @@ CREATE TABLE gtestm (
id int PRIMARY KEY,
f1 int,
f2 int,
- f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
- f4 int GENERATED ALWAYS AS (f2 * 2) STORED
+ f3 int GENERATED ALWAYS AS (f1 * 2) :Generated,
+ f4 int GENERATED ALWAYS AS (f2 * 2) :Generated
);
INSERT INTO gtestm VALUES (1, 5, 100);
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
@@ -107,7 +110,7 @@ DROP TABLE gtestm;
CREATE TABLE gtestm (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
@@ -144,16 +147,16 @@ SELECT * FROM gtest1;
-- can't have generated column that is a child of normal column
CREATE TABLE gtest_normal (a int, b int);
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); -- error
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated) INHERITS (gtest_normal); -- error
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
DROP TABLE gtest_normal, gtest_normal_child;
-- test inheritance mismatches between parent and child
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) VIRTUAL) INHERITS (gtest1); -- error
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Other) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Generated) INHERITS (gtest1); -- ok, overrides parent
\d+ gtestx
INSERT INTO gtestx (a, x) VALUES (11, 22);
SELECT * FROM gtest1;
@@ -161,9 +164,9 @@ SELECT * FROM gtestx;
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
-CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
-CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
+CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) :Generated, a int NOT NULL);
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
-- test multiple inheritance mismatches
@@ -175,14 +178,14 @@ CREATE TABLE gtesty (x int, b int);
CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
DROP TABLE gtesty;
-CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED);
+CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) :Generated);
CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
-CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) STORED) INHERITS (gtest1, gtesty); -- ok
+CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) :Generated) INHERITS (gtest1, gtesty); -- ok
\d gtest1_y
-- test correct handling of GENERATED column that's only in child
CREATE TABLE gtestp (f1 int);
-CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) STORED) INHERITS(gtestp);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) :Generated) INHERITS(gtestp);
INSERT INTO gtestc values(42);
TABLE gtestc;
UPDATE gtestp SET f1 = f1 * 10;
@@ -190,13 +193,13 @@ TABLE gtestc;
DROP TABLE gtestp CASCADE;
-- test stored update
-CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
+CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) :Generated);
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
SELECT * FROM gtest3 ORDER BY a;
UPDATE gtest3 SET a = 22 WHERE a = 2;
SELECT * FROM gtest3 ORDER BY a;
-CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED);
+CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) :Generated);
INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
SELECT * FROM gtest3a ORDER BY a;
UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
@@ -236,12 +239,12 @@ COPY gtest3 (a, b) FROM stdin;
SELECT * FROM gtest3 ORDER BY a;
-- null values
-CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) :Generated);
INSERT INTO gtest2 VALUES (1);
SELECT * FROM gtest2;
-- simple column reference for varlena types
-CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED);
+CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) :Generated);
INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
INSERT INTO gtest_varlena (a) VALUES(NULL);
SELECT * FROM gtest_varlena ORDER BY a;
@@ -251,7 +254,7 @@ DROP TABLE gtest_varlena;
CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
- b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) :Generated
);
INSERT INTO gtest4 VALUES (1), (6);
SELECT * FROM gtest4;
@@ -262,35 +265,35 @@ DROP TYPE double_int;
-- using tableoid is allowed
CREATE TABLE gtest_tableoid (
a int PRIMARY KEY,
- b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) STORED
+ b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) :Generated
);
INSERT INTO gtest_tableoid VALUES (1), (2);
ALTER TABLE gtest_tableoid ADD COLUMN
- c regclass GENERATED ALWAYS AS (tableoid) STORED;
+ c regclass GENERATED ALWAYS AS (tableoid) :Generated;
SELECT * FROM gtest_tableoid;
-- drop column behavior
-CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
ALTER TABLE gtest10 DROP COLUMN b; -- fails
ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
\d gtest10
-CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest10a DROP COLUMN b;
INSERT INTO gtest10a (a) VALUES (1);
-- privileges
CREATE USER regress_user11;
-CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
+CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
INSERT INTO gtest11 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c) ON gtest11 TO regress_user11;
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated);
INSERT INTO gtest12 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
@@ -308,14 +311,14 @@ DROP FUNCTION gf1(int);
DROP USER regress_user11;
-- check constraints
-CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED CHECK (b < 50));
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated CHECK (b < 50));
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
-CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
@@ -323,37 +326,37 @@ ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
-CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20b (a) VALUES (10);
INSERT INTO gtest20b (a) VALUES (30);
ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
-- check with whole-row reference
-CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
-- not-null constraints
-CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
-- also check with table constraint syntax
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED, CONSTRAINT cc NOT NULL b);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated, CONSTRAINT cc NOT NULL b);
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
INSERT INTO gtest21ax (a) VALUES (1); --ok
-- SET EXPRESSION supports not null constraint
ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
DROP TABLE gtest21ax;
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
DROP TABLE gtest21ax;
-CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
@@ -365,7 +368,7 @@ INSERT INTO gtest21b (a) VALUES (0); -- ok now
CREATE TABLE gtestnn_parent (
f1 int,
f2 bigint,
- f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED NOT NULL
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) :Generated NOT NULL
) PARTITION BY RANGE (f1);
CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;
@@ -377,21 +380,21 @@ ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) + nu
INSERT INTO gtestnn_parent VALUES (10, 11, default); -- ok
SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
-- test ALTER TABLE ADD COLUMN
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) STORED; -- error
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) STORED; -- error
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) STORED; -- ok
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) :Generated; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) :Generated; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) :Generated; -- ok
-- index constraints
-CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) :Generated UNIQUE);
INSERT INTO gtest22a VALUES (2);
INSERT INTO gtest22a VALUES (3);
INSERT INTO gtest22a VALUES (4);
-CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) STORED, PRIMARY KEY (a, b));
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) :Generated, PRIMARY KEY (a, b));
INSERT INTO gtest22b VALUES (2);
INSERT INTO gtest22b VALUES (2);
-- indexes
-CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
CREATE INDEX gtest22c_b_idx ON gtest22c (b);
CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
@@ -422,10 +425,10 @@ RESET enable_bitmapscan;
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
-CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x));
\d gtest23b
INSERT INTO gtest23b VALUES (1); -- ok
@@ -436,7 +439,7 @@ ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
DROP TABLE gtest23b;
DROP TABLE gtest23a;
-CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));
+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) :Generated, PRIMARY KEY (y));
INSERT INTO gtest23p VALUES (1), (2), (3);
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
@@ -445,43 +448,43 @@ INSERT INTO gtest23q VALUES (2, 5); -- error
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
-CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest24 (a) VALUES (4); -- ok
INSERT INTO gtest24 (a) VALUES (6); -- error
CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
-CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) STORED);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) :Generated);
INSERT INTO gtest24r (a) VALUES (4); -- ok
INSERT INTO gtest24r (a) VALUES (6); -- error
CREATE TABLE gtest24at (a int PRIMARY KEY);
-ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED; -- ok
-CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated; -- ok
+CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- ok
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
-CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) STORED);
+CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest24nn (a) VALUES (4); -- ok
INSERT INTO gtest24nn (a) VALUES (NULL); -- error
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
-CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);
+CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) :Generated);
DROP TYPE gtest_type CASCADE;
-- partitioning cases
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) STORED
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) :Generated
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
-CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED);
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
DROP TABLE gtest_parent, gtest_child;
-CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent
FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) STORED -- overrides gen expr
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) :Generated -- overrides gen expr
) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
f3 DEFAULT 42 -- error
@@ -490,7 +493,7 @@ CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
- f3 GENERATED ALWAYS AS (f2 * 2) VIRTUAL -- error
+ f3 GENERATED ALWAYS AS (f2 * 2) :Other -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
@@ -501,10 +504,10 @@ DROP TABLE gtest_child3;
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS IDENTITY);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Other);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
\d gtest_child
\d gtest_child2
@@ -538,21 +541,21 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE ((f3 * 3));
-- ALTER TABLE ... ADD COLUMN
CREATE TABLE gtest25 (a int PRIMARY KEY);
INSERT INTO gtest25 VALUES (3), (4);
-ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) STORED, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) :Generated, ALTER COLUMN b SET EXPRESSION AS (a * 3);
SELECT * FROM gtest25 ORDER BY a;
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED; -- error
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) :Generated; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) :Generated; -- error
ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
- ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED;
+ ADD COLUMN x int GENERATED ALWAYS AS (c * 4) :Generated;
ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
- ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) :Generated;
SELECT * FROM gtest25 ORDER BY a;
\d gtest25
@@ -560,7 +563,7 @@ SELECT * FROM gtest25 ORDER BY a;
CREATE TABLE gtest27 (
a int,
b int,
- x int GENERATED ALWAYS AS ((a + b) * 2) STORED
+ x int GENERATED ALWAYS AS ((a + b) * 2) :Generated
);
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
@@ -575,14 +578,14 @@ ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED NOT NULL; -- error
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated NOT NULL; -- error
DELETE FROM gtest27 WHERE a IS NULL AND b IS NULL;
-- It's possible to alter the column types this way:
ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated;
\d gtest27
-- Ideally you could just do this, but not today (and should x change type?):
ALTER TABLE gtest27
@@ -594,7 +597,7 @@ SELECT * FROM gtest27;
-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
@@ -621,7 +624,7 @@ ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
-- with inheritance
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
@@ -630,7 +633,7 @@ ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
DROP TABLE gtest30 CASCADE;
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
@@ -639,7 +642,7 @@ ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
-- composite type dependencies
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
@@ -658,7 +661,7 @@ ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
DROP TABLE gtest31_1, gtest31_2;
-- Check it for a partitioned table, too
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text) PARTITION BY LIST (a);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
DROP TABLE gtest31_1, gtest31_2;
@@ -666,7 +669,7 @@ DROP TABLE gtest31_1, gtest31_2;
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) STORED
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
@@ -782,7 +785,7 @@ CREATE TABLE gtest28a (
a int,
b int,
c int,
- x int GENERATED ALWAYS AS (b * 2) STORED
+ x int GENERATED ALWAYS AS (b * 2) :Generated
);
ALTER TABLE gtest28a DROP COLUMN a;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index f69dd57336d..b66abe80c12 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -1,12 +1,15 @@
-- keep these tests aligned with generated_stored.sql
+-- We can use psql variables to keep the diff to a minimum.
+\set Generated 'VIRTUAL'
+\set Other 'STORED'
CREATE SCHEMA generated_virtual_tests;
GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
SET search_path = generated_virtual_tests;
-CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);
-CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) :Generated);
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;
@@ -15,40 +18,40 @@ SELECT table_name, column_name, dependent_column FROM information_schema.column_
\d gtest1
-- duplicate generated
-CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL GENERATED ALWAYS AS (a * 3) VIRTUAL);
+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated GENERATED ALWAYS AS (a * 3) :Generated);
-- references to other generated columns, including self-references
-CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) VIRTUAL);
-CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIRTUAL);
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) :Generated);
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated, c int GENERATED ALWAYS AS (b * 3) :Generated);
-- a whole-row var is a self-reference on steroids, so disallow that too
CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRTUAL);
+ b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) :Generated);
-- invalid reference
-CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) VIRTUAL);
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) :Generated);
-- generation expression must be immutable
-CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) VIRTUAL);
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) :Generated);
-- ... but be sure that the immutability test is accurate
-CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') VIRTUAL);
+CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') :Generated);
DROP TABLE gtest2;
-- cannot have default/identity and generated
-CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) VIRTUAL);
-CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) :Generated);
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) :Generated);
-- reference to system column not allowed in generated column
-- (except tableoid, which we test below)
-CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) VIRTUAL);
+CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) :Generated);
-- various prohibited constructs
-CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) VIRTUAL);
-CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) VIRTUAL);
-CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) VIRTUAL);
-CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) VIRTUAL);
+CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) :Generated);
+CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) :Generated);
+CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) :Generated);
+CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) :Generated);
-- GENERATED BY DEFAULT not allowed
-CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) :Generated);
INSERT INTO gtest1 VALUES (1);
INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok
@@ -94,8 +97,8 @@ CREATE TABLE gtestm (
id int PRIMARY KEY,
f1 int,
f2 int,
- f3 int GENERATED ALWAYS AS (f1 * 2) VIRTUAL,
- f4 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+ f3 int GENERATED ALWAYS AS (f1 * 2) :Generated,
+ f4 int GENERATED ALWAYS AS (f2 * 2) :Generated
);
INSERT INTO gtestm VALUES (1, 5, 100);
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
@@ -107,7 +110,7 @@ DROP TABLE gtestm;
CREATE TABLE gtestm (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
@@ -144,16 +147,16 @@ SELECT * FROM gtest1;
-- can't have generated column that is a child of normal column
CREATE TABLE gtest_normal (a int, b int);
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) INHERITS (gtest_normal); -- error
-CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated) INHERITS (gtest_normal); -- error
+CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
DROP TABLE gtest_normal, gtest_normal_child;
-- test inheritance mismatches between parent and child
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- error
-CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) VIRTUAL) INHERITS (gtest1); -- ok, overrides parent
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Other) INHERITS (gtest1); -- error
+CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) :Generated) INHERITS (gtest1); -- ok, overrides parent
\d+ gtestx
INSERT INTO gtestx (a, x) VALUES (11, 22);
SELECT * FROM gtest1;
@@ -161,9 +164,9 @@ SELECT * FROM gtestx;
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
-CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
-CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) VIRTUAL, a int NOT NULL);
+CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) :Generated, a int NOT NULL);
ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
-- test multiple inheritance mismatches
@@ -175,14 +178,14 @@ CREATE TABLE gtesty (x int, b int);
CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
DROP TABLE gtesty;
-CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) VIRTUAL);
+CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) :Generated);
CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
-CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) VIRTUAL) INHERITS (gtest1, gtesty); -- ok
+CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) :Generated) INHERITS (gtest1, gtesty); -- ok
\d gtest1_y
-- test correct handling of GENERATED column that's only in child
CREATE TABLE gtestp (f1 int);
-CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) VIRTUAL) INHERITS(gtestp);
+CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) :Generated) INHERITS(gtestp);
INSERT INTO gtestc values(42);
TABLE gtestc;
UPDATE gtestp SET f1 = f1 * 10;
@@ -190,13 +193,13 @@ TABLE gtestc;
DROP TABLE gtestp CASCADE;
-- test update
-CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) VIRTUAL);
+CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) :Generated);
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
SELECT * FROM gtest3 ORDER BY a;
UPDATE gtest3 SET a = 22 WHERE a = 2;
SELECT * FROM gtest3 ORDER BY a;
-CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) VIRTUAL);
+CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) :Generated);
INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
SELECT * FROM gtest3a ORDER BY a;
UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
@@ -236,12 +239,12 @@ COPY gtest3 (a, b) FROM stdin;
SELECT * FROM gtest3 ORDER BY a;
-- null values
-CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
+CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) :Generated);
INSERT INTO gtest2 VALUES (1);
SELECT * FROM gtest2;
-- simple column reference for varlena types
-CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) VIRTUAL);
+CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) :Generated);
INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
INSERT INTO gtest_varlena (a) VALUES(NULL);
SELECT * FROM gtest_varlena ORDER BY a;
@@ -251,7 +254,7 @@ DROP TABLE gtest_varlena;
CREATE TYPE double_int as (a int, b int);
CREATE TABLE gtest4 (
a int,
- b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
+ b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) :Generated
); -- fails, user-defined type
--INSERT INTO gtest4 VALUES (1), (6);
--SELECT * FROM gtest4;
@@ -262,35 +265,35 @@ DROP TYPE double_int;
-- using tableoid is allowed
CREATE TABLE gtest_tableoid (
a int PRIMARY KEY,
- b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) VIRTUAL
+ b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) :Generated
);
INSERT INTO gtest_tableoid VALUES (1), (2);
ALTER TABLE gtest_tableoid ADD COLUMN
- c regclass GENERATED ALWAYS AS (tableoid) VIRTUAL;
+ c regclass GENERATED ALWAYS AS (tableoid) :Generated;
SELECT * FROM gtest_tableoid;
-- drop column behavior
-CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
ALTER TABLE gtest10 DROP COLUMN b; -- fails
ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
\d gtest10
-CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest10a DROP COLUMN b;
INSERT INTO gtest10a (a) VALUES (1);
-- privileges
CREATE USER regress_user11;
-CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) VIRTUAL);
+CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) :Generated);
INSERT INTO gtest11 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c) ON gtest11 TO regress_user11;
CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) VIRTUAL); -- fails, user-defined function
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated); -- fails, user-defined function
--INSERT INTO gtest12 VALUES (1, 10), (2, 20);
--GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
@@ -309,14 +312,14 @@ DROP FUNCTION gf1(int);
DROP USER regress_user11;
-- check constraints
-CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 50));
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated CHECK (b < 50));
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint (currently not supported)
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported)
-CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
@@ -324,37 +327,37 @@ ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 50); -- fails on existing row
ALTER TABLE gtest20a ADD COLUMN c float8 DEFAULT random() CHECK (b < 61); -- ok
-CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
INSERT INTO gtest20b (a) VALUES (10);
INSERT INTO gtest20b (a) VALUES (30);
ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
-- check with whole-row reference
-CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
-- not-null constraints
-CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
-- also check with table constraint syntax
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL, CONSTRAINT cc NOT NULL b);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated, CONSTRAINT cc NOT NULL b);
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
INSERT INTO gtest21ax (a) VALUES (1); --ok
-- SET EXPRESSION supports not null constraint
ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
DROP TABLE gtest21ax;
-CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL);
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
DROP TABLE gtest21ax;
-CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL);
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) :Generated);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
@@ -366,7 +369,7 @@ INSERT INTO gtest21b (a) VALUES (0); -- ok now
CREATE TABLE gtestnn_parent (
f1 int,
f2 bigint,
- f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) VIRTUAL NOT NULL
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) :Generated NOT NULL
) PARTITION BY RANGE (f1);
CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;
@@ -378,21 +381,21 @@ ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) + nu
INSERT INTO gtestnn_parent VALUES (10, 11, default); -- ok
SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
-- test ALTER TABLE ADD COLUMN
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) VIRTUAL; -- error
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) VIRTUAL; -- error
-ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) VIRTUAL; -- ok
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 14) + nullif(f2, 10)) :Generated; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 13) + nullif(f2, 5)) :Generated; -- error
+ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) :Generated; -- ok
-- index constraints
-CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) :Generated UNIQUE);
--INSERT INTO gtest22a VALUES (2);
--INSERT INTO gtest22a VALUES (3);
--INSERT INTO gtest22a VALUES (4);
-CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b));
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) :Generated, PRIMARY KEY (a, b));
--INSERT INTO gtest22b VALUES (2);
--INSERT INTO gtest22b VALUES (2);
-- indexes
-CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
--CREATE INDEX gtest22c_b_idx ON gtest22c (b);
--CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
--CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
@@ -423,10 +426,10 @@ CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
--INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
-CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
-CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x));
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x));
--\d gtest23b
--INSERT INTO gtest23b VALUES (1); -- ok
@@ -437,7 +440,7 @@ CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRT
--DROP TABLE gtest23b;
--DROP TABLE gtest23a;
-CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
+CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) :Generated, PRIMARY KEY (y));
--INSERT INTO gtest23p VALUES (1), (2), (3);
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
@@ -446,43 +449,43 @@ CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
-CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated);
--INSERT INTO gtest24 (a) VALUES (4); -- ok
--INSERT INTO gtest24 (a) VALUES (6); -- error
CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
-CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) VIRTUAL);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) :Generated);
--INSERT INTO gtest24r (a) VALUES (4); -- ok
--INSERT INTO gtest24r (a) VALUES (6); -- error
CREATE TABLE gtest24at (a int PRIMARY KEY);
-ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL; -- error
-CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated; -- error
+CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1; -- error
CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
-CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) :Generated);
--INSERT INTO gtest24nn (a) VALUES (4); -- ok
--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
-CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) VIRTUAL);
+CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) :Generated);
DROP TYPE gtest_type CASCADE;
-- partitioning cases
CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) VIRTUAL
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) :Generated
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
-CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL);
+CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
DROP TABLE gtest_parent, gtest_child;
-CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f1);
+CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f1);
CREATE TABLE gtest_child PARTITION OF gtest_parent
FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
- f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL -- overrides gen expr
+ f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) :Generated -- overrides gen expr
) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
f3 DEFAULT 42 -- error
@@ -491,7 +494,7 @@ CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
- f3 GENERATED ALWAYS AS (f2 * 2) STORED -- error
+ f3 GENERATED ALWAYS AS (f2 * 2) :Other -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
@@ -502,10 +505,10 @@ DROP TABLE gtest_child3;
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS IDENTITY);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Other);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
DROP TABLE gtest_child3;
-CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) VIRTUAL);
+CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) :Generated);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
\d gtest_child
\d gtest_child2
@@ -539,21 +542,21 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
-CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE ((f3 * 3));
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE (f3);
+CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) :Generated) PARTITION BY RANGE ((f3 * 3));
-- ALTER TABLE ... ADD COLUMN
CREATE TABLE gtest25 (a int PRIMARY KEY);
INSERT INTO gtest25 VALUES (3), (4);
-ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) VIRTUAL, ALTER COLUMN b SET EXPRESSION AS (a * 3);
+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) :Generated, ALTER COLUMN b SET EXPRESSION AS (a * 3);
SELECT * FROM gtest25 ORDER BY a;
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) VIRTUAL; -- error
-ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) VIRTUAL; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) :Generated; -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) :Generated; -- error
ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
- ADD COLUMN x int GENERATED ALWAYS AS (c * 4) VIRTUAL;
+ ADD COLUMN x int GENERATED ALWAYS AS (c * 4) :Generated;
ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
- ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
+ ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) :Generated;
SELECT * FROM gtest25 ORDER BY a;
\d gtest25
@@ -561,7 +564,7 @@ SELECT * FROM gtest25 ORDER BY a;
CREATE TABLE gtest27 (
a int,
b int,
- x int GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL
+ x int GENERATED ALWAYS AS ((a + b) * 2) :Generated
);
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
@@ -576,14 +579,14 @@ ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL NOT NULL; -- error
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated NOT NULL; -- error
DELETE FROM gtest27 WHERE a IS NULL AND b IS NULL;
-- It's possible to alter the column types this way:
ALTER TABLE gtest27
DROP COLUMN x,
ALTER COLUMN a TYPE bigint,
ALTER COLUMN b TYPE bigint,
- ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) VIRTUAL;
+ ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) :Generated;
\d gtest27
-- Ideally you could just do this, but not today (and should x change type?):
ALTER TABLE gtest27
@@ -595,7 +598,7 @@ SELECT * FROM gtest27;
-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
a int,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
@@ -622,7 +625,7 @@ SELECT * FROM gtest29;
-- with inheritance
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
@@ -631,7 +634,7 @@ ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
DROP TABLE gtest30 CASCADE;
CREATE TABLE gtest30 (
a int,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
@@ -640,7 +643,7 @@ ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
-- composite type dependencies
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
@@ -659,7 +662,7 @@ ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
DROP TABLE gtest31_1, gtest31_2;
-- Check it for a partitioned table, too
-CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text) PARTITION BY LIST (a);
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') :Generated, c text) PARTITION BY LIST (a);
CREATE TABLE gtest31_2 (x int, y gtest31_1);
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
DROP TABLE gtest31_1, gtest31_2;
@@ -667,7 +670,7 @@ DROP TABLE gtest31_1, gtest31_2;
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
- b int GENERATED ALWAYS AS (a * 2) VIRTUAL
+ b int GENERATED ALWAYS AS (a * 2) :Generated
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
@@ -783,7 +786,7 @@ CREATE TABLE gtest28a (
a int,
b int,
c int,
- x int GENERATED ALWAYS AS (b * 2) VIRTUAL
+ x int GENERATED ALWAYS AS (b * 2) :Generated
);
ALTER TABLE gtest28a DROP COLUMN a;
@@ -804,7 +807,7 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
--
-- using user-defined type not yet supported
-CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
+CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) :Generated); -- error
create table gtest32 (
a int primary key,
--
2.39.5