postgresql conditionnal insert values

Question

I need a SQL which do following work.

INSERT INTO table_1 (column1, column2, column3) VALUES (:1,:2, :3)
This insert will only happen when another condition is TRUE, the
condition is:
SELECT 1 FROM table_2 WHERE column_time = 'time_stamp'

Table_1 and table_2 dont have any relation. the INSERT values to table_1 should only happen if the query on Table_2 return TRUE(1)

So i try to write my SQL as following

 INSERT INTO table_1 (column1, column2, column3) VALUES (:1,:2, :3) 
  WHERE EXISTS (SELECT 1 FROM table_2 WHERE column_time = 'time_stamp') 
  ON CONFLICT DO NOTHING

However, this SQL not work as Postgresql looks dont like the INSERT combine with WHERE condition.

BTW: the values (:1, :2:, :3) are bind array values. the final SQL looks like this:

INSERT INTO table_1 (column1, column2, column3) VALUES ('ca_1','cb_1', 'cc_1') ('ca_2','cb_2', 'cc_2') ... ('ca_n','cb_n', 'cc_n') WHERE EXISTS (SELECT 1 FROM table_2 WHERE column_time = 'my_time_stamp') ON CONFLICT DO NOTHING

Really need help thanks.

GMB · Accepted Answer · 2020-09-14 10:02:45Z

1

You need to select those values.

This would work in Postgres:

INSERT INTO table_1 (column1, column2, column3) 
SELECT :1, :2, :3
WHERE EXISTS (SELECT 1 FROM table_2 WHERE column_time = 'time_stamp') 
ON CONFLICT DO NOTHING

Alternatively:

INSERT INTO table_1 (column1, column2, column3) 
SELECT x.*
FROM (VALUES (:1, :2, :3)) AS x(column1, column2, column3)
WHERE EXISTS (SELECT 1 FROM table_2 WHERE column_time = 'time_stamp') 
ON CONFLICT DO NOTHING

answered Sep 14, 2020 at 10:02

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

postgresql conditionnal insert values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related