Postgresql syntax error for where not exists

Question

I have the following sql query that would only insert if it does not already exist in the table

INSERT INTO message_log (message, from_id, to_id, match_id, unix_timestamp, own_account) VALUES('hi', 'tom', 'tom', '55640ec48a2aecab0e3c096c556f5435f4bb054c68930040', 33333, TRUE)
WHERE NOT EXISTS (SELECT 1 FROM message_log WHERE message = 'hi' AND from_id = 'tom' AND to_id = 'tom' AND match_id = '55640ec48a2aecab0e3c096c556f5435f4bb054c68930040' AND unix_timestamp = 33333)

However I get the following error

ERROR:  syntax error at or near "WHERE"
LINE 2: WHERE NOT EXISTS (SELECT 1 FROM message_log where message = ...
        ^

What I am doing wrong?

That is not something you can do. At least not like that. Can you explain what you are trying to accomplish with that INSERT statement? — JNevill
– JNevill, Commented Jun 25, 2015 at 17:03
@JNevill I want the row to be inserted only if it does not already exist in the table — Arya
– Arya, Commented Jun 25, 2015 at 17:04

JNevill · Accepted Answer · 2015-06-25 17:08:41Z

1

Inserting a record in a table only if that record doesn't exist is done with:

 INSERT INTO message_log (message, from_id, to_id, match_id, unix_timestamp, own_account) 
 SELECT 'hi', 'tom', 'tom', '55640ec48a2aecab0e3c096c556f5435f4bb054c68930040', 33333, TRUE
 FROM message_log
 WHERE NOT EXISTS (SELECT 1 FROM message_log WHERE message = 'hi' AND from_id = 'tom' AND to_id = 'tom' AND match_id = '55640ec48a2aecab0e3c096c556f5435f4bb054c68930040' AND unix_timestamp = 33333)

It's a little more verbose, but it should do what you want. Alternatively, you could set a unique primary key on the table, so the database will give you the smack-down if you try to insert a duplicate key. Six of one, half a dozen of the other.

answered Jun 25, 2015 at 17:08

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

Comments

Joop Eggen · Accepted Answer · 2015-06-25 17:11:42Z

0

The following should do

INSERT INTO message_log (message, from_id, to_id, match_id, unix_timestamp, own_account)
SELECT 'hi', 'tom', 'tom', '55640ec48a2aecab0e3c096c556f5435f4bb054c68930040', 33333, TRUE
WHERE NOT EXISTS (SELECT 1 FROM message_log WHERE message = 'hi' AND from_id = 'tom'
    AND to_id = 'tom' AND match_id = '55640ec48a2aecab0e3c096c556f5435f4bb054c68930040'
    AND unix_timestamp = 33333)

However it lacks styke: self query.

Maybe something like the following might be applicable:

INSERT ...
ON DUPLICATE KEY UPDATE own_account = own_account;

answered Jun 25, 2015 at 17:11

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Collectives™ on Stack Overflow

Postgresql syntax error for where not exists

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related