to_timestamp in generated column

Asked May 26 '23 at 13:05

Active May 29 '23 at 09:14

Viewed 58 times

I have a table in my DB (PstgreSQL 14) that is auto-generated by another software. This table contains a t_stamp column stored as a BIGINT. I need to use this t_stampsomewhere else as a TIMESTAMP data type.

I thought about creating a generated column that would simply be the t_stamp column, casted to the right type :

ALTER TABLE myTable 
ADD COLUMN cpt_timestamp TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS to_timestamp(t_stamp/1000) STORED

And I get an error message :

ERROR:  syntax error at or near "to_timestamp"
LINE 2: ...tamp TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS to_timesta...
                                                             ^
SQL state: 42601
Character: 97

However, it works fine with a simple SELECT :

Select *, to_timestamp(t_stamp/1000) FROM my_table

Is it not possible to cast inside a generated query ?

edited May 29 '23 at 09:14

asked May 26 '23 at 13:05

Sim

2

You forgot the parentheses. – Laurenz Albe May 26 '23 at 13:32
@LaurenzAlbe Where ? – Sim May 29 '23 at 09:13
2

The generation expression must be enclosed in parentheses. So: GENERATED ALWAYS AS **(** to_timestamp(t_stamp/1000) **)** – Belayer May 29 '23 at 19:56

to_timestamp in generated column

0 Answers0