3

I have a postgres table with two columns (an identificator and a date) that are a composite primary key. I would like to hash the concatenation in another column, generating this value everytime a new record is inserted. For that I'm trying to alter my table in order to create a generated column:

ALTER TABLE my_table ADD COLUMN hash_id_date VARCHAR(50)
GENERATED ALWAYS AS (MD5(my_table.original_id||'-'||my_table.time))
STORED;

This raises me the following error:

ERROR:  syntax error at or near "("
LINE 4: GENERATED ALWAYS AS (MD5(my_table.original_id,'-',my_table.t...
                            ^
SQL state: 42601
Character: 178

I'm turning into madness to find where is the syntax error... I've read about STABLE and IMMUTABLE functions and generated columns should always have an IMMUTABLE function as expression. As far as I know MD5 is IMMUTABLE but the error message is not even capable to reach that level.

Any help?

Carlos
  • 889
  • 3
  • 12
  • 34
  • I would try removing an element from the calculated clause one by one in order to find out what causing it - for example, try AS ((my_table.original_id||'-'||my_table.time)) without MD5, try without concat, etc... – paranoidAndroid Aug 10 '20 at 08:58
  • 6
    What does `select version();` give you? –  Aug 10 '20 at 09:09
  • 4
    Yep, @a_horse_with_no_name, your suspicions were right. My current version is not supporting generated column. I'm currently creating a new column and trying to simulate it with a trigger, however my current rows are not affected by this. I'm trying to update my table with the values of each row. – Carlos Aug 10 '20 at 09:26
  • @a_horse_with_no_name That's exactly what I ran into. Local ran fine since it's Postgres v12, my deployed environment was v10. – uberdwang Aug 17 '22 at 18:39

1 Answers1

1

Assuming the basic functionality for calculating the MD5 is common you can create a function for the calculation. Use this function wherever it's needed, including updating your current rows and invoke from a trigger on yo your table. If the particular MD5 calculation is not all that common you can just put the calculation in the trigger function and also use it in a independent update for current rows. See here for example with assumption it is common in your app.

Belayer
  • 13,578
  • 2
  • 11
  • 22