src/test/regress/expected/generated_stored.out - master - githubmirror/postgres

src/test/regress/expected/generated_stored.out
1 724 строки69 KB
Álvaro Herrera
Fix ALTER COLUMN ... DROP EXPRESSION with subpartitions
04 авг 2026, 10:06
04 авг 2026, 10:06fef160d
-- keep these tests aligned with generated_virtual.sql
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);
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 
------------+-------------+----------------+-------------+--------------+-----------------------
 gtest0     | a           |                | NO          | NEVER        | 
 gtest0     | b           |                | YES         | ALWAYS       | 55
 gtest1     | a           |                | NO          | NEVER        | 
 gtest1     | b           |                | YES         | ALWAYS       | (a * 2)
(4 rows)

SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
 table_name | column_name | dependent_column 
------------+-------------+------------------
 gtest1     | a           | b
(1 row)

\d gtest1
                    Table "generated_stored_tests.gtest1"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           | not null | 
 b      | integer |           |          | generated always as (a * 2) stored
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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...
                                                             ^
INSERT INTO gtest1 VALUES (1);
INSERT INTO gtest1 VALUES (2, DEFAULT);  -- ok
INSERT INTO gtest1 VALUES (3, 33);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1 VALUES (3, 33), (4, 44);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT);  -- ok
SELECT * FROM gtest1 ORDER BY a;
 a | b 
---+---
 1 | 2
 2 | 4
 3 | 6
 4 | 8
(4 rows)

SELECT gtest1 FROM gtest1 ORDER BY a;  -- whole-row reference
 gtest1 
--------
 (1,2)
 (2,4)
 (3,6)
 (4,8)
(4 rows)

SELECT a, (SELECT gtest1.b) FROM gtest1 ORDER BY a;  -- sublink
 a | b 
---+---
 1 | 2
 2 | 4
 3 | 6
 4 | 8
(4 rows)

DELETE FROM gtest1 WHERE a >= 3;
UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
UPDATE gtest1 SET b = 11 WHERE a = 1;  -- error
ERROR:  column "b" can only be updated to DEFAULT
DETAIL:  Column "b" is a generated column.
SELECT * FROM gtest1 ORDER BY a;
 a | b 
---+---
 1 | 2
 2 | 4
(2 rows)

SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
 a | b | b2 
---+---+----
 1 | 2 |  4
 2 | 4 |  8
(2 rows)

SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
 a | b 
---+---
 2 | 4
(1 row)

-- test that overflow error happens on write
INSERT INTO gtest1 VALUES (2000000000);
ERROR:  integer out of range
SELECT * FROM gtest1;
 a | b 
---+---
 2 | 4
 1 | 2
(2 rows)

DELETE FROM gtest1 WHERE a = 2000000000;
-- test with joins
CREATE TABLE gtestx (x int, y int);
INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);
SELECT * FROM gtestx, gtest1 WHERE gtestx.y = gtest1.a;
 x  | y | a | b 
----+---+---+---
 11 | 1 | 1 | 2
 22 | 2 | 2 | 4
(2 rows)

DROP TABLE gtestx;
-- test UPDATE/DELETE quals
SELECT * FROM gtest1 ORDER BY a;
 a | b 
---+---
 1 | 2
 2 | 4
(2 rows)

UPDATE gtest1 SET a = 3 WHERE b = 4 RETURNING old.*, new.*;
 a | b | a | b 
---+---+---+---
 2 | 4 | 3 | 6
(1 row)

SELECT * FROM gtest1 ORDER BY a;
 a | b 
---+---
 1 | 2
 3 | 6
(2 rows)

DELETE FROM gtest1 WHERE b = 2;
SELECT * FROM gtest1 ORDER BY a;
 a | b 
---+---
 3 | 6
(1 row)

-- test MERGE
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
);
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
  WHEN MATCHED THEN UPDATE SET f1 = v.f1
  WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200)
  RETURNING merge_action(), old.*, new.*;
 merge_action | id | f1 | f2  | f3 | f4  | id | f1 | f2  | f3 | f4  
--------------+----+----+-----+----+-----+----+----+-----+----+-----
 UPDATE       |  1 |  5 | 100 | 10 | 200 |  1 | 10 | 100 | 20 | 200
 INSERT       |    |    |     |    |     |  2 | 20 | 200 | 40 | 400
(2 rows)

SELECT * FROM gtestm ORDER BY id;
 id | f1 | f2  | f3 | f4  
----+----+-----+----+-----
  1 | 10 | 100 | 20 | 200
  2 | 20 | 200 | 40 | 400
(2 rows)

DROP TABLE gtestm;
CREATE TABLE gtestm (
  a int PRIMARY KEY,
  b int GENERATED ALWAYS AS (a * 2) STORED
);
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 *;
 a  | b  | a  | b  
