postgres generated columns - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/ddl-generated-columns.html

Generated column (Postgres 10+)

The SERIAL data type is unique to PostgreSQL and has some known problems, so starting at version 10, the SERIAL datatype was softly deprecated in favor of identity columns:

• GENERATED BY DEFAULT - we can set autoganarated column manualy and cause mess (duplicate value error)

CREATE TABLE sale (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# SELECT * FROM sale;
 id │           sold_at             │ amount
────┼───────────────────────────────┼────────
  1 │ 2021-09-25 10:11:57.771121+03 │   1000

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
INSERT 0 1

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
ERROR:  duplicate key value violates unique constraint "sale_pkey"
DETAIL:  Key (id)=(2) already exists.

• GENERATED ALWAYS - not allowed set manulally

CREATE TABLE sale (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

In the error message, PostgreSQL is kind enough to offer a solution for when you actually do want to set the value for an identity column explicitly:

db=# INSERT INTO sale (id, sold_at, amount)
OVERRIDING SYSTEM VALUE VALUES (2, now(), 1000);

INSERT 0 1

By adding the OVERRIDING SYSTEM VALUE to the INSERT command you explicitly instruct PostgreSQL to allow you to set the value of an identity column. You still have to handle a possible unique constraint violation.

Differences between a column with a default and a generated column.

default	generated column
evaluated once when the row is first inserted if no other value was provided	a generated column is updated whenever the row changes and cannot be overridden
may not refer to other columns of the table	a generation expression would normally do so
can use volatile functions, for example random() or functions referring to the current time	volatile functions are not allowed

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

CREATE TABLE tab_gen
(
	username CHARACTER VARYING(20),
	userpass CHARACTER VARYING(20),
	userhash CHARACTER VARYING(50) GENERATED ALWAYS AS (md5(username||userpass)) STORED
);

'STORED' is required to signify that the column will be computed on write and will be stored on disk.

Equivalent using triggers - worse performance

CREATE OR REPLACE FUNCTION fn_gen_hash()
RETURNS TRIGGER AS
$BODY$
	BEGIN
		IF (NEW.userhash IS NOT NULL AND TG_OP='INSERT')
	    THEN
	      RAISE EXCEPTION 'cannot insert into the column "userhash". It''s a generated column';
	    ELSEIF (TG_OP='UPDATE' AND NEW.userhash <> OLD.userhash)
	    THEN
		      RAISE EXCEPTION 'cannot update the column "userhash". It''s a generated column';
	    END IF;
		NEW.userhash=md5(NEW.username||NEW.userpass);
		RETURN NEW;
	END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER trg_gen_hash
	BEFORE INSERT OR UPDATE
	ON tab_trigger
	FOR EACH ROW
	EXECUTE FUNCTION fn_gen_hash();