CREATE SCHEMA generated_test; SET SESSION search_path = 'generated_test'; CREATE EXTENSION orioledb; CREATE TABLE o_test_generated ( a int, b int GENERATED ALWAYS AS (a * 2) STORED ) USING orioledb; INSERT INTO o_test_generated VALUES (1), (2); SELECT * FROM o_test_generated; a | b ---+--- 1 | 2 2 | 4 (2 rows) CREATE TABLE o_test_generated_like ( LIKE o_test_generated INCLUDING GENERATED ) USING orioledb; INSERT INTO o_test_generated_like VALUES (5), (6); SELECT * FROM o_test_generated_like; a | b ---+---- 5 | 10 6 | 12 (2 rows) CREATE TABLE o_test_drop_expression ( a int, b int GENERATED ALWAYS AS (a * 2) STORED ) USING orioledb; INSERT INTO o_test_drop_expression VALUES (1); INSERT INTO o_test_drop_expression VALUES (2); SELECT * FROM o_test_drop_expression; a | b ---+--- 1 | 2 2 | 4 (2 rows) ALTER TABLE o_test_drop_expression ALTER COLUMN a DROP EXPRESSION; ERROR: column "a" of relation "o_test_drop_expression" is not a stored generated column ALTER TABLE o_test_drop_expression ALTER COLUMN b DROP EXPRESSION; INSERT INTO o_test_drop_expression VALUES (3); SELECT * FROM o_test_drop_expression; a | b ---+--- 1 | 2 2 | 4 3 | (3 rows) CREATE TABLE o_test_add_identity_exist ( a SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 7 INCREMENT BY 5), b TEXT ) USING orioledb; ALTER TABLE o_test_add_identity_exist ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; ERROR: column "a" of relation "o_test_add_identity_exist" is already an identity column INSERT INTO o_test_add_identity_exist (b) VALUES ('a'), ('b'), ('c'); SELECT * FROM o_test_add_identity_exist; a | b ----+--- 7 | a 12 | b 17 | c (3 rows) CREATE TABLE o_test_add_identity ( a SMALLINT, b TEXT ) USING orioledb; ALTER TABLE o_test_add_identity ALTER COLUMN a SET NOT NULL, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; INSERT INTO o_test_add_identity (b) VALUES ('a'), ('b'), ('c'); SELECT * FROM o_test_add_identity; a | b ---+--- 1 | a 2 | b 3 | c (3 rows) ALTER TABLE o_test_add_identity ALTER COLUMN a RESTART WITH 10; INSERT INTO o_test_add_identity (b) VALUES ('A'), ('B'), ('C'); SELECT * FROM o_test_add_identity; a | b ----+--- 1 | a 2 | b 3 | c 10 | A 11 | B 12 | C (6 rows) ALTER TABLE o_test_add_identity ALTER COLUMN a DROP IDENTITY, ALTER COLUMN a DROP NOT NULL; INSERT INTO o_test_add_identity (b) VALUES ('X'), ('Y'), ('Z'); SELECT * FROM o_test_add_identity; a | b ----+--- 1 | a 2 | b 3 | c 10 | A 11 | B 12 | C | X | Y | Z (9 rows) CREATE TABLE o_test_generated_null ( val_1 text, val_2 int GENERATED ALWAYS AS (1 + COALESCE(val_3::text, '0')::int) STORED, val_3 int DEFAULT 5 ) USING orioledb; \d+ o_test_generated_null Table "generated_test.o_test_generated_null" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+----------------------------------------------------------------------------+----------+--------------+------------- val_1 | text | | | | extended | | val_2 | integer | | | generated always as (1 + COALESCE(val_3::text, '0'::text)::integer) stored | plain | | val_3 | integer | | | 5 | plain | | INSERT INTO o_test_generated_null (val_1, val_3) VALUES (1, NULL); INSERT INTO o_test_generated_null (val_1, val_3) VALUES (NULL, NULL); SELECT orioledb_tbl_structure('o_test_generated_null'::regclass, 'nue'); orioledb_tbl_structure --------------------------------------------------------------------- Index ctid_primary contents + Page 0: level = 0, maxKeyLen = 8, nVacatedBytes = 0 + state = free, datoid equal, relnode equal, ix_type = primary, dirty+ Leftmost, Rightmost + Chunk 0: offset = 0, location = 256, hikey location = 64 + Item 0: offset = 264, tuple = ('(0,1)', '1', '1', null) + Item 1: offset = 312, tuple = ('(0,2)', null, '1', null) + + Index toast: not loaded + (1 row) SELECT * FROM o_test_generated_null; val_1 | val_2 | val_3 -------+-------+------- 1 | 1 | | 1 | (2 rows) CREATE TABLE o_test_tableoid ( val_1 int PRIMARY KEY, val_2 bool GENERATED ALWAYS AS (tableoid = 'o_test_tableoid'::regclass) STORED ) USING orioledb; INSERT INTO o_test_tableoid VALUES (1), (2); ALTER TABLE o_test_tableoid ADD COLUMN val_3 regclass GENERATED ALWAYS AS (tableoid) STORED; SELECT * FROM o_test_tableoid; val_1 | val_2 | val_3 -------+-------+----------------- 1 | t | o_test_tableoid 2 | t | o_test_tableoid (2 rows) CREATE TABLE o_test_generated_alter_type ( val_1 int, val_3 int GENERATED ALWAYS AS (val_1 * 2) STORED ) USING orioledb; INSERT INTO o_test_generated_alter_type (val_1) VALUES (1), (3); SELECT * FROM o_test_generated_alter_type; val_1 | val_3 -------+------- 1 | 2 3 | 6 (2 rows) SELECT orioledb_tbl_structure('o_test_generated_alter_type'::regclass, 'nue'); orioledb_tbl_structure --------------------------------------------------------------------- Index ctid_primary contents + Page 0: level = 0, maxKeyLen = 8, nVacatedBytes = 0 + state = free, datoid equal, relnode equal, ix_type = primary, dirty+ Leftmost, Rightmost + Chunk 0: offset = 0, location = 256, hikey location = 64 + Item 0: offset = 264, tuple = ('(0,1)', '1', '2') + Item 1: offset = 296, tuple = ('(0,2)', '3', '6') + + Index toast: not loaded + (1 row) ALTER TABLE o_test_generated_alter_type ALTER COLUMN val_3 TYPE numeric; SELECT * FROM o_test_generated_alter_type; val_1 | val_3 -------+------- 1 | 2 3 | 6 (2 rows) DROP EXTENSION orioledb CASCADE; NOTICE: drop cascades to 8 other objects DETAIL: drop cascades to table o_test_generated drop cascades to table o_test_generated_like drop cascades to table o_test_drop_expression drop cascades to table o_test_add_identity_exist drop cascades to table o_test_add_identity drop cascades to table o_test_generated_null drop cascades to table o_test_tableoid drop cascades to table o_test_generated_alter_type DROP SCHEMA generated_test CASCADE; RESET search_path;