----+----+----+----
  1 |  2 |  1 |  2
  2 |  4 |  2 |  4
  3 |  6 |  3 |  6
  4 |  8 |  4 |  8
  5 | 10 |  5 | 10
  6 | 12 |  6 | 12
  7 | 14 |  7 | 14
  8 | 16 |  8 | 16
  9 | 18 |  9 | 18
 10 | 20 | 10 | 20
(10 rows)

DROP TABLE gtestm;
-- views
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
SELECT * FROM gtest1v;
 a | b 
---+---
 3 | 6
(1 row)

INSERT INTO gtest1v VALUES (4, 8);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1v VALUES (5, DEFAULT);  -- ok
INSERT INTO gtest1v VALUES (6, 66), (7, 77);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT);  -- ok
ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
INSERT INTO gtest1v VALUES (8, DEFAULT);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT);  -- error
ERROR:  cannot insert a non-DEFAULT value into column "b"
DETAIL:  Column "b" is a generated column.
SELECT * FROM gtest1v;
 a | b  
---+----
 3 |  6
 5 | 10
 6 | 12
 7 | 14
(4 rows)

DELETE FROM gtest1v WHERE a >= 5;
DROP VIEW gtest1v;
-- CTEs
WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo;
 a | b 
---+---
 3 | 6
(1 row)

-- inheritance
CREATE TABLE gtest1_1 () INHERITS (gtest1);
SELECT * FROM gtest1_1;
 a | b 
---+---
(0 rows)

\d gtest1_1
                   Table "generated_stored_tests.gtest1_1"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           | not null | 
 b      | integer |           |          | generated always as (a * 2) stored
Inherits:
    gtest1

INSERT INTO gtest1_1 VALUES (4);
SELECT * FROM gtest1_1;
 a | b 
---+---
 4 | 8
(1 row)

SELECT * FROM gtest1;
 a | b 
---+---
 3 | 6
 4 | 8
(2 rows)

-- 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
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);
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;
-- test inheritance mismatches between parent and child
CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1);  -- error
NOTICE:  merging column "b" with inherited definition
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
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
NOTICE:  merging column "b" with inherited definition
\d+ gtestx
                                        Table "generated_stored_tests.gtestx"
 Column |  Type   | Collation | Nullable |               Default               | Storage | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------
 a      | integer |           | not null |                                     | plain   |              | 
 b      | integer |           |          | generated always as (a * 22) stored | plain   |              | 
 x      | integer |           |          |                                     | plain   |              | 
Not-null constraints:
    "gtest1_a_not_null" NOT NULL "a" (inherited)
Inherits:
    gtest1

INSERT INTO gtestx (a, x) VALUES (11, 22);
SELECT * FROM gtest1;
 a  |  b  
----+-----
  3 |   6
  4 |   8
 11 | 242
(3 rows)

SELECT * FROM gtestx;
 a  |  b  | x  
----+-----+----
 11 | 242 | 22
(1 row)

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);
ALTER TABLE gtestxx_3 INHERIT gtest1;  -- ok
CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
ALTER TABLE gtestxx_4 INHERIT gtest1;  -- ok
-- test multiple inheritance mismatches
CREATE TABLE gtesty (x int, b int DEFAULT 55);
CREATE TABLE gtest1_y () INHERITS (gtest0, 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);
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 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
NOTICE:  merging multiple inherited definitions of column "b"
NOTICE:  moving and merging column "b" with inherited definition
DETAIL:  User-specified column moved to the position of the inherited column.
\d gtest1_y
                   Table "generated_stored_tests.gtest1_y"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           | not null | 
 b      | integer |           |          | generated always as (x + 1) stored
 x      | integer |           |          | 
Inherits:
    gtest1
    gtesty

-- 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);
INSERT INTO gtestc values(42);
TABLE gtestc;
 f1 | f2 
----+----
 42 | 43
(1 row)

UPDATE gtestp SET f1 = f1 * 10;
TABLE gtestc;
 f1  | f2  
-----+-----
 420 | 421
(1 row)

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);
INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
SELECT * FROM gtest3 ORDER BY a;
 a | b 
---+---
 1 | 3
 2 | 6
 3 | 9
   |  
(4 rows)

UPDATE gtest3 SET a = 22 WHERE a = 2;
SELECT * FROM gtest3 ORDER BY a;
 a  | b  
----+----
  1 |  3
  3 |  9
 22 | 66
    |   
(4 rows)

CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED);
INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
SELECT * FROM gtest3a ORDER BY a;
 a |  b  
---+-----
 a | a+a
 b | b+b
 c | c+c
   | 
(4 rows)

UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
SELECT * FROM gtest3a ORDER BY a;
 a  |   b   
----+-------
 a  | a+a
 bb | bb+bb
 c  | c+c
    | 
(4 rows)

