0

Okay, so I just started with pgSQL, and of course discovered there is no ON DUPLICATE KEY like MySQL has. Developing in PHP.

I want to hear your opinion on this solution for upsert, good and bad things and possibly errors if there are any.

So I have an object (in PHP) which has been populated with data. If populate was made on edit, ID of object will also be there, else ID will be 0. When it comes to saving, firstly I check that ID, if it's > 0 then okay, else, I'm getting a new ID from my DB, using this:

$query = "SELECT NEXTVAL(PG_GET_SERIAL_SEQUENCE(?, ?)) id";

As I learned recently, there is a guarantee that ID's will be unique, which is normally the important thing (@IMSoP).

The next thing is the actual upsert, and the easiest method I found was this (@Craig Ringer also commented it has concurrency problems)

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
   SELECT 3, 'C', 'Z'
   WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

And that's preety much it.

I would love to hear your opinions :)

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
Adrian
  • 1,499
  • 3
  • 19
  • 26
  • Yikes, where'd I write that and how long ago? It needs a giant "not concurrency safe unless you lock the table" disclaimer. (Edit) Aah, you're talking about http://stackoverflow.com/a/6527838/398670 . I didn't write that, I just added the warning on the bottom. – Craig Ringer Aug 15 '13 at 09:20
  • So if the object has id you make an object, else you pull serial from table and insert? Why not just update those who have id and insert the rest relying on sequence to insert the id on itself? – Jakub Kania Aug 15 '13 at 09:22
  • possible duplicate of [How do I do an UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?](http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql) – Craig Ringer Aug 15 '13 at 09:23
  • @Craig Ringer: I'm sorry for saying you wrote that, I edited the original post... btw, just a question, how come it is "not concurrency safe" when I have a separate query for getting new ID? Won't that ensure no concurrency problem? AFAIK NEXTVAL ensures new ID is given every time, isn't that the case? – Adrian Aug 15 '13 at 09:37
  • Here's another useful link about upsert in PostgreSQL - http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql – Roman Pekar Aug 15 '13 at 09:53
  • 1
    @Adrian You're right that if you *know* you're doing an insert and get a new ID, the upsert is safe but it's also pointless and inefficient. Your app knows that it can issue an `INSERT` or an `UPDATE`, it doesn't need to jump through hoops to upsert if it knows whether or not it's creating the record. – Craig Ringer Aug 15 '13 at 12:39
  • @Craig Ringer - you're absolutely right, I just refactored my code so it does the necessary SQL command, based on given ID. Tnx – Adrian Aug 15 '13 at 13:23
  • @Craig Ringer - just another question, is it really necessary to even call NEXTVAL for getting me a unique ID, won't my basic INSERT command handle that stuff (postgreSQL does everything with queues isn't it?) ? I just got a quick look at my book for INSERT command, didn't find any warnings for that kind of stuff. – Adrian Aug 16 '13 at 06:18
  • @Adrian Typically you just omit the generated column from the insert-list, eg `INSERT INTO mytable(a,b,c) VALUES (...) RETURNING id`. Or specify `DEFAULT` explicitly: `INSERT INTO mytable(id,a,b,c) VALUES (DEFAULT, ...) RETURNING id;`. So in your case the explicit `nextval` appears to be unnecessary. – Craig Ringer Aug 16 '13 at 06:40

1 Answers1

1

as far as I know, you can do something like this in PostgreSQL:

with cte as (
    update table1 set
       field='C', field2='Z'
    where id=3
    returning *
)
insert into table1 (id, field, field2)
select 3, 'C', 'Z'
where not exists (select * from cte);
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197