Adding generated column existing table in postgresql-CodePudding

I'm trying to add a generated column in existing table. I've a transaction table, in that table I've one column named as dateTime(containing date and time in timestamp format). I want to create a virtual column named as transactionDate which will contain a date and time derived from dateTime column.

Below is the query which I created

ALTER TABLE public.transaction ADD COLUMN "transactionDate" timestamp without time zone GENERATED ALWAYS AS (timestamp("dateTime")::date) STORED;

and I'm getting below error:

ERROR: syntax error at or near ""dateTime"" LINE 2: ... without time zone GENERATED ALWAYS AS (timestamp("dateTime"... ^ SQL state: 42601 Character: 121

Please help me out.

CodePudding user response：

What are you using timestamp() for?

For generated column, it should be like -

ALTER TABLE transaction ADD COLUMN "transactionDate" timestamp without time zone GENERATED ALWAYS AS ("dateTime"::date) STORED;

ALTER TABLE transaction ADD COLUMN "transactionDate" timestamp without time zone GENERATED ALWAYS AS ("dateTime"::timestamp) STORED;

Fiddle here.

CodePudding user response：

The column dateTime is already a timestamp so you do not need to create a timestamp from it. So just: ( see demo)

alter table transaction  
      add column "transactionDate" date
         generated always as ("dateTime"::date) stored;