-- COPY
TRUNCATE gtest1;
INSERT INTO gtest1 (a) VALUES (1), (2);
COPY gtest1 TO stdout;
1
2
COPY gtest1 (a, b) TO stdout;
ERROR:  column "b" is a generated column
DETAIL:  Generated columns cannot be used in COPY.
COPY gtest1 FROM stdin;
COPY gtest1 (a, b) FROM stdin;
ERROR:  column "b" is a generated column
DETAIL:  Generated columns cannot be used in COPY.
COPY gtest1 FROM stdin WHERE b <> 10;
ERROR:  generated columns are not supported in COPY FROM WHERE conditions
DETAIL:  Column "b" is a generated column.
COPY gtest1 FROM stdin WHERE gtest1 IS NULL;
ERROR:  generated columns are not supported in COPY FROM WHERE conditions
DETAIL:  Column "b" is a generated column.
SELECT * FROM gtest1 ORDER BY a;
 a | b 
---+---
 1 | 2
 2 | 4
 3 | 6
 4 | 8
(4 rows)

TRUNCATE gtest3;
INSERT INTO gtest3 (a) VALUES (1), (2);
COPY gtest3 TO stdout;
1
2
COPY gtest3 (a, b) TO stdout;
ERROR:  column "b" is a generated column
DETAIL:  Generated columns cannot be used in COPY.
COPY gtest3 FROM stdin;
COPY gtest3 (a, b) FROM stdin;
ERROR:  column "b" is a generated column
DETAIL:  Generated columns cannot be used in COPY.
SELECT * FROM gtest3 ORDER BY a;
 a | b  
---+----
 1 |  3
 2 |  6
 3 |  9
 4 | 12
(4 rows)

-- COPY JSON should exclude generated columns, same as text/CSV
COPY gtest1 TO stdout WITH (FORMAT json);
{"a":1}
{"a":2}
{"a":3}
{"a":4}
-- null values
CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
INSERT INTO gtest2 VALUES (1);
SELECT * FROM gtest2;
 a | b 
---+---
 1 |  
(1 row)

-- simple column reference for varlena types
CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED);
INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
INSERT INTO gtest_varlena (a) VALUES(NULL);
SELECT * FROM gtest_varlena ORDER BY a;
          a           |          b           
----------------------+----------------------
 01234567890123456789 | 01234567890123456789
                      | 
(2 rows)

DROP TABLE gtest_varlena;
-- composite types
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
);
INSERT INTO gtest4 VALUES (1), (6);
SELECT * FROM gtest4;
 a |    b    
---+---------
 1 | (2,3)
 6 | (12,18)
(2 rows)

DROP TABLE gtest4;
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
);
INSERT INTO gtest_tableoid VALUES (1), (2);
ALTER TABLE gtest_tableoid ADD COLUMN
  c regclass GENERATED ALWAYS AS (tableoid) STORED;
SELECT * FROM gtest_tableoid;
 a | b |       c        
---+---+----------------
 1 | t | gtest_tableoid
 2 | t | gtest_tableoid
(2 rows)

-- drop column behavior
CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
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
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
ALTER TABLE gtest10 DROP COLUMN b CASCADE;  -- drops c too
NOTICE:  drop cascades to column c of table gtest10
\d gtest10
      Table "generated_stored_tests.gtest10"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
Indexes:
    "gtest10_pkey" PRIMARY KEY, btree (a)

CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
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);
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);
INSERT INTO gtest12 VALUES (1, 10), (2, 20);
GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
SELECT a, b FROM gtest11;  -- not allowed
ERROR:  permission denied for table gtest11
SELECT a, c FROM gtest11;  -- allowed
 a | c  
---+----
 1 | 20
 2 | 40
(2 rows)

SELECT gf1(10);  -- not allowed
ERROR:  permission denied for function gf1
INSERT INTO gtest12 VALUES (3, 30), (4, 40);  -- currently not allowed because of function permissions, should arguably be allowed
ERROR:  permission denied for function gf1
SELECT a, c FROM gtest12;  -- allowed (does not actually invoke the function)
 a | c  
---+----
 1 | 30
 2 | 60
(2 rows)

RESET ROLE;
DROP FUNCTION gf1(int);  -- fail
ERROR:  cannot drop function gf1(integer) because other objects depend on it
DETAIL:  column c of table gtest12 depends on function gf1(integer)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
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));
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"
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);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
ALTER TABLE gtest20a ADD CHECK (b < 50);  -- fails on existing row
ERROR:  check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
-- table rewrite cases
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);
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);
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).
ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int);  -- violates constraint
ERROR:  check constraint "whole_row_check" of relation "gtest20c" is violated by some row
-- index with whole-row reference needs rebuild
CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest20d VALUES (1), (1);
CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS (a * 2::bigint);  -- index rebuild
CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS (a * 3);  -- index rebuild
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED 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);
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 (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
-- virtual generated columns are not physically stored, even when not null
--CREATE TABLE gtest21c (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL NOT NULL, c int NOT NULL);
--INSERT INTO gtest21c (a, c) VALUES (10, 42);
--SELECT a, b, c FROM gtest21c;
--DROP TABLE gtest21c;
-- try adding a virtual generated column to an existing table with tuples,
-- then try adding an atthasmissing column before adding a normal nullable
-- column.
--CREATE TABLE gtest21d (a int NOT NULL);
--INSERT INTO gtest21d (a) VALUES(10);
--ALTER TABLE gtest21d ADD COLUMN b INT GENERATED ALWAYS AS (a * 10) VIRTUAL NOT NULL;
--SELECT * FROM gtest21d ORDER BY a;
--INSERT INTO gtest21d (a) VALUES(20);
--ALTER TABLE gtest21d ADD COLUMN c INT NOT NULL DEFAULT 1234;
--SELECT * FROM gtest21d ORDER BY a;
--ALTER TABLE gtest21d ADD COLUMN d INT;
--INSERT INTO gtest21d (a, c, d) VALUES(30, 12345, 100);
--SELECT * FROM gtest21d ORDER BY a;
--DROP TABLE gtest21d;
-- 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);
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));
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 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;
\d gtest22c
                   Table "generated_stored_tests.gtest22c"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 2) stored
