CODEWITHSUNDEEP
postgresql
RAJ PATEL
RAJ PATEL

Reputation: 594

Insert rows if not exist postgresql

I want to insert a row into postgresql if not exist into table. My table structure is as below.

Table Name: test

id serial NOT NULL

unitid integer

eventid integer

date_time timestamp without time zone

I tried following query but gives me error near Select.

INSERT INTO test VALUES (905, 10, '2015-09-23 13:34:26')
SELECT 905, 10, '2015-09-23 13:34:26'
WHERE NOT EXISTS(
SELECT 1
FROM test
WHERE unitid = 905 AND eventid = 10 AND date_time = '2015-09-23 13:34:26'
);

Please any one give me good suggestion about it. I want faster way to do this.

Upvotes: 0

Views: 105

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76426

You are specifying values and then you are including a selection. You need to write your insert into without values(...) and use the select you have there instead. Simple example

insert into items_ver
select * from items where item_id=2;

taken from here. Try your selection first. Is

SELECT 905, 10, '2015-09-23 13:34:26'
WHERE NOT EXISTS(
SELECT 1
FROM test
WHERE unitid = 905 AND eventid = 10 AND date_time = '2015-09-23 13:34:26'
);

If so, then you should have an insertion like this:

INSERT INTO test
SELECT 905, 10, '2015-09-23 13:34:26'
WHERE NOT EXISTS(
SELECT 1
FROM test
WHERE unitid = 905 AND eventid = 10 AND date_time = '2015-09-23 13:34:26'
);

Upvotes: 1

Related Questions