CODEWITHSUNDEEP
postgresql
Arnold Cristobal
Arnold Cristobal

Reputation: 851

postgres insert into table with select case

I'm trying to insert into a table a set of records which will be extracted based on 2 situations:

INSERT INTO table1 (pkid, time_created)
SELECT pkid, time_created,
CASE WHEN tgt_time >= src_time THEN tgt_time as time
    WHEN src_time >= tgt_time THEN src_time as time
END
FROM temptable
WHERE time >= '0001-01-01' and time <= '2016-03-01';

I'm trying to do a case on when to use either of the 2 columns to filter the date to extract the records. Is this possible in postgres or maybe tweak the statement to make it work?

Upvotes: 0

Views: 2256

Answers (2)

Ivan Burlutskiy
Ivan Burlutskiy

Reputation: 1623

Wrong count of columns: insert - 2 columns, select - 3 columns. Probably missed time in insert into table1 (pkid, time_created, time)

Upvotes: 3

Arnold Cristobal
Arnold Cristobal

Reputation: 851

INSERT INTO table1 (pkid, src_time_created)
SELECT pkid, src_time_created FROM temptable
WHERE 
CASE WHEN tgt_time >= src_time THEN tgt_time >= '0001-01-01' and tgt_time < '2016-03-01'
ELSE src_time >= '0001-01-01' and src_time < '2016-03-01'
END

This worked with a little tweaking on the query.

Upvotes: 1

Related Questions