1 files changed, 72 insertions, 18 deletions
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index dc09c85938e..26bbe1e9c31 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -664,28 +664,73 @@ 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 (currently not supported)
+-- not-null constraints
 CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
-ERROR:  not-null constraints are not supported on virtual generated columns
-LINE 1: ... b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
-                                                             ^
---INSERT INTO gtest21a (a) VALUES (1);  -- ok
---INSERT INTO gtest21a (a) VALUES (0);  -- violates constraint
+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);  -- error
-ERROR:  not-null constraints are not supported on virtual generated columns
+CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL, 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).
+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)) VIRTUAL);
-ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;  -- error
-ERROR:  not-null constraints are not supported on virtual generated columns
+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 PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL);
+CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL);
 ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
-ERROR:  not-null constraints are not supported on virtual generated columns
-DETAIL:  Column "b" of relation "gtest21b" is a virtual generated column.
---INSERT INTO gtest21b (a) VALUES (1);  -- ok
---INSERT INTO gtest21b (a) VALUES (0);  -- violates constraint
+INSERT INTO gtest21b (a) VALUES (1);  -- ok
+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, virtual).
+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, virtual).
 ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
---INSERT INTO gtest21b (a) VALUES (0);  -- ok now
+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)) VIRTUAL 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, virtual).
+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, virtual).
+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)) VIRTUAL;  -- 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
+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
 -- index constraints
 CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
 ERROR:  unique constraints on virtual generated columns are not supported
@@ -693,7 +738,7 @@ ERROR:  unique constraints on virtual generated columns are not supported
 --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));
-ERROR:  not-null constraints are not supported on virtual generated columns
+ERROR:  primary keys on virtual generated columns are not supported
 --INSERT INTO gtest22b VALUES (2);
 --INSERT INTO gtest22b VALUES (2);
 -- indexes
@@ -738,7 +783,7 @@ ERROR:  foreign key constraints on virtual generated columns are not supported
 --DROP TABLE gtest23b;
 --DROP TABLE gtest23a;
 CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
-ERROR:  not-null constraints are not supported on virtual generated columns
+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));
 ERROR:  relation "gtest23p" does not exist
@@ -1056,6 +1101,15 @@ LINE 1: ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0...
 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
+-- 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) VIRTUAL 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,