Right syntax to do an INSERT in PostgreSQL with multiple IF conditions

Question

I have a quite large table of sporting events and I am cleaning up trying to arrange everything in smaller and cleaner tables. I am getting there, but I found an obstacle. This is the original table structure:

date  |  team_name  |  team_id  |  opposition  |  opposition_id  |  venue
-------------------------------------------------------------------------

and here is what I want to do with it. A part of what I will write now is pseudocode, because I am not very sure how can I express this in PostgreSQL:

INSERT INTO new_table(date, home, away)
SELECT DISTINCT date, (...)

For the next two fields after date, I want to insert in the column home of the new table the values of the original column team_id IF the value in the original column venue is "home". If the value of venue is not "home", then in the new column home of the new table I will insert the values of the original opposition_id.

Can this be done easily? I found a question dealing with multiple IFs in MySQL here, but I am not very sure about how would I adapt that method to my case.

Andomar · Accepted Answer · 2015-05-30 19:02:03Z

2

You can use case:

insert  into NewTable
        (date, home)
select  date
,       case venue 
        when 'home' then team_id
        else opposition_id
        end
from    YourTable

answered May 30, 2015 at 19:02

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

1 Comment

telex-wap Over a year ago

Excellent! It took me a few tries to get my (much longer) real case working, but it's perfect now.

Collectives™ on Stack Overflow

Right syntax to do an INSERT in PostgreSQL with multiple IF conditions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related