Search code examples
postgresqlhashhash-functionvolatilitygenerated-columns

ERROR: generation expression is not immutable

I'm trying to create a generated column with a hash on two columns, but I get error.
What's wrong with my hash?

create table dwh_stage.account_data_src(
    id int4 not null,
    status_nm text null,
    create_dttm timestamp null,
    update_dttm timestamp  null,
    hash bytea NULL GENERATED ALWAYS AS 
       (digest(COALESCE(status_nm, '#$%^&'::text)
     || date_part('epoch'::text, COALESCE(timezone('UTC'::text, create_dttm), '1990-01-01 00:00:00'::timestamp without time zone))::text, 'sha256'::text))
        stored
);
Solution

  • datepart(text, timestamptz) is only STABLE, because the result implicitly depends on the current timezone setting. (Well, AFAICT, extracting the epoch would be immutable, but other extracted parts vary with the setting, so the function cannot be marked IMMUTABLE.)

    But datepart(text, timestamp) is IMMUTABLE.

    Your expression is a mess in this regard to begin with. You mix timestamptz and timestamp in the COALESCE expression, which forces the conversion of the given timestamp constant ...

    Drop the conversion of create_dttm to timestamptz and it works:

    CREATE TABLE dwh_stage.account_data_src (
  id int4 NOT NULL
, status_nm text NULL
, create_dttm timestamp NULL
, update_dttm timestamp  NULL
, hash bytea NULL GENERATED ALWAYS AS (
      digest(COALESCE(status_nm, '#$%^&'::text)
  ||  date_part('epoch', COALESCE(create_dttm, '1990-01-01'))::text, 'sha256'::text))
      STORED
);

    Note: create_dttm instead of timezone('UTC'::text, create_dttm).

    That said, if a bigint hash is good enough (like it is in many cases), and you run Postgres 14 or later, then consider instead:

    , hash bigint GENERATED ALWAYS AS (hash_record_extended((status_nm, create_dttm),0)) STORED

    Much simpler and faster. See: