Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add generated column to an existing table Postgres

Tags:

sql

postgresql

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?

like image 689
peter.petrov Avatar asked Mar 17 '20 17:03

peter.petrov


People also ask

How do I add a column to a PostgreSQL table?

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.

What is generated column in Postgres?

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 duplicate a column in PostgreSQL?

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];


1 Answers

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
like image 185
jjanes Avatar answered Nov 06 '22 22:11

jjanes