CODEWITHSUNDEEP
sqlpostgresql
peter.petrov
peter.petrov

Reputation: 39437

Add generated column to an existing table Postgres

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?

Upvotes: 17

Views: 16997

Answers (2)

pink_daemon
pink_daemon

Reputation: 374

More general, simplified command

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

Upvotes: 7

jjanes
jjanes

Reputation: 44137

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

Upvotes: 19

Related Questions