Add generated column to an existing table Postgres

Question

I am trying to add a generated column to an existing table with this script.

alter table Asset_Store add column

md5_hash VARCHAR(100) GENERATED ALWAYS AS 

(CAST(UPPER(    
        case
             when OR_ID is not null then MD5(cast(OR_ID as varchar(100)))
             when Asset_ID is not null then MD5(Asset_ID)
             else null
        end 
) as VARCHAR(100)))

STORED

;

but I am getting an error:

SQL Error [42601]: ERROR: syntax error at or near "("
 Position: 88
 ERROR: syntax error at or near "("
 Position: 88
 ERROR: syntax error at or near "("
 Position: 88

What is the issue? I don't get it.

In the schema of my Asset_Store table the column
OR_ID is int and Asset_ID is varchar(100).

I guess it expects a slightly different syntax... but what is the right syntax?

Version of PostgreSQL? It was introduced in 12.

Abelisto
– Abelisto

2020-03-17 18:58:36 +00:00
Commented Mar 17, 2020 at 18:58 — Abelisto
– Abelisto, Commented Mar 17, 2020 at 18:58
What does select version(); give you?

user330315
– user330315

2020-03-17 20:00:43 +00:00
Commented Mar 17, 2020 at 20:00 — user330315
– user330315, Commented Mar 17, 2020 at 20:00
Thanks. Yes, I realized later that I was on PG 11.

peter.petrov
– peter.petrov

2020-03-18 00:01:17 +00:00
Commented Mar 18, 2020 at 0:01 — peter.petrov
– peter.petrov, Commented Mar 18, 2020 at 0:01

jjanes · Accepted Answer · 2020-03-17 19:37:44Z

19

Your syntax is correct. Your version of PostgreSQL apparently is not.

In version 12:

create table asset_store(or_id text, asset_id text);

alter table Asset_Store add column
md5_hash VARCHAR(100) GENERATED ALWAYS AS 
(CAST(UPPER(    
        case
             when OR_ID is not null then MD5(cast(OR_ID as varchar(100)))
             when Asset_ID is not null then MD5(Asset_ID)
             else null
        end 
) as VARCHAR(100)))
STORED
;

ALTER TABLE
Time: 17.678 ms

answered Mar 17, 2020 at 19:37

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

2 Comments

peter.petrov Over a year ago

Thanks. Yes, I realized later that I was on PG 11.

mickmackusa Over a year ago

For future copy-pasters, neither of the CAST (... as VARCHAR(100)) expressions were necessary for me in my different SQL.

pink_daemon · Accepted Answer · 2022-08-09 10:50:59Z

7

More general, simplified command

ALTER TABLE "items"
ADD COLUMN "revenue" numeric 
GENERATED ALWAYS AS ("price" * (1-"discount")) STORED;

answered Aug 9, 2022 at 10:50

pink_daemon

3943 silver badges6 bronze badges

Collectives™ on Stack Overflow

Add generated column to an existing table Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related