PostgreSQL - how to create a derived attribute which sums two attributes?

Question

I'm struggling with creating a totalCost column that is the sum of costA and costB for each row.

CREATE TABLE Cost (
  customerId      INTEGER NOT NULL,
  costA           FLOAT(4) NOT NULL,
  costB           FLOAT(4) NOT NULL,
  totalCost       FLOAT(4) GENERATED ALWAYS AS (costA + costB) STORED,
  PRIMARY KEY (customerId)
);

Can anyone tell me what I'm missing? I would have thought this would be super simple!

Note - trying to build it in the DDL rather than a query.

Thank you! :)

sorry for the typo trailing comma! Now deleted. Still getting a syntax error at or near "(" thank you everyone for your help — leesh_656, Apr 27 '21 at 08:06
Which version of postgres are you using? This works with postgres > 10. — Adam Tokarski, Apr 27 '21 at 08:55

Schwern · Answer 1 · 2021-04-27T05:49:42.563

0

You have a trailing comma at the end. SQL doesn't allow that.

CREATE TABLE Cost (
  customerId      INTEGER NOT NULL,
  costA           FLOAT(4) NOT NULL,
  costB           FLOAT(4) NOT NULL,
  totalCost       FLOAT(4) GENERATED ALWAYS AS (costA + costB) STORED,
  PRIMARY KEY (customerId),
                          ^ remove this
);

Note: since float(4) is imprecise, consider using the precise numeric type to store money.

edited Apr 27 '21 at 05:49

answered Apr 27 '21 at 04:49

Schwern

139,746
23
170
313

2

`You have a trailing comma at the end` ... maybe avoid answering such typo questions if possible. – Tim Biegeleisen Apr 27 '21 at 04:51
@TimBiegeleisen Why? – Schwern Apr 27 '21 at 04:53
@TimBiegeleisen Says we can close them. Doesn't say anything about not answering them. It's a well formed question with a clear problem and example. Seems impolite not to answer, especially for a new user. Close it if you like. – Schwern Apr 27 '21 at 05:02
1

[Don't use money](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money) – a_horse_with_no_name Apr 27 '21 at 05:32

PostgreSQL - how to create a derived attribute which sums two attributes?

1 Answers1