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
);
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: