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 . . . https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql. — Gordon Linoff, Feb 16 '19 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, Feb 16 '19 at 11:36

score 0 · Answer 1 · answered Feb 16 '19 at 11:28

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.

score 0 · Answer 2 · answered Feb 16 '19 at 11:32

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;

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

2 Answers2