Upsert in node.js - what is `IF EXIST`?

Question

Could someone explain the if exist command that is used in SQL?

for example:

if exist(select * from waiter){
    update waiter set (...) where waiter_id='somevalue'
} else{
    insert into waiter values (values)
}

Could you also explain how one will check if the update/insert was completed without going into the database?

[*The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true"; if the subquery returns no rows, the result of EXISTS is "false".*](http://www.postgresql.org/docs/current/static/functions-subquery.html) — Jonathan Lonowski, Jan 20 '14 at 20:19
This question appears to be off-topic because it is about just reading the documentation — Lamak, Jan 20 '14 at 20:23
@JonathanLonowski Please do not reffer to the manual of outdated versions. Use `/docs/current/static/` instead of `/docs/8.1/static/` in links to postgres documentation — Ihor Romanchenko, Jan 20 '14 at 20:23
@IgorRomanchenko Missed that the link was versioned. Just grabbed what appeared in a search. But, updated the link. — Jonathan Lonowski, Jan 20 '14 at 20:24
The above code is nonsense, it can never run in PostgreSQL because there's no `IF test { block } else { block }` statement, either in SQL or (with that syntax) in PL/PgSQL. It's also clear that you're attempting to do an upsert, and this is a totally incorrect way to do an upsert. See horse's link, and http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql/17267423#17267423 — Craig Ringer, Jan 21 '14 at 02:04

score 2 · Answer 1 · answered Jan 20 '14 at 20:20

2

I don't know if this helps, but the code should probably read:

if exist(select * from waiter where waiter_id='somevalue') {
    update waiter set (...) where waiter_id='somevalue'
} else{
    insert into waiter values (values)
}

The if exists . . . portion in this case is saying "if the record exists in the table". Overall, the statement is saying "if the 'somevalue' record exists in the value, then update the record; otherwise, insert a new record."

answered Jan 20 '14 at 20:20

Gordon Linoff

1,242,037
58
646
786

Good find on the missing `WHERE` clause. – Mike Perrenoud Jan 20 '14 at 20:20
1

@MichaelPerrenoud . . . "If the tables isn't empty, do an update on a record that might or might not exist, otherwise insert a record" doesn't seem like a sensible or safe thing to do. ;) – Gordon Linoff Jan 20 '14 at 20:22
@Gordon Linoff now what about if I was to see if this happened? How will I know? for example how will I know if the update or the insert has happened without looking into the database? – user3192435 Jan 20 '14 at 20:23
@user3192435 . . . The easiest thing to do is to set a variable inside each clause and then examine the variable afterwards. – Gordon Linoff Jan 20 '14 at 20:35

score 2 · Answer 2 · edited May 23 '17 at 12:03

The approach you are taking is wrong. It will not work. Stop.

Go read the guidance on upserts:

and then use one of the correct methods documented there, either locking the table and doing the upsert, or doing it in a retry loop.

To understand why your current approach is wrong, think about what happens when two people do the same command at the same time.

person1: select * from waiter
person2: select * from waiter
person1: if exist(...) => false, nope, doesn't exist
person2: if exist(...) => false, nope, doesn't exist
person1: insert...
person2: insert...

and boom, you have two records.

(This approach is also awfully inefficient - the SELECT * FROM WAITER is just horrible).

That's why everyone's giving you links to articles and documentation showing you how to do it right. You're trying to solve the wrong problem. You're effectively saying "When I drive my car into the intersection without looking at the traffic lights, how do I know if somebody else is already there?". We're saying "Uh, don't do that. Look at the traffic lights."

Only once you have the actual upsert ("create if not exists") operation correct, then worry about how to deal with it from node.js.

I'd say it'll be like any other query, anyway: Run query/command, get id (either existing or newly created) as result from query, use ID however you want. If you need more help with that part, try posting a node.js specific question on just that part, without all the incorrect-upsert-code mess to throw everybody off track. Consider that many on the postgresql tag won't know much about node.js anyway.

score 0 · Answer 3 · answered Jan 20 '14 at 20:22

0

The exists statement do a partial scan over the sql to know if something was returned..

If we was check the perform about exists if comparated with count().. we would see a really faster sql using exists, since count will go check each row at the database.

Hope it helps.

answered Jan 20 '14 at 20:22

Hugo S. Mendes

1,076
11
23

Now what id I want to check if the update/insert has been completed without looking into the database? @Hugo S. Mendes – user3192435 Jan 20 '14 at 20:25
then you need to put the "where waiter_id='somevalue'" into the exists like this: exist(select * from waiter where waiter_id='somevalue') – Hugo S. Mendes Jan 20 '14 at 20:28

Upsert in node.js - what is `IF EXIST`?

3 Answers3