473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

What is the postgres version of mysql's "ON DUPLICATE KEY"

I have a table with columns
(product_id,related_product_id,related_counter)

If product A is related to product B then a record should be created,
if the record already exists then the related_counter should be
incremented.

This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY.
Standard or not, it is very usefull.

Is there a way to catch the insert error. For example...

INSERT INTO related_products (product_id,related_product_id) VALUES
(?,?);
IF (???error: duplicate key???) THEN
UPDATE related_products SET related_counter = related_counter + 1;
END IF;

-Nick
Nov 23 '05 #1
8 9439
Nick wrote:
I have a table with columns
(product_id,related_product_id,related_counter)

If product A is related to product B then a record should be created,
if the record already exists then the related_counter should be
incremented.

This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY.
Standard or not, it is very usefull.

Is there a way to catch the insert error. For example...

INSERT INTO related_products (product_id,related_product_id) VALUES
(?,?);
IF (???error: duplicate key???) THEN
UPDATE related_products SET related_counter = related_counter + 1;
END IF;

-Nick


With a rule you can do it easily ( never tried ).
Regards
Gaetano Mendola

Nov 23 '05 #2
UPDATE related_products SET related_counter = related_counter
WHERE .....

only updates if the record exists

INSERT (x,y,z) SELECT ?,?,1 WHERE NOT EXISTS (SELECT 1 FROM
related_products WHERE .....)

Inserts if the key does not exist.

On Sat, 11 Sep 2004 00:02:26 +0200, Gaetano Mendola <me*****@bigfoot.com> wrote:
Nick wrote:
I have a table with columns
(product_id,related_product_id,related_counter)

If product A is related to product B then a record should be created,
if the record already exists then the related_counter should be
incremented.

This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY.
Standard or not, it is very usefull.

Is there a way to catch the insert error. For example...

INSERT INTO related_products (product_id,related_product_id) VALUES
(?,?);
IF (???error: duplicate key???) THEN
UPDATE related_products SET related_counter = related_counter + 1;
END IF;

-Nick


With a rule you can do it easily ( never tried ).

Regards
Gaetano Mendola

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3
I may have short handed this to much. I will assume the product A has
an id of 1 and the related product B has an id of 2. You have a
default on related_counter of 1 I am assuming

INSERT INTO related_products (product_id,related_product_id)
SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
FROM related_products
WHERE
product_id = 1 AND related_product_id = 2)
The insert is plain enough but instead of using values you are getting
the data from the select statement. The select statement returns 1
row of constant values just like the doing the values however no row
is returned if the where clause is not met. If no row is returned
then nothing can be inserted therefore no error is returned.

So let's look at the where clause it is a if the subselect returns any
value then exists will be true but we invert that with the NOT. The
subselect returns 1 if a row already exists with product_id and
related_product_id other wise a null row is returned.

You can think of this as a INSERT if the key doesn't already exist.
If you still need more help just let me know :-)

On Sat, 11 Sep 2004 01:17:29 +0100, Ian Linwood
<ia*@dinwoodie.freeuk.com> wrote:
Hello Kevin,

Friday, September 10, 2004, 11:19:58 PM, you wrote:

KB> INSERT (x,y,z) SELECT ?,?,1 WHERE NOT EXISTS (SELECT 1 FROM
KB> related_products WHERE .....)

could someone walk me through this one? I do not understand it at all.
apologies for my cluelessness ;-)

--
Best regards,
Ian


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4
INSERT INTO related_products (product_id,related_product_id)
SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
FROM
related_products
WHERE
product_id = 1 AND related_product_id = 2)


Should not the SELECT be FOR UPDATE ?
because if no insert is done, the OP wanted to UPDATE the row, so it
should not be deleted by another transaction in-between...

Can the above query fail if another transaction inserts a row between the
SELECT and the INSERT or postgres guarantee that this won't happen ?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5
Pierre-Frédéric Caillaud wrote:
INSERT INTO related_products (product_id,related_product_id)
SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
FROM
related_products
WHERE
product_id = 1 AND related_product_id = 2)


Should not the SELECT be FOR UPDATE ?
because if no insert is done, the OP wanted to UPDATE the row, so it
should not be deleted by another transaction in-between...

Can the above query fail if another transaction inserts a row
between the SELECT and the INSERT or postgres guarantee that this
won't happen ?


There is no "between" a single statement.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6
There is no "between" a single statement.
Yes, I know, even if the statement involves mutiple subqueries...

I meant :
The OP wants to UPDATE if the row already exists, and to INSERT otherwise
; we have the INSERT bit here, but to UPDATE he needs to check if the
insert really took place, and if not, issue an UPDATE statement.. so that
makes it two statements.

By the way, do several consecutive queries inside a plpgsql function
count as one statement (the function call) or as several statements (ie.
inside a function are transactions like SERIALIZED ?)

On Sat, 11 Sep 2004 13:56:26 +0200, Peter Eisentraut <pe*****@gmx.net>
wrote:
Pierre-Frédéric Caillaud wrote:
> INSERT INTO related_products (product_id,related_product_id)
> SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
> FROM
> related_products
> WHERE
> product_id = 1 AND related_product_id = 2)


