Question 114560 in PostgreSQL Question posted in
The official documentation can be found here.

Adding generated column existing table in postgresql

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search