CODEWITHSUNDEEP
sqlpostgresqlcalculated-columns
coderoftheday
coderoftheday

Reputation: 2075

Alter column to make it a derived column

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);

Upvotes: 1

Views: 401

Answers (1)

D-Shih
D-Shih

Reputation: 46249

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

Upvotes: 1

Related Questions