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?
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.