473,836 Members | 1,416 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
11 7531
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(sequenc e) afterwards that
expects to get the id of the row inserted with a id obtained from
nextval(sequenc e)

Nigel Andrews

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

Nov 11 '05 #12

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

Similar topics

5
4717
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 Expected result: column1 1234
1
1907
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> v; v.insert(v.end(), 1);
20
8585
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
1273
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 " & (job_search) & ";" Regards, Daniel
2
2491
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 --------+------------------------+---------------------------------------------------------- rid | integer | not null default nextval('"nuke_referer_rid_seq"'::text)
9
3224
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 web browser to view a highest bid and can make a bid. My question is; how can I be sure that when a user submits a bid, that another user isn't also currently submittimg a bid, i.e i can tell user A
2
7051
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 find syntax to insert values into a mysql database table. I'm able to use the below syntax to insert hard-coded values like 'josie' and 'smith' but can't find working syntax to insert $scalar data from another file (which is really what perl-mysql is...
1
3753
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 t1 VALUES (5, 'DEF') Can anyone tell what is the value that is being inserted into the ID column by the following statement?
0
1007
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 11111 0 22222 22222 22222 0
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9665
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10835
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
7785
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5818
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4007
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.