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?
In PostgreSQL, the ADD COLUMN command/statement along with the ALTER TABLE clause is used to add single or multiple columns to a table. The ADD COLUMN command allows us to add new columns with constraints such as DEFAULT, NOT NULL, UNIQUE, etc.
A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual.
How do I COPY a column from one table to another in PostgreSQL? To copy create a pre-structured table: CREATE TABLE [Table to copy To] AS [Table to copy From] WITH NO DATA; Copy into pre-existing table: INSERT INTO [Table to copy To] SELECT [Columns to Copy] FROM [Table to copy From] WHERE [Optional Condition];
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With