SQL : Error when adding column generated from other columns (postgresql 11)

Question

I try to add a column to my table which references the BMI and I already have 2 columns with height and weight. I want to generate automatically this column when I fill the 2 others. But I get an error and I don't find any answer to solve it.

CREATE TABLE player
(
    id INT PRIMARY KEY NOT NULL,
    height INT,
    weight INT
);

ALTER TABLE player ADD COLUMN bmi FLOAT GENERATED ALWAYS AS (weight/((height/100)^2)) STORED;

I get following error :

ERROR: syntax error at or near "(" 
LINE : ...E player ADD COLUMN bmi FLOAT GENERATED ALWAYS AS (weight/((h...

SQL state: 42601 Character: 61

Postgres does not (yet) support computed columns . . . stackoverflow.com/questions/8250389/…. — Gordon Linoff
– Gordon Linoff, Commented Feb 16, 2019 at 11:17
Why do you want to store the BMI instead of calculating it in a Select/View? If you need an index you can use an Expression Index — dnoeth
– dnoeth, Commented Feb 16, 2019 at 11:36

Derviş Kayımbaşıoğlu · Accepted Answer · 2022-06-17 14:03:27Z

1

Postgres has no support for computed columns but you can use triggers to simulate the behavior of computed columns.

CREATE TRIGGER tr_player_computed
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE f_tr_player_computed();

CREATE OR REPLACE FUNCTION f_tr_player_computed() RETURNS TRIGGER AS $player$
   BEGIN
      new.bmi = (new.weight/((new.height/100)^2));
      RETURN NEW;
   END;

UPDATE: Generated columns are being supported starting from Posgres 12

CREATE TABLE people (
   ...,
   height_cm numeric,
   height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

edited Jun 17, 2022 at 14:03

answered Feb 16, 2019 at 11:32

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vittorio Carmignani Over a year ago

This answer needs to be revised since the command is available since PostgreSQL 12: postgresql.org/docs/12/ddl-generated-columns.html

Álvaro González · Accepted Answer · 2019-02-16 11:28:29Z

0

That syntax is for MySQL rather than PostgreSQL:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

In general, each DBMS speaks its own SQL dialect, with often big differences. Save for very basic DML statements, you must stick to the documentation of the product you're using.

answered Feb 16, 2019 at 11:28

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Comments

Vittorio Carmignani · Accepted Answer · 2022-06-08 10:57:35Z

0

I had this error also because I was thinking to run PostgreSQL 14 but then I run the command:

select version();
 -> PostgreSQL 11.7, compiled by Visual C++ build 1914, 64-bit

The command GENERATED ALWAYS AS is available since PostgreSQL 12 (as far as I can see from the documentation here: https://www.postgresql.org/docs/12/ddl-generated-columns.html).

You can check an implementation (where you can try also your code) here: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0caf484b5b1a5e3453bdbc63fd64a3c2

answered Jun 8, 2022 at 10:57

Vittorio Carmignani

1154 bronze badges

Collectives™ on Stack Overflow

SQL : Error when adding column generated from other columns (postgresql 11)

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related