Postgres Generation Expression Is Not Immutable

I wanted to have a cool computed field in Postgres, so I can use it from different types of apps (NodeJS and Golang).

The two NodeJS functions I’m moving the DB would be summed up in these methods

  static getUserHash (string) {
    return crypto
      .createHash('md5')
      .update(string)
      .digest('hex')
  }

  static getUserString (user) {
    return `${user.id}::${user.username}::${user.encPassword}::${user.updatedAt.toISOString()}`
  }

At first, I thought I could just do this. It would add a computed, stored field to the user_account table.

ALTER TABLE user_account ADD COLUMN user_hash TEXT GENERATED ALWAYS AS (
  md5(id || '::' || username || '::' || enc_password || '::' || updated_at::text)
) STORED;

Nope: Received the error

generation expression is not immutable

Turns out, the computed needs to return the same all the time (no matter where). It didn’t like the updated_at field, since it’s a timestamptz and could change based off the TZ. It also is not a fan of fields that can be NULL.

So I ended with this rendition.

ALTER TABLE user_account
ADD COLUMN user_hash TEXT GENERATED ALWAYS AS (
  md5(id || '::' || coalesce(enc_password, '') || '::' || date_part('epoch', COALESCE(updated_at at time zone 'UTC', '1990-01-01'))::text) 
) STORED;

A slight compromise.

• Using the epoch instead of the ISO 8601 string, which is fine
• Forced the TZ to be UTC
• wrapped NULL-able fields in COALESCE
• Removed the username: wasn’t causing a problem, I didn’t need it for this bit