CODEWITHSUNDEEP
sqlpostgresql
Keshav Narsu
Keshav Narsu

Reputation: 1

Translate SQL Server Query to Postgres

I am trying to convert this if statement from SQL Server to Postgres:

IF COL_LENGTH('Flex','revision') IS NOT NULL and OBJECT_ID(N'df_revision', N'D') IS NULL

BEGIN

ALTER TABLE Flex ADD CONSTRAINT df_revision DEFAULT 0 FOR revision

END

The purpose of this query is to set the default on the revision column if the table has that column.

Upvotes: 0

Views: 195

Answers (1)

user330315
user330315

Reputation:

Postgres has default values for columns. Those aren't "constraints".

To change the default value of a column you use:

alter table flex alter column revision set default 0;

If for some strange reason you don't know if the column exists in the table, you can use a PL/pgSQL block:

do
$$
begin
  if exists (select * 
             from information_schema.columns
             where table_name = 'flex' 
               and column_name = 'df_revision')
  then 
     alter table flex alter column revision set default 0;
  end if;
end;
$$
;

Upvotes: 2

Related Questions