Generate value from columns in Postgres

Question

I would like to have a generated column, which value will be the concated string from two other values:

 CREATE TABLE public.some_data (
    user_name varchar NULL,
    domain_name serial NOT NULL,

    email GENERATED ALWAYS AS (user_name ||'@'||domain_name) stored
 );

But that gives SQL Error [42601]: ERROR: syntax error at or near "ALWAYS"

You still have the declare the data type; Try `email text GENERATED ALWAYS AS (user_name ||'@'||domain_name) stored` — Belayer, Aug 13 '21 at 14:21
this produces an error https://dbfiddle.uk/?rdbms=postgres_13&fiddle=8540dcd1198753520754028e5fcda379 — nbk, Aug 13 '21 at 14:55

score 1 · Accepted Answer · answered Aug 13 '21 at 15:05

You need to provide the data type for the column as @Belayer commented.

And then you need to explicitly cast domain_name as text (or some varchar). Otherwise you'll get an error that the expression isn't immutable as @nbk commented. serial is translated to be basically an integer and for whatever reason implicit casts of an integer in concatenations are considered not immutable by the engine. We had that just recently here.

So overall, using the given types for the columns, you want something like:

CREATE TABLE public.some_data
             (user_name varchar NULL,
              domain_name serial NOT NULL,
              email text GENERATED ALWAYS AS (user_name || '@' || domain_name::text) STORED);

But it's a little weird that a domain name is a serial? Shouldn't that be a text or similar? Then you wouldn't need the cast of course.

score 0 · Answer 2 · answered Aug 13 '21 at 15:06

You need to create an IMMUTABLE function to achieve the generate column, for example:

CREATE OR REPLACE FUNCTION generate_email_concat(varchar,int) returns text as 
$$
    select $1 ||'@'||$2::text;
$$
LANGUAGE SQL IMMUTABLE ;

CREATE TABLE public.some_data (
    user_name varchar NULL,
    domain_name serial NOT NULL,
    email text GENERATED ALWAYS AS (generate_email_concat(user_name,domain_name)) stored
 );

INSERT into some_data(user_name) values ('hello');

score 0 · Answer 3 · answered Aug 13 '21 at 15:10

You try to concatenate varchar and integer. You have to cast domain_name. This works for me

CREATE TABLE public.some_data (
    user_name varchar NULL,
    domain_name serial NOT NULL,
    email varchar GENERATED ALWAYS AS (CASE WHEN user_name IS NULL THEN 'noname'||'@'||domain_name::text ELSE user_name ||'@'||domain_name::text END) STORED
 );

Generate value from columns in Postgres

3 Answers3