Alter column to make it a derived column

user_13512812 0
Viewed 79

I'm learning PostgreSQL and I'm wondering is it possible to add a derived column to a table then ALTER it on a another line, I've tried different ways but I can't find the correct Syntax for it.

For some reason its seems as if the exercise wants it on two lines, but I can only do it on one at the moment.

-- 1) Add an attribute Taxes to table Payment
ALTER TABLE Payment
ADD Taxes real;

-- 2) Set the derived attribute taxes as 12% of PaySlip
ALTER TABLE Payment
ALTER COLUMN Taxes AS (0.12*Payment.PaySlip);
calculated-columns postgresql sql
1 Answers

You can try to use Generated Columns

If this column exists in your table, we might need to drop it before creating it.

Due to the document might not support ALTER COLUMN with GENERATED

ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

ALTER TABLE Payment DROP COLUMN Taxes;

ALTER TABLE Payment
ADD Taxes  [type of Taxes]  GENERATED ALWAYS AS (0.12 * PaySlip) stored;

sqlfiddle

user_51760710
Related