CODEWITHSUNDEEP
sqlsql-serverpostgresqldatabase-migrationgenerated-columns
TejusN
TejusN

Reputation: 107

How can I alter a column so that it has a generated constraint in postgres

I have a table in Postgres that has the following columns:

Table Definition:

FirstName varchar(255)| LastName varchar(255)| FullName varchar(255)

what I want to do is add the following generated constraint to the FullName column like this (FullName varchar(255) generated always as (FirstName || LastName) stored). What is the correct alter table command that can be used to make the FullName a generated column?

Some Context: I'm migrating a DB from SQL Server to Postgres, one of the tables in SQL Server has a generated column defined like so (ColumnName varchar(255) as (column1 + column2) persisted) This column in Postgres is defined as (ColumnName varchar(255) generated always as (column1 || column2) stored) Problem is that when I migrate the data using a migration tool I'm getting errors related to the column being generated, so what I intend on doing is to create the table without the generated constraint then migrated the data to the postgres table, after that using alter table I'll alter the column to have the generated constarint

Upvotes: 1

Views: 1960

Answers (1)

user22760463
user22760463

Reputation: 77

the alter command is:

ALTER TABLE your_table_name
ALTER COLUMN FullName SET GENERATED ALWAYS AS (FirstName || LastName) STORED;

it will calculate always the both FirstName and LastName columns into FullName column

Upvotes: 1

Related Questions