Generated columns are not (always?) returned by a RETURNING clause in PostgreSQL databases

[olavloite]

Tables with generated columns do not (always?) return the generated value after an insert when a RETURNING clause is used. The value is generated, and selecting the row afterwards does return the generated value.

Reproduction steps

Start PGAdapter + Emulator and psql

docker run -d -p 5433:5432 gcr.io/cloud-spanner-pg-adapter/pgadapter-emulator
psql -h localhost -p 5433

Run the following SQL statements:

loite=> create table if not exists singers (
    id         varchar not null primary key,
    first_name varchar,
    last_name  varchar not null,
    full_name varchar(300) generated always as (
        CASE WHEN first_name IS NULL THEN last_name
             WHEN last_name  IS NULL THEN first_name
             ELSE first_name || ' ' || last_name
        END) stored,
    active     boolean,
    created_at timestamptz,
    updated_at timestamptz
);
CREATE
loite=> insert into singers (id, first_name, last_name, active, created_at, updated_at) values (spanner.generate_uuid(), 'first', 'last', true, current_timestamp, current_timestamp) returning *;
                  id                  | first_name | last_name | full_name | active |          created_at           |          updated_at           
--------------------------------------+------------+-----------+-----------+--------+-------------------------------+-------------------------------
 d37de893-89a1-45a2-86cc-ca45e2cd7fde | first      | last      |           | t      | 2024-01-25 15:35:07.993069+01 | 2024-01-25 15:35:07.993069+01
(1 row)

INSERT 0 1
loite=> select * from singers;
                  id                  | first_name | last_name | full_name  | active |          created_at           |          updated_at           
--------------------------------------+------------+-----------+------------+--------+-------------------------------+-------------------------------
 d37de893-89a1-45a2-86cc-ca45e2cd7fde | first      | last      | first last | t      | 2024-01-25 15:35:07.993069+01 | 2024-01-25 15:35:07.993069+01
(1 row)

loite=> 

[skuruppu]

Just wanted to add that this issue is being tracked internally. So far it seems to be an issue with an interaction between the PG translator and the GSQL reference implementation.