472,952 Members | 1,835 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Duplicate key insert question

I have a table with a primary field and a few other fields. What is the
fastest way to do an insert into that table assuming that sometimes I
might try to insert a record with a duplicate primary key and want that
to fail?

I know that if I try a plain insert this will work, but in the case
where I am trying to insert a duplicate key, the insert fails (as it
should) and an error is logged.

I could first do a check to see if there is already an entry with the
same key as the one I am trying to insert but that would mean quite a
few extra operations.

Is there a quick and clean way of doing something like:

INSERT into table values(...) IF there isn't already a row with pkey=x

Thanks,

Jean-Christian Imbeault

PS The reason I am asking is that an open source project is using MySQL
as their DB and they have a port to PG that isn't very clean b/c the DB
code keeps trying to insert duplicate primary keys. According to them
MySQL doesn't complain and just drops the insert whereas PG (as is
right) complains. I've offered to clean up their PG insertion code but
they say that they don't want too many extra checks as their app writes
to the DB a *lot* and any extra check is going to slow down the
application noticeably ...
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
11 7444
On Tuesday 01 July 2003 08:10 pm, Jean-Christian Imbeault wrote:
I have a table with a primary field and a few other fields. What is the
fastest way to do an insert into that table assuming that sometimes I
might try to insert a record with a duplicate primary key and want that
to fail?

I know that if I try a plain insert this will work, but in the case
where I am trying to insert a duplicate key, the insert fails (as it
should) and an error is logged.

I could first do a check to see if there is already an entry with the
same key as the one I am trying to insert but that would mean quite a
few extra operations.

Is there a quick and clean way of doing something like:

INSERT into table values(...) IF there isn't already a row with pkey=x

Thanks,

Jean-Christian Imbeault


Hi, not sure if this is answering your question, but I just asked similar
questions here. I asked about using INSERT WHERE NOT EXISTS (which you can do
in PostgreSQL). Here is what you can do:

INSERT INTO mytable
SELECT 'value1', 'value2'
WHERE NOT EXISTS
(SELECT NULL FROM mytable
WHERE mycondition)

This will just return 0 when fails, but it does check first. Don't know if you
can really afford that. Just for reference, this brought up some discussion
here. Here is a link to the archive:
http://marc.theaimsgroup.com/?l=post...NOT+EXISTS&q=b

Hope that helps.
RDB
--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2
On Wed, Jul 02, 2003 at 09:58:28AM +0900, Jean-Christian Imbeault wrote:
Alvaro Herrera wrote:

No, only the "second" one will fail (though it's difficult which one is
the second)


I couldn't get the link to work so I couldn't read why Tom shot it down.
But if Tom shot down this idea down ... then it mustn't be correct.


The thread is here:
http://groups.google.com/groups?hl=e...EXISTS%26ie%3D

The solution is not correct in that there _is_ a race condition.
You should check the returned value from the insertion
function to see if it succeeded or not.


No, what I want if to have one query that will *always* insert if there
is no record with this primary key and *always* do nothing (not fail,
not generate an error) if there is already a record with this primary
key. I don't want to check return values :)


No way.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No es bueno caminar con un hombre muerto"

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

http://archives.postgresql.org

Nov 11 '05 #3
Alvaro Herrera wrote:

No, only the "second" one will fail (though it's difficult which one is
the second)
From:

http://marc.theaimsgroup.com/?l=post...6988915991&w=2

Ian Barwick wrote:

[...]

I proposed that same solution 3 years ago. Tom shoots it down:

[...]

I couldn't get the link to work so I couldn't read why Tom shot it down.
But if Tom shot down this idea down ... then it mustn't be correct.
If I followed all the arguments correctly according to the thread there
is *no* way to do what I (and you ;) want in one simple query.

No, there's not.


You say no, but at first you say that the proposed method works. The
proposed method, if it is correct, is simple enough for me. By simple I
mean all can be done with one query.
You should check the returned value from the insertion
function to see if it succeeded or not.


No, what I want if to have one query that will *always* insert if there
is no record with this primary key and *always* do nothing (not fail,
not generate an error) if there is already a record with this primary
key. I don't want to check return values :)

Thanks,

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

