CODEWITHSUNDEEP
sqlpostgresqlif-statementpgadmin-4
Giampaolo Levorato
Giampaolo Levorato

Reputation: 1612

Add column to existing table in SQL with IF/ ELSE Statement

I am suing PgAdmin4
I have a table called Development. This table contains a column called Customer. I want to create a new column called Target which is equal to 1 when the Customer is null and 0 otherwise.

Here is my code:

ALTER TABLE development ADD COLUMN Target INTEGER;

IF Customer IS NULL then
  SET Target = 1;
else
  SET Target = 0;
END if;

I am getting this error:

ERROR:  syntax error at or near "IF"
LINE 3: IF Customer IS NULL then
        ^
SQL state: 42601
Character: 53

Upvotes: 2

Views: 2652

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Most databases support generated columns. The syntax is something like this:

alter table development add column int generated always as
    (case when customer is null then 1 else 0 end);

The exact syntax varies by database.

This is much more powerful than adding a new column to the table, because a generated column always has the correct value -- even without an update.

Upvotes: 4

Mureinik
Mureinik

Reputation: 311188

You could use an update statement with a case expression:

UPDATE mytable
SET target = CASE WHEN customer IS NULL THEN 1 ELSE 0 END

Upvotes: 1

Related Questions