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

Question

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

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2021-04-23 10:42:41 +00:00
Commented Apr 23, 2021 at 10:42 — Gordon Linoff
– Gordon Linoff, Commented Apr 23, 2021 at 10:42

Gordon Linoff · Accepted Answer · 2021-04-23 10:43:29Z

5

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.

answered Apr 23, 2021 at 10:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2021-04-23 10:42:59Z

1

You could use an update statement with a case expression:

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

answered Apr 23, 2021 at 10:42

Mureinik

316k54 gold badges396 silver badges405 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related