Nov 11 '05 #4
On Tuesday 01 July 2003 08:45 pm, Jean-Christian Imbeault wrote:
Reuben D. Budiardja wrote:
Hi, not sure if this is answering your question, but I just asked similar
questions here. I asked about using INSERT WHERE NOT EXISTS (which you
can do in PostgreSQL). Here is what you can do:

INSERT INTO mytable
SELECT 'value1', 'value2'
WHERE NOT EXISTS
(SELECT NULL FROM mytable
WHERE mycondition)

http://marc.theaimsgroup.com/?l=post...s=WHERE+NOT+EX
ISTS&q=b


Thanks for the link!

I read the thread and it looks like even the above solution is not
perfect because of a possible race condition where two inserts trying to
insert a row with a pk not in the table will both get think it is ok to
do so, try it and then both will fail?


No, onlu *one* of them will fail, but yes, the other will then generate error.
So it really is a trade off. Another way would be to lock the table, as other
has suggested. But then there is disadvantages to that also.

RDB
--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #5
Reuben D. Budiardja wrote:

No, onlu *one* of them will fail, but yes, the other will then generate error.
So it really is a trade off. Another way would be to lock the table, as other
has suggested. But then there is disadvantages to that also.


Really? I just got a post form Alvaro Herrera saying;

"The solution is not correct in that there _is_ a race condition."

Maybe I misunderstood, but "not correct" doesn't sound good :)

Jean-Christian Imbeault
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #6
Alvaro Herrera wrote:

Well, he is right. One will fail, the other will not. The race
condition is for the application. If you want to ignore it, you can do
that, but there _will_ be an ERROR thrown and the transaction will be
aborted.


Ah ... then maybe this solution is 'good enough'. It will still generate
an error message some of the time (when there is a race condition) but
will definitely generate fewer error messages than the current method
used which is just to do the insert and let it fail if there is already
a record with the same primary key.

Thanks for the help!

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

Nov 11 '05 #7
On Wed, Jul 02, 2003 at 10:25:54AM +0900, Jean-Christian Imbeault wrote:
Reuben D. Budiardja wrote:

No, onlu *one* of them will fail, but yes, the other will then generate error.
So it really is a trade off. Another way would be to lock the table, as other
has suggested. But then there is disadvantages to that also.


Really? I just got a post form Alvaro Herrera saying;

"The solution is not correct in that there _is_ a race condition."

Maybe I misunderstood, but "not correct" doesn't sound good :)


Well, he is right. One will fail, the other will not. The race
condition is for the application. If you want to ignore it, you can do
that, but there _will_ be an ERROR thrown and the transaction will be
aborted. The other transaction _will_ insert the tuple, though, and it
won't be aborted.

Note that for the race condition to show there has to be a race, i.e.
two backends trying to insert the same primary key at the same time. If
one finishes half a second before the other, they will behave that way
you want, i.e. there will one tuple inserted and no error generated.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #8
On Tuesday 01 July 2003 09:25 pm, Jean-Christian Imbeault wrote:
Reuben D. Budiardja wrote:
No, onlu *one* of them will fail, but yes, the other will then generate
error. So it really is a trade off. Another way would be to lock the
table, as other has suggested. But then there is disadvantages to that
also.


Really? I just got a post form Alvaro Herrera saying;

"The solution is not correct in that there _is_ a race condition."

Maybe I misunderstood, but "not correct" doesn't sound good :)

If you want to avoid the race condition as well, then use the locking
mechanism for transaction. Combine it with the previous INSERT ... SELECT ...
WHERE NOT EXISTS, it should give you what you want. I suspect it's slower
though. Eg:

BEGIN WORK;

INSERT INTO mytable
SELECT 'value1', 'value2'
WHERE NOT EXISTS
(SELECT NULL FROM mytable
WHERE mycondition)

COMMIT WORK;

This should solve the Race Condition, since other transaction have to wait.
But if the PK already exists, this will quit without error.

RDB

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #9
On Tuesday 01 July 2003 11:08 pm, Jean-Christian Imbeault wrote:
Reuben D. Budiardja wrote:
INSERT INTO mytable
SELECT 'value1', 'value2'
WHERE NOT EXISTS
(SELECT NULL FROM mytable
WHERE mycondition)


