0

Looking to create a table like the following:

CREATE TABLE newtable (
    id UUID  GENERATED ALWAYS AS gen_random_uuid() STORED,
    org uuid NOT NULL,
    name text,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    UNIQUE(name, org)
)

But I am getting an error:

Query 1 ERROR: ERROR:  syntax error at or near "gen_random_uuid"
LINE 2:     id UUID  GENERATED ALWAYS AS gen_random_uuid() STORED

Postgres docs here(generated columns) and here (table creation) say this should be possible:

GENERATED ALWAYS AS ( generation_expr ) STORED This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned.

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

The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.

What am I missing? Is gen_random_uuid not a generation expression? It seems to fit all of the criteria.

matic
  • 301
  • 2
  • 3
  • 10

1 Answers1

0

You are missing the parentheses around the generation_expr. But that is only a syntactical problem, and you won't be able to use a generated column for that. Read a little further in the documentation you linked to, and you will find:

  • The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.

Now gen_random_uuid() certainly is no IMMUTABLE function.

You have two options:

  • use a column DEFAULT clause

  • use a BEFORE INSERT trigger if you don't want the user to be able to override the column DEFAULT

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    An extension to `identity` columns would be useful for a case like this. Something along the lines of `generated always as identity using (...)` to use a different "generator" than a sequence. –  Dec 09 '22 at 07:19
  • @a_horse_with_no_name There is the danger of colliding with the SQL standard and its possible future extensions. – Laurenz Albe Dec 09 '22 at 08:40