The PostgreSQL / MERGE discussion is a heated one for the PostgreSQL
developers (from what I know). Being a highly standards-compliant database,
it is a bit of an oddity that this powerful statement is not yet supported.
Even Derby is about to support it in the next release (joining CUBRID, DB2,
Firebird, HSQLDB, Oracle, SQL Server, and Sybase SQL Anywhere):
http://blog.jooq.org/2013/08/16/apache-derby-about-to-adopt-the-awesome-sql2003-merge-statement/
In the mean time, you can probably tweak your statement semantics to
perform a single-statement MERGE through:
INSERT INTO table (...)
SELECT ...
FROM (
SELECT ...
FROM merge_source
WHERE NOT EXISTS (
UPDATE table
SET ...
WHERE ...
RETURNING
)
)
The above is pseudo-PostgreSQL. You'll have to experiment a little to see
how you can get it working. Maybe there is a formal way to transform SQL
MERGE into PostgreSQL INSERT .. SELECT .. NOT EXISTS .. UPDATE?
Note that PostgreSQL probably doesn't know the MERGE semantics of this
statement, so race conditions might occur. In general, you're probably
better off with the insert / on exception update approach.
Cheers
Lukas
2014-07-07 20:18 GMT+02:00 Raman Gupta <[email protected]>:
> PostgresQL does not support MERGE though (
> http://wiki.postgresql.org/wiki/SQL_MERGE). So I think if one is using
> PostgresQL one would generally fall back to the exception/update approach.
> Is that right?
>
> Regards,
> Raman
>
>
> On Wednesday, June 25, 2014 6:42:00 AM UTC-4, Deven Phillips wrote:
>>
>> I have had MANY co-workers and colleagues make that statement about
>> MySQL... I think it just indicates laziness... Oh well, perhaps I am overly
>> critical because I am such a huge fan of PostgreSQL..
>>
>> Deven
>>
>> On Wednesday, June 25, 2014 4:05:09 AM UTC-4, Lukas Eder wrote:
>>>
>>>
>>> 2014-06-24 18:15 GMT+02:00 Deven Phillips <[email protected]>:
>>>
>>>> It always annoys me when people say they MUST use MySQL because it has
>>>> UPSERT...
>>>>
>>>
>>> People do say that?
>>>
>>>
>>>> UPSERT is STUPID, but implementing that capability using a SQL standard
>>>> like MERGE is VERY NICE!
>>>>
>>>
>>> I thought so as well :)
>>> We're also emulating INSERT IGNORE ... using MERGE, in fact. Except that
>>> the jOOQ syntax is INSERT .. ON DUPLICATE KEY IGNORE
>>>
>> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
