Insert rows if not exist postgresql

Question

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.

Community · Accepted Answer · 2017-04-13 12:42:39Z

1

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'
);

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Sep 23, 2015 at 14:30

Lajos Arpad

80.3k42 gold badges122 silver badges234 bronze badges

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

1 Comment

RAJ PATEL Over a year ago

Thank you so much. This is the best answer. Performance wise I have aroung 100,000 data and perform operation in around 22 ms. Thank you so much.

Collectives™ on Stack Overflow

Insert rows if not exist postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related