Indexes:
    "gtest22c_b_idx" btree (b)
    "gtest22c_expr_idx" btree ((b * 3))
    "gtest22c_pred_idx" btree (a) WHERE b > 0

INSERT INTO gtest22c VALUES (1), (2), (3);
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
                 QUERY PLAN                  
---------------------------------------------
 Index Scan using gtest22c_b_idx on gtest22c
   Index Cond: (b = 4)
(2 rows)

SELECT * FROM gtest22c WHERE b = 4;
 a | b 
---+---
 2 | 4
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
                   QUERY PLAN                   
------------------------------------------------
 Index Scan using gtest22c_expr_idx on gtest22c
   Index Cond: ((b * 3) = 6)
(2 rows)

SELECT * FROM gtest22c WHERE b * 3 = 6;
 a | b 
---+---
 1 | 2
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
                   QUERY PLAN                   
------------------------------------------------
 Index Scan using gtest22c_pred_idx on gtest22c
   Index Cond: (a = 1)
(2 rows)

SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
 a | b 
---+---
 1 | 2
(1 row)

ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
ANALYZE gtest22c;
EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
                 QUERY PLAN                  
---------------------------------------------
 Index Scan using gtest22c_b_idx on gtest22c
   Index Cond: (b = 8)
(2 rows)

SELECT * FROM gtest22c WHERE b = 8;
 a | b 
---+---
 2 | 8
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
                   QUERY PLAN                   
------------------------------------------------
 Index Scan using gtest22c_expr_idx on gtest22c
   Index Cond: ((b * 3) = 12)
(2 rows)

SELECT * FROM gtest22c WHERE b * 3 = 12;
 a | b 
---+---
 1 | 4
(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
                   QUERY PLAN                   
------------------------------------------------
 Index Scan using gtest22c_pred_idx on gtest22c
   Index Cond: (a = 1)
(2 rows)

SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
 a | b 
---+---
 1 | 4
(1 row)

RESET enable_seqscan;
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
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
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));
\d gtest23b
                   Table "generated_stored_tests.gtest23b"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           | not null | 
 b      | integer |           |          | generated always as (a * 2) stored
Indexes:
    "gtest23b_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
    "gtest23b_b_fkey" FOREIGN KEY (b) REFERENCES gtest23a(x)

INSERT INTO gtest23b VALUES (1);  -- ok
INSERT INTO gtest23b VALUES (5);  -- error
ERROR:  insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
DETAIL:  Key (b)=(10) is not present in table "gtest23a".
ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
ERROR:  insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
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));
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
INSERT INTO gtest23q VALUES (2, 5);  -- error
ERROR:  insert or update on table "gtest23q" violates foreign key constraint "gtest23q_b_fkey"
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);
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);
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
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);
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
) 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);
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_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
) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
    f3 DEFAULT 42  -- error
) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
ERROR:  column "f3" inherits from generated column but specifies default
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');
ERROR:  identity columns are not supported on partitions
CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
    f3 GENERATED ALWAYS AS (f2 * 2) VIRTUAL  -- 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.
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
ERROR:  column "f3" in child table must be a generated column
DROP TABLE gtest_child3;
CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint DEFAULT 42);
ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
ERROR:  column "f3" in child table must be a generated column
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
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);
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);
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"
 Column |  Type  | Collation | Nullable |               Default               
--------+--------+-----------+----------+-------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 2) stored
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')

\d gtest_child2
                  Table "generated_stored_tests.gtest_child2"
 Column |  Type  | Collation | Nullable |               Default                
--------+--------+-----------+----------+--------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 22) stored
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')

\d gtest_child3
                  Table "generated_stored_tests.gtest_child3"
 Column |  Type  | Collation | Nullable |               Default                
--------+--------+-----------+----------+--------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 33) stored
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')

INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
   tableoid   |     f1     | f2 | f3 
--------------+------------+----+----
 gtest_child  | 07-15-2016 |  1 |  2
 gtest_child  | 07-15-2016 |  2 |  4
 gtest_child2 | 08-15-2016 |  3 | 66
