| From: | Rick Gigger <rick(at)alpinenetworking(dot)com> |
|---|---|
| To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
| Cc: | Martijn van Oosterhout <kleptog(at)svana(dot)org>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>, josh(at)agliodbs(dot)com, pgsql-hackers(at)postgresql(dot)org, Jaime Casanova <systemguards(at)gmail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net> |
| Subject: | Re: MERGE vs REPLACE |
| Date: | 2005-11-16 21:29:13 |
| Message-ID: | 65136D3A-E69D-4DAD-9D1A-DD75467D7048@alpinenetworking.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
>> Conceptually, a MERGE statement is just a long string of INSERTs and
>> UPDATEs in the same transaction and I think we should treat it as
>> such.
I've just got one question about this. Everyone seems to be saying
that "try to insert and if that fails update" is the same as "try to
insert and if that fails delete and then insert".
What about the following scenario:
mytable
------------
id serial primary key,
a int4,
b int4,
data text
I've got an id field on every table because it simplifies a lot of
things (such as slony configuration for example)
But I've also got a unique key on (a, b) and if I was to do a merge I
would most likely do it in (a, b) not id.
If merge does a delete insert then it creates new values for the id
columns which could cause me problems. Basically any default fields
are going to change or for that matter any fields not specified would
be reinitialized whereas an update would leave them in place.
It seems to me that "try to update and if that fails insert" seems to
be the best approach for not messing with existing data. I guess
"try to insert and if that fails update" gets you the same effect.
- Rick Gigger
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dave Cramer | 2005-11-16 21:30:09 | PANIC: could not locate a valid checkpoint record |
| Previous Message | Tom Lane | 2005-11-16 21:22:52 | Re: Some array semantics issues |