CODEWITHSUNDEEP
postgresql
GuiOm Clair
GuiOm Clair

Reputation: 149

Multiple condition postgresql 9.5

I have a table with three columns cola, colb, colc, containing a positive integer (which can be null though). I would like to make an INSERT INTO a new table if cola, colb, or colc is not null, in such a way that if none of them are null, then I will have three new entries inserted, and if only two of them the corresponding two new entries.

This syntax is working, but I am looking for a possible simpler way to do :

INSERT INTO new_table select 'cola' where table.cola > 0;
INSERT INTO new_table select 'colb' where table.colb > 0;
INSERT INTO new_table select 'colc' where table.colc > 0;

Any idea ? Thanks

Upvotes: 1

Views: 66

Answers (1)

Adam
Adam

Reputation: 5599

You can select multiple rows (using UNION) and add them in one INSERT INTO:

INSERT INTO 
    new_table (
        SELECT 'cola' WHERE table.cola > 0
        UNION ALL
        SELECT 'colb' WHERE table.colb > 0
        UNION ALL
        SELECT 'colc' WHERE table.colc > 0
    );

Upvotes: 1

Related Questions