postgresql

Using Timestamp with TimeZone in computed column

I need to migrate 2 very large tables continually. My first solution was to compare column by column to find what has changed, but that is too expensive to execute.

So second idea was to create PERSISTED Computed Columns to store hashed values of set columns. This could speed up the process. But Problem is with hashing columns with timestamp with timezone. I am getting this error: ERROR: generation expression is not immutable.

I tried various ways to cast the value, but nothing is working yet.

Code (commented values, did not work):

ALTER TABLE schema.table ADD COLUMN hashed_columns UUID GENERATED ALWAYS AS (
    md5(
        id::text ||
        type_id::text ||
        --EXTRACT(EPOCH FROM date)::text ||
        -- to_char(date::timestamp without time zone , 'YYYY-MM-DD"T"HH24:MI:SS') ||
        -- date::text ||
        -- to_char(date, 'YYYYMMDD')::integer ||
        -- md5(date::text) ||
        to_char(date, 'YYYYMMDD') ||
        value1::text ||
        value2::text
    )::uuid
) STORED;

It looks like, it detects immutable column, and just throws error, without tryint to cast it first. Is what I am tring to do fundementally wrong or is it possible?

When I remove date column from script, it works, so problem is within the timestamp.

Server is running Postgre 16.

Solution

  • If you want to have an IMMUTABLE expression that converts a timestamp with time zone to something you can concatenate, use

    extract(epoch FROM date AT TIME ZONE 'UTC')

    I don't think it is a great idea to name a timestamp column date, but that's just an aside.

    For your original problem: rather than computing a hash from all columns or comparing all columns, why fon't you simply add a column version that is either filled with a sequence value or with the current timestamp on every UPDATE? Then you could compare just with that one column to figure out if the row was changed or not. That would be way cheaper than computing the hash.