Question 169847 in PostgreSQL Question posted in
The official documentation can be found here.

Can Postgresql generate an immutable expression?

I’m trying to hash my 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
);
type here
Tags:

2

Answers


  1. 0

    You can cheat, by hiding the epoch extraction in an ‘immutable’ function.:

    CREATE OR REPLACE FUNCTION public.epoch_immutable(ts timestamp without time zone)
 RETURNS numeric
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
    _epoch_val numeric;
BEGIN
    _epoch_val =  date_part('epoch', COALESCE(ts, '1990-01-01 00:00:00'::timestamp without time zone));
    return _epoch_val;
END;
$function$
;

create table 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)
        ||
        epoch_immutable(create_dttm)::text, 
'sha256'::text))
    stored
);

insert into account_data_src (id, status_nm, create_dttm) values (1, 'test', now()::timestamp);

 select * from account_data_src ;
-[ RECORD 1 ]-------------------------------------------------------------------
id          | 1
status_nm   | test
create_dttm | 05/16/2023 14:03:08.715413
update_dttm | NULL
hash        | xd097c82689e3bec0b9688f6fef397fd610cf3aef779204fa912c7734b03bb7e4
    Login or Signup to reply.
  2. 0

    datepart(text, timestamptz) is only STABLE, because the result implicitly depends on the current timezone setting.

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search