(3 rows)

SELECT tableoid::regclass, * FROM gtest_child ORDER BY 1, 2, 3;
  tableoid   |     f1     | f2 | f3 
-------------+------------+----+----
 gtest_child | 07-15-2016 |  1 |  2
 gtest_child | 07-15-2016 |  2 |  4
(2 rows)

SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3;
   tableoid   |     f1     | f2 | f3 
--------------+------------+----+----
 gtest_child2 | 08-15-2016 |  3 | 66
(1 row)

SELECT tableoid::regclass, * FROM gtest_child3 ORDER BY 1, 2, 3;
 tableoid | f1 | f2 | f3 
----------+----+----+----
(0 rows)

UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
   tableoid   |     f1     | f2 | f3 
--------------+------------+----+----
 gtest_child  | 07-15-2016 |  2 |  4
 gtest_child2 | 08-15-2016 |  3 | 66
 gtest_child3 | 09-13-2016 |  1 | 33
(3 rows)

-- alter only parent's and one child's generation expression
ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
\d gtest_parent
           Partitioned table "generated_stored_tests.gtest_parent"
 Column |  Type  | Collation | Nullable |               Default               
--------+--------+-----------+----------+-------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 4) stored
Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)

\d gtest_child
                  Table "generated_stored_tests.gtest_child"
 Column |  Type  | Collation | Nullable |               Default                
--------+--------+-----------+----------+--------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 10) stored
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')

\d gtest_child2
                  Table "generated_stored_tests.gtest_child2"
 Column |  Type  | Collation | Nullable |               Default                
--------+--------+-----------+----------+--------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 22) stored
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')

\d gtest_child3
                  Table "generated_stored_tests.gtest_child3"
 Column |  Type  | Collation | Nullable |               Default                
--------+--------+-----------+----------+--------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 33) stored
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')

SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
   tableoid   |     f1     | f2 | f3 
--------------+------------+----+----
 gtest_child  | 07-15-2016 |  2 | 20
 gtest_child2 | 08-15-2016 |  3 | 66
 gtest_child3 | 09-13-2016 |  1 | 33
(3 rows)

-- alter generation expression of parent and all its children altogether
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_parent
           Partitioned table "generated_stored_tests.gtest_parent"
 Column |  Type  | Collation | Nullable |               Default               
--------+--------+-----------+----------+-------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 2) stored
Partition key: RANGE (f1)
Number of partitions: 3 (Use \d+ to list them.)

\d gtest_child
                  Table "generated_stored_tests.gtest_child"
 Column |  Type  | Collation | Nullable |               Default               
--------+--------+-----------+----------+-------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 2) stored
Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')

\d gtest_child2
                 Table "generated_stored_tests.gtest_child2"
 Column |  Type  | Collation | Nullable |               Default               
--------+--------+-----------+----------+-------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 2) stored
Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')

\d gtest_child3
                 Table "generated_stored_tests.gtest_child3"
 Column |  Type  | Collation | Nullable |               Default               
--------+--------+-----------+----------+-------------------------------------
 f1     | date   |           | not null | 
 f2     | bigint |           |          | 
 f3     | bigint |           |          | generated always as (f2 * 2) stored
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')

SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
   tableoid   |     f1     | f2 | f3 
--------------+------------+----+----
 gtest_child  | 07-15-2016 |  2 |  4
 gtest_child2 | 08-15-2016 |  3 |  6
 gtest_child3 | 09-13-2016 |  1 |  2
(3 rows)

-- 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);
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));
ERROR:  cannot use generated column in partition key
LINE 1: ...ERATED 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));
ERROR:  cannot use generated column in partition key
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
                                                             ^
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 ((gtest_part_key));
ERROR:  cannot use generated column in partition key
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par...
                                                             ^
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 ((gtest_part_key is not null));
ERROR:  cannot use generated column in partition key
LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((gtest_par...
                                                             ^
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);
SELECT * FROM gtest25 ORDER BY a;
 a | b  
---+----
 3 |  9
 4 | 12
(2 rows)

ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED;  -- 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
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;
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;
SELECT * FROM gtest25 ORDER BY a;
 a | b  | c  |  x  |  d  |  y  
---+----+----+-----+-----+-----
 3 |  9 | 42 | 168 | 101 | 404
 4 | 12 | 42 | 168 | 101 | 404
(2 rows)

\d gtest25
                                 Table "generated_stored_tests.gtest25"
 Column |       Type       | Collation | Nullable |                       Default                        
--------+------------------+-----------+----------+------------------------------------------------------
 a      | integer          |           | not null | 
 b      | integer          |           |          | generated always as (a * 3) stored
 c      | integer          |           |          | 42
 x      | integer          |           |          | generated always as (c * 4) stored
 d      | double precision |           |          | 101
 y      | double precision |           |          | generated always as (d * 4::double precision) stored
Indexes:
    "gtest25_pkey" PRIMARY KEY, btree (a)

-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
    a int,
    b int,
    x int GENERATED ALWAYS AS ((a + b) * 2) STORED
);
INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
ALTER TABLE gtest27 ALTER COLUMN a TYPE text;  -- error
ERROR:  cannot alter type of a column used by a generated column
DETAIL:  Column "a" is used by generated column "x".
ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
\d gtest27
                        Table "generated_stored_tests.gtest27"
 Column |  Type   | Collation | Nullable |                  Default                   
