Postgresql create generated column syntax error, why?

Question

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?

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
– paranoidAndroid, Commented Aug 10, 2020 at 8:58
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
– Carlos, Commented Aug 10, 2020 at 9: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
– uberdwang, Commented Aug 17, 2022 at 18:39

Belayer · Accepted Answer · 2020-08-10 21:38:29Z

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.

answered Aug 10, 2020 at 21:38

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql create generated column syntax error, why?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related