Adding a computed column in Postgres SQL based on a date

user_11813699 0
Viewed 689

Here my table.

CREATE TABLE annual_goals (
  id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  content TEXT NOT NULL,
  complete BOOLEAN DEFAULT false,
  date_created TIMESTAMP DEFAULT now() NOT null,
  date_modified TIMESTAMP DEFAULT now() NOT null
);

I want to alter it such that I can add a new column called month_num that returns the number of the month given date_created (i.e. if the date_created of an entry is 5/31/2019, I want the month_num to automatically populate 5).

I tried the following but I'm getting an error that states "ERROR: syntax error at or near "A S"

ALTER TABLE annual_goals
    ADD year_num
        AS year(date_created);

Any help is greatly appreciated. Thanks

postgresql sql
2 Answers

You have two errors in the code. One is that MySQL requires the type. The second is that the expression needs to be surrounded by parentheses:

ALTER TABLE annual_goals ADD year_num int AS ( year(date_created) );

EDIT:

In Postgres, you can use the syntax:

alter table annual_goals
    add year_num int generated always as (extract(year from date_created)) stored;

Here is a db<>fiddle.

user_11440350

You could try this:

ALTER TABLE annual_goals
    ADD year_num int;

UPDATE annual_goals
SET year_num=year(date_created);
user_119486100
Related