--------+---------+-----------+----------+--------------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | 
 x      | numeric |           |          | generated always as (((a + b) * 2)) stored

SELECT * FROM gtest27;
 a | b  | x  
---+----+----
 3 |  7 | 20
 4 | 11 | 30
(2 rows)

ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0;  -- error
ERROR:  cannot specify USING when altering type of generated column
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
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,
  ALTER COLUMN a TYPE bigint,
  ALTER COLUMN b TYPE bigint,
  ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
\d gtest27
                      Table "generated_stored_tests.gtest27"
 Column |  Type  | Collation | Nullable |                 Default                  
--------+--------+-----------+----------+------------------------------------------
 a      | bigint |           |          | 
 b      | bigint |           |          | 
 x      | bigint |           |          | generated always as ((a + b) * 2) stored

-- Ideally you could just do this, but not today (and should x change type?):
ALTER TABLE gtest27
  ALTER COLUMN a TYPE float8,
  ALTER COLUMN b TYPE float8;  -- error
ERROR:  cannot alter type of a column used by a generated column
DETAIL:  Column "a" is used by generated column "x".
\d gtest27
                      Table "generated_stored_tests.gtest27"
 Column |  Type  | Collation | Nullable |                 Default                  
--------+--------+-----------+----------+------------------------------------------
 a      | bigint |           |          | 
 b      | bigint |           |          | 
 x      | bigint |           |          | generated always as ((a + b) * 2) stored

SELECT * FROM gtest27;
 a | b  | x  
---+----+----
 3 |  7 | 20
 4 | 11 | 30
(2 rows)

-- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
CREATE TABLE gtest29 (
    a int,
    b int GENERATED ALWAYS AS (a * 2) STORED
);
INSERT INTO gtest29 (a) VALUES (3), (4);
SELECT * FROM gtest29;
 a | b 
---+---
 3 | 6
 4 | 8
(2 rows)

\d gtest29
                    Table "generated_stored_tests.gtest29"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 2) stored

ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3);  -- error
ERROR:  column "a" of relation "gtest29" is not a generated column
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION;  -- error
ERROR:  column "a" of relation "gtest29" is not a generated column
ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS;  -- notice
NOTICE:  column "a" of relation "gtest29" is not a generated column, skipping
-- Change the expression
ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
SELECT * FROM gtest29;
 a | b  
---+----
 3 |  9
 4 | 12
(2 rows)

\d gtest29
                    Table "generated_stored_tests.gtest29"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 3) stored

ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
INSERT INTO gtest29 (a) VALUES (5);
INSERT INTO gtest29 (a, b) VALUES (6, 66);
SELECT * FROM gtest29;
 a | b  
---+----
 3 |  9
 4 | 12
 5 |   
 6 | 66
(4 rows)

\d gtest29
      Table "generated_stored_tests.gtest29"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 

-- check that dependencies between columns have also been removed
ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
\d gtest29
      Table "generated_stored_tests.gtest29"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 b      | integer |           |          | 

-- with inheritance
CREATE TABLE gtest30 (
    a int,
    b int GENERATED ALWAYS AS (a * 2) STORED
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
\d gtest30
      Table "generated_stored_tests.gtest30"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
Number of child tables: 1 (Use \d+ to list them.)

\d gtest30_1
     Table "generated_stored_tests.gtest30_1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
Inherits:
    gtest30

DROP TABLE gtest30 CASCADE;
NOTICE:  drop cascades to table gtest30_1
CREATE TABLE gtest30 (
    a int,
    b int GENERATED ALWAYS AS (a * 2) STORED
);
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION;  -- error
ERROR:  ALTER TABLE / DROP EXPRESSION must be applied to child tables too
\d gtest30
                    Table "generated_stored_tests.gtest30"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 2) stored
Number of child tables: 1 (Use \d+ to list them.)

\d gtest30_1
                   Table "generated_stored_tests.gtest30_1"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 a      | integer |           |          | 
 b      | integer |           |          | generated always as (a * 2) stored
Inherits:
    gtest30

ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION;  -- error
ERROR:  cannot drop generation expression from inherited column
BEGIN;
CREATE TABLE gtest30_1_1 () INHERITS (gtest30_1);
ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
\d gtest30_1_1
    Table "generated_stored_tests.gtest30_1_1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
Inherits:
    gtest30_1

