Technical questionAccepted answer
Add generated column to an existing table Postgres
peter.petrov
Mar 17
0 views1
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?
1 answer
Accepted answer · original discussion
jjanes
PermalinkMar 17
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
2 question comments
Use comments to ask for clarification. Post a solution as an answer.
user330315Permalink
Mar 17
What does
select version(); give you?