Should not the SELECT be FOR UPDATE ?
because if no insert is done, the OP wanted to UPDATE the row, so it
should not be deleted by another transaction in-between...

Can the above query fail if another transaction inserts a row
between the SELECT and the INSERT or postgres guarantee that this
won't happen ?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #7
Peter Eisentraut <pe*****@gmx.net> writes:
Pierre-Frédéric Caillaud wrote:
INSERT INTO related_products (product_id,related_product_id)
SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
FROM
related_products
WHERE
product_id = 1 AND related_product_id = 2)

Should not the SELECT be FOR UPDATE ?
because if no insert is done, the OP wanted to UPDATE the row, so it
should not be deleted by another transaction in-between...

Can the above query fail if another transaction inserts a row
between the SELECT and the INSERT or postgres guarantee that this
won't happen ?
There is no "between" a single statement.


Sure there is. In the above example, the EXISTS result will be correct
as of the time of the snapshot that was taken at the start of the
command (or the start of the whole transaction, if using SERIALIZABLE
mode). So it is *entirely* possible for the INSERT to fail on duplicate
key if some other transaction commits a conflicting row concurrently.

AFAIK, all the bulletproof solutions for this sort of problem involve
being prepared to recover from a failed insertion. There are various
ways you can do that but they all come down to needing to catch the
duplicate key error. In the past you have had to code that in
client-side logic. In 8.0 you could write a plpgsql function that
catches the exception.

Given the need for a test anyway, I think the WHERE NOT EXISTS above
is pretty much a waste of time. Just do an INSERT, and if it fails do
an UPDATE; or do an UPDATE, and if it fails (hits zero rows) then do
an INSERT, being prepared to go back to the UPDATE if the INSERT fails.
Which of these is better probably depends on how often you expect each
path to be taken.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #8
On Sat, 11 Sep 2004 11:27:02 -0400, Tom Lane <tg*@sss.pgh.pa.us> wrote:
There is no "between" a single statement.


Sure there is. In the above example, the EXISTS result will be correct
as of the time of the snapshot that was taken at the start of the
command (or the start of the whole transaction, if using SERIALIZABLE
mode). So it is *entirely* possible for the INSERT to fail on duplicate
key if some other transaction commits a conflicting row concurrently.

AFAIK, all the bulletproof solutions for this sort of problem involve
being prepared to recover from a failed insertion. There are various
ways you can do that but they all come down to needing to catch the
duplicate key error. In the past you have had to code that in
client-side logic. In 8.0 you could write a plpgsql function that
catches the exception.

Given the need for a test anyway, I think the WHERE NOT EXISTS above
is pretty much a waste of time. Just do an INSERT, and if it fails do
an UPDATE; or do an UPDATE, and if it fails (hits zero rows) then do
an INSERT, being prepared to go back to the UPDATE if the INSERT fails.
Which of these is better probably depends on how often you expect each
path to be taken.


It's not meant to be a bulletproof solution. It's meant to be a
syntactically equivalent to the MySQL statement. You still have to
check for a failure.

Do the update followed by the insert in a serial transaction. If the
transaction fails you redo the same SQL transaction. This eliminates
the need for a this query else this query scenario which is the whole
point of the MySQL bastard syntax in the first place. Not the best
solution but if you have a good DBA and bad programmers it might be
what you actually want.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: F C | last post by:
I have http://localhost/myweb1 who redirect me to my pages. I would like to have http://localhost/myweb2 which I would like to use the same exact set of pages, except a different global.asa...
0
by: jy2003 | last post by:
1. Does MyISAM support FOREIGN KEY constraints? When I read the online MySQL Reference Manual, it said "InnoDB also supports FOREIGN KEY constraints". Does it mean MyISAM does not support FOREIGN...
0
by: jacksuyu | last post by:
I have two xsd files, in one xsd file, I defined a "key", I'd like to use "keyref" to refer to that "key" from another xsd file. But I always get attribute is empty error. my.xsd is my first xsd...
2
by: microsoft | last post by:
When I create a instance of web services proxy in winform, I get the exception. Stack Trace: System.ArgumentException: Item has already been added. Key in dictionary:"winbootdir" Key being...
2
by: susanwinchell | last post by:
I have a table with only two columns (created from query of a much larger database). The first column contains many "duplicate" names or ID, and the second column contains values which need to be...
10
by: VB Programmer | last post by:
1. Is there an ASP.NET 2.0 version available yet? 2. Is there a VB.NET version available? 3. How do I install the source version on my dev machine (I have SQL Server 2000, VS.NET, IIS, etc...)...
0
by: Po Eddie Lim | last post by:
Hello... Is it possible in PostGre 7.3 to query the size of a text array attribute of a table? Does anyone know how this is queried in 7.3? thanks... help is greatly needed. eddie -----...
2
by: ericnyc | last post by:
Hi, I am a newbee in C++ Please review this is what I wrote , what so ever I understood so far, My question is that I have to " Write a program C++ array that reads in an integer number and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.