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.

Derviş Kayımbaşıoğlu · Answer 2 · 2022-06-17T14:03:27.003

0

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 '22 at 14:03

answered Feb 16 '19 at 11:32

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

This answer needs to be revised since the command is available since PostgreSQL 12: https://www.postgresql.org/docs/12/ddl-generated-columns.html – Vittorio Carmignani Jun 08 '22 at 10:59

score 0 · Answer 3 · answered Jun 08 '22 at 10:57

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

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

3 Answers3