postgres generated columns virtual columns default - ghdrako/doc_snipets GitHub Wiki
Generowanie wartości kolumn
- wartości wyliczanie po wstawieniu
DEFAULT- Identity (v10) –
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY - Expression (v12) –
GENERATED ALWAYS AS ( generation_expr ) STORED
Kolumna domyślnie przyjmuje wartość NULL, jeśli podczas wstawiania wiersza nie podano żadnej wartości. Istnieją jednak trzy przypadki, w których silnik może podstawić inną wartość. Najbardziej znanym jest klauzula DEFAULT. W tym przypadku wstawiona wartość odpowiada wartości określonej w tej klauzuli, jeśli dla kolumny nie określono żadnej wartości. Jeżeli określono wartość, to zastępuje ona wartość domyślną. Poniższy przykład to pokazuje
CREATE TABLE t2 (c1 integer, c2 integer, c3 integer DEFAULT 10);
INSERT INTO t2 (c1, c2, c3) VALUES (1, 2, 3);
INSERT INTO t2 (c1) VALUES (2);
SELECT * FROM t2;
c1 | c2 | c3
----+----+----
1 | 2 | 3
2 | | 10
Klauzuli DEFAULT nie można stosować z klauzulami złożonymi, w tym z klauzulami zawierającymi zapytania. Zazwyczej do rejestrowania daty wprowadzenia rekordu:
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Począwszy od PostgreSQL 12, możliwe jest użycie GENERATED ALWAYS AS (wyrażenie) STORED. Umożliwia to uzyskanie obliczonej wartości dla kolumny, wartości, której nie można przeciążyć ani przy wstawianiu, ani przy aktualizacji (ale która jest przechowywana na dysku).
Jako przykład weźmiemy tabelę kapitanów i dodamy do niej kolumnę zawierającą zmodyfikowaną wersję numeru karty kredytowej jako jej wartość:
ALTER TABLE capitaines ADD COLUMN num_cc_anon text GENERATED ALWAYS AS (substring(num_cartecredit, 0, 10) || '******') STORED;
SELECT nom, num_cartecredit, num_cc_anon FROM capitaines;
nom | num_cartecredit | num_cc_anon
----------------+------------------+-----------------
Robert Surcouf | 1234567890123456 | 123456789******
Haddock | |
INSERT INTO capitaines VALUES (2, 'Joseph Pradere-Niquet', 40, '9876543210987654', '44000', 'Lundi', 'test');
ERROR: cannot insert into column "num_cc_anon" DETAIL: Column "num_cc_anon" is a generated column.
INSERT INTO capitaines VALUES (2, 'Joseph Pradere-Niquet', 40, '9876543210987654', '44000', 'Lundi');
SELECT nom, num_cartecredit, num_cc_anon FROM capitaines;
nom | num_cartecredit | num_cc_anon
-----------------------+------------------+-----------------
Robert Surcouf | 1234567890123456 | 123456789******
Haddock | |
Joseph Pradere-Niquet | 9876543210987654 | 987654321******
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY pozwala na uzyskanie kolumny typu id, znacznie lepszej niż to, co oferuje pseudotyp seryjny. Jeśli określono opcję ALWAYS, wartość ta nie jest edytowalna.
ALTER TABLE capitaines ADD COLUMN id2 integer GENERATED ALWAYS AS IDENTITY;
SELECT nom, id2 FROM capitaines;
nom | id2
-----------------------+-----
Robert Surcouf. | 1
Haddock | 2
Joseph Pradere-Niquet | 3
INSERT INTO capitaines (nom) VALUES ('Tom Souville');
SELECT nom, id2 FROM capitaines;
nom | id2
-----------------------+-----
Robert Surcouf | 1
Haddock | 2
Joseph Pradere-Niquet | 3
Tom Souville | 4
Typ szeregowy jest zastępowany typem liczb całkowitych i sekwencją, jak pokazano w poniższym przykładzie. Jest to problem, o ile deklaracja składająca się z tworzenia tabeli daje wynik podstawy, a zatem w eksporcie danych.
CREATE TABLE tserial(s serial);
Table "public.tserial"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
s | integer | | not null | nextval('tserial_s_seq'::regclass) 84
There are two kinds of generated columns:
- stored (Postgres 12+)
- virtual (not implemented in Postgres
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
CREATE TABLE tbl (
int1 int
, int2 int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);
A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.
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();