ROLLBACK;
-- test drop expression with subpartitions
CREATE TABLE gtest_root (a int, b int, c int GENERATED ALWAYS AS (a + b) STORED) PARTITION BY LIST (a);
CREATE TABLE gtest_node PARTITION OF gtest_root FOR VALUES IN (1) PARTITION BY LIST (b);
CREATE TABLE gtest_leaf PARTITION OF gtest_node FOR VALUES IN (1);
ALTER TABLE gtest_node ALTER COLUMN c DROP EXPRESSION;  -- fails
ERROR:  cannot drop generation expression from inherited column
ALTER TABLE ONLY gtest_root ALTER COLUMN c DROP EXPRESSION;  -- fails
ERROR:  ALTER TABLE / DROP EXPRESSION must be applied to child tables too
ALTER TABLE gtest_root ALTER COLUMN c DROP EXPRESSION;
\d gtest_(root|node|leaf)
     Table "generated_stored_tests.gtest_leaf"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | integer |           |          | 
Partition of: gtest_node FOR VALUES IN (1)

Partitioned table "generated_stored_tests.gtest_node"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | integer |           |          | 
Partition of: gtest_root FOR VALUES IN (1)
Partition key: LIST (b)
Number of partitions: 1 (Use \d+ to list them.)

Partitioned table "generated_stored_tests.gtest_root"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | integer |           |          | 
 c      | integer |           |          | 
Partition key: LIST (a)
Number of partitions: 1 (Use \d+ to list them.)

DROP TABLE gtest_root;
-- composite type dependencies
CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, 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
-- bug #18970: these cases are unsupported, but make sure they fail cleanly
ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL);
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1');
ERROR:  cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
ALTER TABLE gtest31_2 DROP CONSTRAINT cc;
CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2;
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2');
ERROR:  cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
DROP STATISTICS gtest31_2_stat;
CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b));
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_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
DROP TABLE gtest31_1, gtest31_2;
-- triggers
CREATE TABLE gtest26 (
    a int PRIMARY KEY,
    b int GENERATED ALWAYS AS (a * 2) STORED
);
CREATE FUNCTION gtest_trigger_func() RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  IF tg_op IN ('DELETE', 'UPDATE') THEN
    RAISE INFO '%: %: old = %', TG_NAME, TG_WHEN, OLD;
  END IF;
  IF tg_op IN ('INSERT', 'UPDATE') THEN
    RAISE INFO '%: %: new = %', TG_NAME, TG_WHEN, NEW;
  END IF;
  IF tg_op = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END
$$;
CREATE TRIGGER gtest1 BEFORE DELETE OR UPDATE ON gtest26
  FOR EACH ROW
  WHEN (OLD.b < 0)  -- ok
  EXECUTE PROCEDURE gtest_trigger_func();
CREATE TRIGGER gtest2a BEFORE INSERT OR UPDATE ON gtest26
  FOR EACH ROW
  WHEN (NEW.b < 0)  -- error
  EXECUTE PROCEDURE gtest_trigger_func();
ERROR:  BEFORE trigger's WHEN condition cannot reference NEW generated columns
LINE 3:   WHEN (NEW.b < 0)  -- error
                ^
DETAIL:  Column "b" is a generated column.
CREATE TRIGGER gtest2b BEFORE INSERT OR UPDATE ON gtest26
  FOR EACH ROW
  WHEN (NEW.* IS NOT NULL)  -- error
  EXECUTE PROCEDURE gtest_trigger_func();
ERROR:  BEFORE trigger's WHEN condition cannot reference NEW generated columns
LINE 3:   WHEN (NEW.* IS NOT NULL)  -- error
                ^
DETAIL:  A whole-row reference is used and the table contains generated columns.
CREATE TRIGGER gtest2 BEFORE INSERT ON gtest26
  FOR EACH ROW
  WHEN (NEW.a < 0)
  EXECUTE PROCEDURE gtest_trigger_func();
CREATE TRIGGER gtest3 AFTER DELETE OR UPDATE ON gtest26
  FOR EACH ROW
  WHEN (OLD.b < 0)  -- ok
  EXECUTE PROCEDURE gtest_trigger_func();
CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26
  FOR EACH ROW
  WHEN (NEW.b < 0)  -- ok
  EXECUTE PROCEDURE gtest_trigger_func();
INSERT INTO gtest26 (a) VALUES (-2), (0), (3);
INFO:  gtest2: BEFORE: new = (-2,)
INFO:  gtest4: AFTER: new = (-2,-4)
SELECT * FROM gtest26 ORDER BY a;
 a  | b  
----+----
 -2 | -4
  0 |  0
  3 |  6
(3 rows)

UPDATE gtest26 SET a = a * -2;
INFO:  gtest1: BEFORE: old = (-2,-4)
INFO:  gtest1: BEFORE: new = (4,)
INFO:  gtest3: AFTER: old = (-2,-4)
INFO:  gtest3: AFTER: new = (4,8)
INFO:  gtest4: AFTER: old = (3,6)
INFO:  gtest4: AFTER: new = (-6,-12)
SELECT * FROM gtest26 ORDER BY a;
 a  |  b  
----+-----
 -6 | -12
  0 |   0
  4 |   8
(3 rows)

