Error when creating a generated column in Postgresql

Question

CREATE TABLE Person (
   id  serial primary key,
   accNum  text UNIQUE GENERATED ALWAYS AS (
 concat(right(cast(extract year from current_date) as text), 2), cast(id as text)) STORED
);

Error: generation expression is not immutable

The goal is to populate the accNum field with YYid where YY is the last two letters of the year when the person was added.

I also tried the '||' operator but it was unsuccessful.

Answer 1

As you don't expect the column to be updated, when the row is changed, you can define your own function that generates the number:

create function generate_acc_num(id int)
returns text
as
$$
  select to_char(current_date, 'YY')||id::text;
$$
language sql
immutable; --<< this is lying to Postgres!

Note that you should never use this function for any other purpose. Especially not as an index expression.

Then you can use that in a generated column:

CREATE TABLE Person 
(
  id  integer generated always as identity primary key,
  acc_num  text UNIQUE GENERATED ALWAYS AS (generate_acc_num(id)) STORED
);

Answer 2

As @ScottNeville correctly mentioned:

CURRENT_DATE is not immutable. So it cannot be used int a GENERATED ALWAYS AS expression.

However, you can achieve this using a trigger nevertheless:

demo:db<>fiddle

CREATE FUNCTION accnum_trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   NEW.accNum := right(extract(year from current_date)::text, 2) || NEW.id::text;
   
   RETURN NEW;
END
$$;

CREATE TRIGGER tr_accnum
  BEFORE INSERT
  ON person
  FOR EACH ROW
  EXECUTE PROCEDURE accnum_trigger_function();

As @a_horse_with_no_name mentioned correctly in the comments: You can simplify the expression to:

NEW.accNum := to_char(current_date, 'YY') || NEW.id;