Create a new column based on other columns

Question

I've managed to create the following column in a new table:

CREATE TABLE t_issue_dates as

SELECT issue_d,

cast(substr(issue_d,5,4) as numeric) as issue_year
FROM myDB

(The code recodes a year-month variable to a year-only variable)

However, i can't add this variable to my existing table "myDB". I've tried using the:

ALTER TABLE myDB ADD v_year - command, but i can't manage to get it right.

Does anyone have an idea how i add the above variable to the "original" table myDB?

Thank you!

Answer 1

First, many databases support computed or generated columns. That means that you can add a virtual column to the database by doing:

alter table t_issue_dates add issue_year as (cast(substr(issue_d, 5, 4) as numeric));

I recommend this approach because issue_year is always up-to-date.

Second, you can do this as an actual column, but the value can get out-of-date and needs to be re-calculated for each inserted/updated row:

alter table t_issue_dates add issue_year numeric;

update t_issue_dates
    set issue_year = cast(substr(issue_d, 5, 4) as numeric);

For the record, I would use int rather than numeric.