DELETE FROM gtest26 WHERE a = -6;
INFO:  gtest1: BEFORE: old = (-6,-12)
INFO:  gtest3: AFTER: old = (-6,-12)
SELECT * FROM gtest26 ORDER BY a;
 a | b 
---+---
 0 | 0
 4 | 8
(2 rows)

DROP TRIGGER gtest1 ON gtest26;
DROP TRIGGER gtest2 ON gtest26;
DROP TRIGGER gtest3 ON gtest26;
-- Check that an UPDATE of "a" fires the trigger for UPDATE OF b, per
-- SQL standard.
CREATE FUNCTION gtest_trigger_func3() RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'OK';
  RETURN NEW;
END
$$;
CREATE TRIGGER gtest11 BEFORE UPDATE OF b ON gtest26
  FOR EACH ROW
  EXECUTE PROCEDURE gtest_trigger_func3();
UPDATE gtest26 SET a = 1 WHERE a = 0;
NOTICE:  OK
DROP TRIGGER gtest11 ON gtest26;
TRUNCATE gtest26;
-- check that modifications of generated columns in triggers do
-- not get propagated
CREATE FUNCTION gtest_trigger_func4() RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  NEW.a = 10;
  NEW.b = 300;
  RETURN NEW;
END;
$$;
CREATE TRIGGER gtest12_01 BEFORE INSERT OR UPDATE ON gtest26
  FOR EACH ROW
  EXECUTE PROCEDURE gtest_trigger_func();
CREATE TRIGGER gtest12_02 BEFORE INSERT OR UPDATE ON gtest26
  FOR EACH ROW
  EXECUTE PROCEDURE gtest_trigger_func4();
CREATE TRIGGER gtest12_03 BEFORE INSERT OR UPDATE ON gtest26
  FOR EACH ROW
  EXECUTE PROCEDURE gtest_trigger_func();
INSERT INTO gtest26 (a) VALUES (1);
INFO:  gtest12_01: BEFORE: new = (1,)
INFO:  gtest12_03: BEFORE: new = (10,300)
SELECT * FROM gtest26 ORDER BY a;
 a  | b  
----+----
 10 | 20
(1 row)

UPDATE gtest26 SET a = 11 WHERE a = 10;
INFO:  gtest12_01: BEFORE: old = (10,20)
INFO:  gtest12_01: BEFORE: new = (11,)
INFO:  gtest12_03: BEFORE: old = (10,20)
INFO:  gtest12_03: BEFORE: new = (10,)
SELECT * FROM gtest26 ORDER BY a;
 a  | b  
----+----
 10 | 20
(1 row)

-- LIKE INCLUDING GENERATED and dropped column handling
CREATE TABLE gtest28a (
  a int,
  b int,
  c int,
  x int GENERATED ALWAYS AS (b * 2) STORED
);
ALTER TABLE gtest28a DROP COLUMN a;
CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
\d gtest28*
                   Table "generated_stored_tests.gtest28a"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 b      | integer |           |          | 
 c      | integer |           |          | 
 x      | integer |           |          | generated always as (b * 2) stored

                   Table "generated_stored_tests.gtest28b"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 b      | integer |           |          | 
 c      | integer |           |          | 
 x      | integer |           |          | generated always as (b * 2) stored

-- rule actions referring to generated columns:
-- NEW.b in a rule action should reflect the generated column's new value
CREATE TABLE gtest_rule (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
CREATE TABLE gtest_rule_log (op text, old_b int, new_b int);
CREATE RULE gtest_rule_upd AS ON UPDATE TO gtest_rule
  DO ALSO INSERT INTO gtest_rule_log VALUES ('UPD', OLD.b, NEW.b);
CREATE RULE gtest_rule_ins AS ON INSERT TO gtest_rule
  DO ALSO INSERT INTO gtest_rule_log VALUES ('INS', NULL, NEW.b);
INSERT INTO gtest_rule (a) VALUES (1);
UPDATE gtest_rule SET a = 10;
UPDATE gtest_rule SET a = (SELECT max(b) FROM gtest_rule);
SELECT * FROM gtest_rule_log;
 op  | old_b | new_b 
-----+-------+-------
 INS |       |     2
 UPD |     2 |    20
 UPD |    20 |    40
(3 rows)

DROP RULE gtest_rule_upd ON gtest_rule;
DROP RULE gtest_rule_ins ON gtest_rule;
DROP TABLE gtest_rule_log;
-- rule quals referring to generated columns:
-- NEW.b in the rule qual should reflect the generated column's new value
CREATE RULE gtest_rule_qual AS ON UPDATE TO gtest_rule WHERE NEW.b > 100
  DO INSTEAD NOTHING;
UPDATE gtest_rule SET a = 100;
SELECT * FROM gtest_rule;
 a  | b  
----+----
 20 | 40
(1 row)

DROP TABLE gtest_rule;
-- sanity check of system catalog
SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
 attrelid | attname | attgenerated 
----------+---------+--------------
(0 rows)