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;