----------+---------+--------------

(0 rows)

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 WHEREtable_name LIKE 'gtest_' ORDER BY 1, 2;

SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHEREtable_schema = 'generated_stored_tests' ORDER BY 1, 2;

table_name | column_name | column_default | is_nullable | is_generated | generation_expression

------------+-------------+----------------+-------------+--------------+-----------------------

gtest0 | a | | NO | NEVER |

gtest1 | b | | YES | ALWAYS | (a * 2)

(4 rows)

SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage ORDER BY 1, 2, 3;

SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usageWHERE table_schema = 'generated_stored_tests'ORDER BY 1, 2, 3;

table_name | column_name | dependent_column

------------+-------------+------------------

gtest1 | a | b

(1 row)

\d gtest1

Table "public.gtest1"

Table "generated_stored_tests.gtest1"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | not null |

(0 rows)

\d gtest1_1

Table "public.gtest1_1"

Table "generated_stored_tests.gtest1_1"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | not null |

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 "public.gtestx"

Table "generated_stored_tests.gtestx"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

--------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------

a | integer | | not null | | plain | |

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 "public.gtest1_y"

Table "generated_stored_tests.gtest1_y"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | not null |

ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too

NOTICE: drop cascades to column c of table gtest10

\d gtest10

Table "public.gtest10"

Table "generated_stored_tests.gtest10"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

a | integer | | not null |

CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));

CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;

\d gtest22c

Table "public.gtest22c"

Table "generated_stored_tests.gtest22c"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | |

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 "public.gtest23b"

Table "generated_stored_tests.gtest23b"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | not null |

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 "public.gtest_child"

Table "generated_stored_tests.gtest_child"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+-------------------------------------

f1 | date | | not null |

Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')

\d gtest_child2

Table "public.gtest_child2"

Table "generated_stored_tests.gtest_child2"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+--------------------------------------

f1 | date | | not null |

Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')

\d gtest_child3

Table "public.gtest_child3"

Table "generated_stored_tests.gtest_child3"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+--------------------------------------

f1 | date | | not null |

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 "public.gtest_parent"

Partitioned table "generated_stored_tests.gtest_parent"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+-------------------------------------

f1 | date | | not null |

Number of partitions: 3 (Use \d+ to list them.)

\d gtest_child

Table "public.gtest_child"

Table "generated_stored_tests.gtest_child"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+--------------------------------------

f1 | date | | not null |

Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')

\d gtest_child2

Table "public.gtest_child2"

Table "generated_stored_tests.gtest_child2"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+--------------------------------------

f1 | date | | not null |

Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')

\d gtest_child3

Table "public.gtest_child3"

Table "generated_stored_tests.gtest_child3"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+--------------------------------------

f1 | date | | not null |

-- 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 "public.gtest_parent"

Partitioned table "generated_stored_tests.gtest_parent"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+-------------------------------------

f1 | date | | not null |

Number of partitions: 3 (Use \d+ to list them.)

\d gtest_child

Table "public.gtest_child"

Table "generated_stored_tests.gtest_child"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+-------------------------------------

f1 | date | | not null |

Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')

\d gtest_child2

Table "public.gtest_child2"

Table "generated_stored_tests.gtest_child2"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+-------------------------------------

f1 | date | | not null |

Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')

\d gtest_child3

Table "public.gtest_child3"

Table "generated_stored_tests.gtest_child3"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+-------------------------------------

f1 | date | | not null |

(2 rows)

\d gtest25

Table "public.gtest25"

Table "generated_stored_tests.gtest25"

Column | Type | Collation | Nullable | Default

--------+------------------+-----------+----------+------------------------------------------------------

a | integer | | not null |

DETAIL: Column "a" is used by generated column "x".

ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;

\d gtest27

Table "public.gtest27"

Table "generated_stored_tests.gtest27"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+--------------------------------------------

a | integer | | |

ALTER COLUMN b TYPE bigint,

ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;

\d gtest27

Table "public.gtest27"

Table "generated_stored_tests.gtest27"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+------------------------------------------

a | bigint | | |

ERROR: cannot alter type of a column used by a generated column

DETAIL: Column "a" is used by generated column "x".

\d gtest27

Table "public.gtest27"

Table "generated_stored_tests.gtest27"

Column | Type | Collation | Nullable | Default

--------+--------+-----------+----------+------------------------------------------

a | bigint | | |

(2 rows)

\d gtest29

Table "public.gtest29"

Table "generated_stored_tests.gtest29"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | |

(2 rows)

\d gtest29

Table "public.gtest29"

Table "generated_stored_tests.gtest29"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | |

(4 rows)

\d gtest29

Table "public.gtest29"

Table "generated_stored_tests.gtest29"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

a | integer | | |

-- check that dependencies between columns have also been removed

ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b

\d gtest29

Table "public.gtest29"

Table "generated_stored_tests.gtest29"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

b | integer | | |

CREATE TABLE gtest30_1 () INHERITS (gtest30);

ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;

\d gtest30

Table "public.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 "public.gtest30_1"

Table "generated_stored_tests.gtest30_1"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

a | integer | | |

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 "public.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 "public.gtest30_1"

Table "generated_stored_tests.gtest30_1"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

a | integer | | |

ALTER TABLE gtest28a DROP COLUMN a;

CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);

\d gtest28*

Table "public.gtest28a"

Table "generated_stored_tests.gtest28a"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

b | integer | | |

c | integer | | |

x | integer | | | generated always as (b * 2) stored

Table "public.gtest28b"

Table "generated_stored_tests.gtest28b"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------

b | integer | | |

SELECT attrelid, attname, attgeneratedFROM pg_attributeWHERE attgenerated NOTIN ('','s');

CREATESCHEMAgenerated_stored_tests;

GRANT USAGEON SCHEMA generated_stored_tests TO PUBLIC;

SET search_path= generated_stored_tests;

CREATETABLEgtest0 (aintPRIMARY KEY, bint GENERATED ALWAYSAS (55) STORED);

CREATETABLEgtest1 (aintPRIMARY KEY, bint GENERATED ALWAYSAS (a*2) STORED);

SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expressionFROMinformation_schema.columnsWHEREtable_nameLIKE'gtest_'ORDER BY1,2;

SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expressionFROMinformation_schema.columnsWHEREtable_schema='generated_stored_tests'ORDER BY1,2;

SELECT table_name, column_name, dependent_columnFROMinformation_schema.column_column_usageORDER BY1,2,3;

SELECT table_name, column_name, dependent_columnFROMinformation_schema.column_column_usageWHERE table_schema='generated_stored_tests'ORDER BY1,2,3;

\d gtest1