Thank you to everyone who helped out on my question. I am trying to
implement the above solution but I'm having problems getting this to
work when I want to insert more than one value:

TAL=# create table b (a text primary key, b text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey'
for table 'b'
CREATE TABLE
TAL=# insert into b select 'a';
INSERT 335311 1
TAL=# insert into b select 'b', select 'b';
ERROR: parser: parse error at or near "select" at character 27


I don't see what you're trying to do. Why do you have two select ?

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

Nov 11 '05 #10
On Wed, Jul 02, 2003 at 12:08:56 +0900,
Jean-Christian Imbeault <jc@mega-bucks.co.jp> wrote:
TAL=# insert into b select 'b', select 'b';
ERROR: parser: parse error at or near "select" at character 27


You probably want:
insert into b select 'b', 'b';

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

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

Nov 11 '05 #11
On Wed, 2 Jul 2003, Jean-Christian Imbeault wrote:
I have a table with a primary field and a few other fields. What is the
fastest way to do an insert into that table assuming that sometimes I
might try to insert a record with a duplicate primary key and want that
to fail?

I know that if I try a plain insert this will work, but in the case
where I am trying to insert a duplicate key, the insert fails (as it
should) and an error is logged.

I could first do a check to see if there is already an entry with the
same key as the one I am trying to insert but that would mean quite a
few extra operations.

Is there a quick and clean way of doing something like:

INSERT into table values(...) IF there isn't already a row with pkey=x

Thanks,

Jean-Christian Imbeault

PS The reason I am asking is that an open source project is using MySQL
as their DB and they have a port to PG that isn't very clean b/c the DB
code keeps trying to insert duplicate primary keys. According to them
MySQL doesn't complain and just drops the insert whereas PG (as is
right) complains. I've offered to clean up their PG insertion code but
they say that they don't want too many extra checks as their app writes
to the DB a *lot* and any extra check is going to slow down the
application noticeably ...


Skipping ahead without reading all the messages in this thread (got to rush
out) what about using a before insert trigger, doing the check in there,
returning null if the insert would fail and see if they complain about the slow
down :)

Of course it's still got the race condition for the application unless you also
lock the table and it'll screw up any use of currval(sequence) afterwards that
expects to get the id of the row inserted with a id obtained from
nextval(sequence)

Nigel Andrews

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #12

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

Similar topics

5
by: TThai | last post by:
HI, I'm trying to insert records to a table using bcp command. The problem is the input file to the bcp is a text file that looks like this: Text file data: 1234 abc def ghi jkl mno ...
1
by: Mattias B | last post by:
Hello! I have a question about how stl's insert works on containers and on vector in particular. The question is: is it OK to insert a value before end() with something like: vector<int>...
20
by: akej via SQLMonster.com | last post by:
Hi, i have table with 15 columns CREATE TABLE . ( PRIMARY KEY , NULL , NULL , NULL , NULL , (50) NULL , NULL
2
by: Daniel Tan | last post by:
I got a syntax error in SQL insert into statement , hope someone can help me .Thanks. job_search = "='" & Me.Jobno & "' " sqlstr = "INSERT INTO Custorder (job_order) " & _ "values " &...
2
by: Russ Schneider | last post by:
I downloaded the port for Postgres for PHP-NUKE. All insert statements are as follows: For table: Table "nuke_referer" Column | Type | Modifiers...
9
by: David Eades | last post by:
Hi all Complete newbie here, so apologies if this is the wrong forum. I've been asked to use mysql and asp to make a simple bidding system (rather like a simple ebay), whereby users can use a...
2
josie23
by: josie23 | last post by:
Egad, I'm not a coder/programmer by nature or occupation but understand things like html and css and a small amount of perl. So, basically, I'm a perl/mysql imbecile. But, I've been trying to...
1
by: PrakashRS | last post by:
Given the table T1, created by: CREATE TABLE t1 (id INTEGER GENERATED BY DEFAULT AS IDENTITY, c1 CHAR(3) ) The following SQL statements are issued: INSERT INTO t1 VALUES (1, 'ABC') INSERT INTO...
0
by: Wayne L | last post by:
I have a table with Field1, Field2 Field Two may have 2 or more duplicate values. I want to insert a 0 (zero) in field one in a new row after the duplicate values in field 2. 11111 ...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

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.