public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yugo Nagata <[email protected]>
Subject: [PATCH] Align tests for stored and virtual generated columns
Date: Fri, 8 Aug 2025 11:34:11 +0900
These tests were intended to be aligned with each other, but additional
tests for virtual generated columns disrupted that alignment.
The test confirming that user-defined types are not allowed in virtual
generated columns has also been moved to the generated_virtual.sql-specific
section.
---
.../regress/expected/generated_stored.out | 70 ++++++++++++++++++-
.../regress/expected/generated_virtual.out | 14 ++--
src/test/regress/sql/generated_stored.sql | 51 +++++++++++++-
src/test/regress/sql/generated_virtual.sql | 8 +--
4 files changed, 128 insertions(+), 15 deletions(-)
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..6c70353cd97 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -679,14 +679,67 @@ 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).
-CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+-- 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);
+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).
+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
+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);
+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);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
-INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
+INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint
DETAIL: Failing row contains (0, null).
+INSERT INTO gtest21b (a) VALUES (NULL); -- error
+ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint
+DETAIL: Failing row contains (null, null).
ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- not-null constraint with partitioned table
+CREATE TABLE gtestnn_parent (
+ f1 int,
+ f2 bigint,
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED 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;
+-- check the error messages
+INSERT INTO gtestnn_parent VALUES (2, 2, default), (3, 5, default), (14, 12, default); -- ok
+INSERT INTO gtestnn_parent VALUES (1, 2, default); -- error
+ERROR: null value in column "f3" of relation "gtestnn_child" violates not-null constraint
+DETAIL: Failing row contains (1, 2, null).
+INSERT INTO gtestnn_parent VALUES (2, 10, default); -- error
+ERROR: null value in column "f3" of relation "gtestnn_child" violates not-null constraint
+DETAIL: Failing row contains (2, 10, null).
+ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) + nullif(f2, 11)); -- error
+ERROR: column "f3" of relation "gtestnn_child" contains null values
+INSERT INTO gtestnn_parent VALUES (10, 11, default); -- ok
+SELECT * FROM gtestnn_parent ORDER BY f1, f2, f3;
+ f1 | f2 | f3
+----+----+----
+ 2 | 2 | 4
+ 3 | 5 | 8
+ 10 | 11 | 21
+ 14 | 12 | 26
+(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
+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
+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
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
INSERT INTO gtest22a VALUES (2);
@@ -847,6 +900,10 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A
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 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);
INSERT INTO gtest24nn (a) VALUES (4); -- ok
@@ -1154,6 +1211,15 @@ DETAIL: Column "x" is a generated column.
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
ERROR: column "x" of relation "gtest27" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.
+-- test not-null checking during table rewrite
+INSERT INTO gtest27 (a, b) VALUES (NULL, NULL);
+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
+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:
ALTER TABLE gtest27
DROP COLUMN x,
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..f959141d7c1 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -552,7 +552,7 @@ 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
-);
+); -- 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.
--INSERT INTO gtest4 VALUES (1), (6);
@@ -808,12 +808,6 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTU
ERROR: virtual generated column "b" cannot have a domain type
--INSERT INTO gtest24nn (a) VALUES (4); -- ok
--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
--- using user-defined type not yet supported
-CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- error
-ERROR: generation expression uses user-defined type
-LINE 1: ...main1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a...
- ^
-DETAIL: Virtual generated columns that make use of user-defined types are not yet supported.
-- 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);
@@ -1484,6 +1478,12 @@ 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
+ERROR: generation expression uses user-defined type
+LINE 1: ...main1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a...
+ ^
+DETAIL: Virtual generated columns that make use of user-defined types are not yet supported.
create table gtest32 (
a int primary key,
b int generated always as (a * 2),
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..e98aef4d3f9 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -340,13 +340,47 @@ CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
-CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
+-- 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);
+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);
+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);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
-INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
+INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
+INSERT INTO gtest21b (a) VALUES (NULL); -- error
ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- not-null constraint with partitioned table
+CREATE TABLE gtestnn_parent (
+ f1 int,
+ f2 bigint,
+ f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED 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;
+-- check the error messages
+INSERT INTO gtestnn_parent VALUES (2, 2, default), (3, 5, default), (14, 12, default); -- ok
+INSERT INTO gtestnn_parent VALUES (1, 2, default); -- error
+INSERT INTO gtestnn_parent VALUES (2, 10, default); -- error
+ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) + nullif(f2, 11)); -- error
+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
+
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
INSERT INTO gtest22a VALUES (2);
@@ -419,6 +453,11 @@ CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS A
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 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);
INSERT INTO gtest24nn (a) VALUES (4); -- ok
@@ -530,6 +569,14 @@ ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
SELECT * FROM gtest27;
ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
+-- test not-null checking during table rewrite
+INSERT INTO gtest27 (a, b) VALUES (NULL, NULL);
+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
+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,
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..01ed9b69b71 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -252,7 +252,7 @@ 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
-);
+); -- fails, user-defined type
--INSERT INTO gtest4 VALUES (1), (6);
--SELECT * FROM gtest4;
@@ -464,9 +464,6 @@ CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) VIRTU
--INSERT INTO gtest24nn (a) VALUES (4); -- ok
--INSERT INTO gtest24nn (a) VALUES (NULL); -- error
--- using user-defined type not yet supported
-CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) VIRTUAL); -- 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);
@@ -806,6 +803,9 @@ 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 gtest32 (
a int primary key,
b int generated always as (a * 2),
--
2.43.0
--Multipart=_Fri__8_Aug_2025_11_51_42_+0900_CP97CmrUzTBhq9M8--
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] Align tests for stored and virtual generated columns
In-Reply-To: <no-message-id-606178@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox