Adding generated column existing table in postgresql

Question

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.

Pankaj · Accepted Answer · 2022-09-19 17:56:35Z

1

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;

OR

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

Fiddle here.

answered Sep 19, 2022 at 17:56

Pankaj

2,7762 gold badges9 silver badges22 bronze badges

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

1 Comment

Ganga Singh Over a year ago

Will this give me a output in MM-DD-YYYY HH:MM format?

Laurenz Albe · Accepted Answer · 2022-09-20 01:31:21Z

0

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;

edited Sep 20, 2022 at 1:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Sep 19, 2022 at 18:03

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

4 Comments

Ganga Singh Over a year ago

I run this query in pgadmin and it's giving error

ERROR:  unterminated quoted identifier at or near ""dateTime::date) STORED;" LINE 3: GENERATED ALWAYS AS ("dateTime::date) STORED;                              ^ SQL state: 42601 Character: 87

Ganga Singh Over a year ago

I did change ALTER TABLE public.transaction ADD COLUMN "transactionDate" DATE GENERATED ALWAYS AS ("dateTime"::date) STORED; and it's giving ERROR: generation expression is not immutable SQL state: 42P17

Belayer Over a year ago

There is a missing double quote after dateTime. Line should read generated always as ("dateTime"::date) stored; Think this comes from the fact that I never use double quoted column names unless they are already there.

Laurenz Albe Over a year ago

@Belayer What kept you from fixing it?

Collectives™ on Stack Overflow

Adding generated column existing table in postgresql

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related