|
Home > Archive > PostgreSQL Discussion > October 2006 > skip duplicate key error during inserts
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
skip duplicate key error during inserts
|
|
| Jean-Christophe Roux 2006-10-25, 8:21 am |
| Hello,
I have a table like this:
create table dummy (value integer primary key);
and I insert a row like this
insert into dummy values(0);
then I want to insert three rows:
insert into dummy values(0);
insert into dummy values(1);
insert into dummy values(2);
none of them will be inserted because the first insert is a primary key violation. How can I have postgreSQL not mind about the error and proceed to the next insert. I could send the inserts one at a time but bundling them speeds up the process..
Thanks
| |
| Merlin Moncure 2006-10-25, 8:21 am |
| On 10/20/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:
> Hello,
> I have a table like this:
> create table dummy (value integer primary key);
> and I insert a row like this
> insert into dummy values(0);
> then I want to insert three rows:
> insert into dummy values(0);
> insert into dummy values(1);
> insert into dummy values(2);
> none of them will be inserted because the first insert is a primary key
> violation. How can I have postgreSQL not mind about the error and proceed to
> the next insert. I could send the inserts one at a time but bundling them
> speeds up the process.
> Thanks
if this is bulk insert,
1. insert into scratch table:
create temp table scratch_foo as select * from foo limit 0;
2. insert into scratch, etc.
3. insert into foo select distinct on * from scratch_foo <-- season to taste
merlin
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Jeffrey Webster 2006-10-25, 8:21 am |
| On 10/20/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:
>
> Hello,
> [snip]
>
none of them will be inserted because the first insert is a primary key
> violation. How can I have postgreSQL not mind about the error and proceed to
> the next insert. I could send the inserts one at a time but bundling them
> speeds up the process.
> Thanks
>
>
I feel your pain... However, there is no way to do this (currently?). Some
possible solutions had been suggested some time back by a colleague, but
they were rejected. It requires a source code change (and not a simple one,
at that) to implement.
Until a concensus is reached by the primary contributors there is no easy
answer.
We've resorted to batch loading and parsing error messages to load all data
between primary key violations. It's still considerably more efficient than
single inserts.
(we've got the code to accomplish what you're asking about in the attic...
hopefully something will come of it some day.)
Best of luck.
| |
| Ron Johnson 2006-10-25, 8:21 am |
| -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 10/22/06 04:39, Jeffrey Webster wrote:
> On 10/20/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:
>
>
>
> none of them will be inserted because the first insert is a primary key
> I feel your pain... However, there is no way to do this (currently?). Some
> possible solutions had been suggested some time back by a colleague, but
> they were rejected. It requires a source code change (and not a simple
> one,
> at that) to implement.
>
> Until a concensus is reached by the primary contributors there is no easy
> answer.
>
> We've resorted to batch loading and parsing error messages to load all data
> between primary key violations. It's still considerably more efficient
> than
> single inserts.
>
> (we've got the code to accomplish what you're asking about in the attic...
> hopefully something will come of it some day.)
The generalized version of this issue (transaction totally fails on
any error) is extremely painful.
Most RDBMSs (well, ok, the other RDBMSs that *I* have worked with)
don't do that, and there's a lot of code written in the form:
INSERT INTO ...
IF PK-ERROR THEN
UPDATE
END-IF.
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFO0buS9HxQb37
XmcRAughAKCrD6o+ibwr
7fclE+wBXnUgX3tNDwCg
w8Or
5rGcfhYoAH8giSjwwSqH
Je8=
=aczL
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Gurjeet Singh 2006-10-27, 7:26 pm |
| If you are using psql, I'd recommend using '\set ON_ERROR_ROLLBACK on'.
HTH
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
On 10/22/06, Ron Johnson <ron.l.johnson@cox.net> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 10/22/06 04:39, Jeffrey Webster wrote:
> them
> (currently?). Some
> easy
> data
> attic...
>
> The generalized version of this issue (transaction totally fails on
> any error) is extremely painful.
>
> Most RDBMSs (well, ok, the other RDBMSs that *I* have worked with)
> don't do that, and there's a lot of code written in the form:
>
> INSERT INTO ...
> IF PK-ERROR THEN
> UPDATE
> END-IF.
>
> - --
> Ron Johnson, Jr.
> Jefferson LA USA
>
> Is "common sense" really valid?
> For example, it is "common sense" to white-power racists that
> whites are superior to blacks, and that those with brown skins
> are mud people.
> However, that "common sense" is obviously wrong.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
>
> iD8DBQFFO0buS9HxQb37
XmcRAughAKCrD6o+ibwr
7fclE+wBXnUgX3tNDwCg
w8Or
> 5rGcfhYoAH8giSjwwSqH
Je8=
> =aczL
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
| |
| Ron Johnson 2006-10-27, 7:26 pm |
| -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
That doesn't help at all during multi-table transactions or
bulk-loads where you want the loader to kick duplicates out to an
exception file.
On 10/27/06 04:41, Gurjeet Singh wrote:
& #91;snip][color=dark
red]
> The generalized version of this issue (transaction totally fails on
> any error) is extremely painful.
>
> Most RDBMSs (well, ok, the other RDBMSs that *I* have worked with)
> don't do that, and there's a lot of code written in the form:
>
> INSERT INTO ...
> IF PK-ERROR THEN
> UPDATE
> END-IF.
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFQde1S9HxQb37
XmcRAnqJAKCGgg3CDeGW
uvKNFBZCbrAdSESqjACd
FZSI
virSEhXIR8SVDK2CXKbg
N3Y=
=0Qrc
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
| |
| Gurjeet Singh 2006-10-27, 7:26 pm |
| On 10/27/06, Ron Johnson <ron.l.johnson@cox.net> wrote:
That doesn't help at all during multi-table transactions
What problem do you think you would face in multi-table scenario? I tried
the following and it worked for me; hope this is what you meant by
multi-table transactions:
postgres=# begin;
BEGIN
postgres=# create table t1 ( a int primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
CREATE TABLE
postgres=# create table t2 ( a int primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for
table "t2"
CREATE TABLE
postgres=# \set ON_ERROR_ROLLBACK on
postgres=# insert into t1 values ( 1 );
INSERT 0 1
postgres=# insert into t1 values ( 1 );
ERROR: duplicate key violates unique constraint "t1_pkey"
postgres=# insert into t2 values ( 1 );
INSERT 0 1
postgres=# insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
postgres=# commit;
COMMIT
postgres=# select * from t1;
a
---
1
(1 row)
postgres=# select * from t2;
a
---
1
(1 row)
postgres=#
want the loader to kick duplicates out to an
> exception file.
>
Now you are asking for a completely new feature!!!
Regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
| |
| Ron Johnson 2006-10-27, 7:26 pm |
| | |
| Gurjeet Singh 2006-10-27, 7:26 pm |
| On 10/27/06, Ron Johnson <ron.l.johnson@cox.net> wrote:
>
> Dueling examples. Attached are two examples of errors.
I think you completely missed that I am recommending using '\set
ON_ERROR_ROLLBACK on' in psql.
Please refer to my previous post and see the effect of the following line:
postgres=# \set ON_ERROR_ROLLBACK on
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
| |
| Ron Johnson 2006-10-27, 7:26 pm |
| -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 10/27/06 06:48, Gurjeet Singh wrote:
> On 10/27/06, Ron Johnson <ron.l.johnson@cox.net> wrote:
>
>
> I think you completely missed that I am recommending using '\set
> ON_ERROR_ROLLBACK on' in psql.
>
> Please refer to my previous post and see the effect of the following line:
>
> postgres=# \set ON_ERROR_ROLLBACK on
But I do *not* want my whole transaction to roll back!!
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFQgH8S9HxQb37
XmcRAsW+AKCOzz6WidLY
lS96oacxCL0qbaxfOACg
qz/H
/ RA16NFFwi82JyibP58tm
UI=
=jNzL
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
| |
| Alexander Staubo 2006-10-27, 7:26 pm |
| On Oct 27, 2006, at 14:56 , Ron Johnson wrote:
>
> But I do *not* want my whole transaction to roll back!!
That is not what is happening. From the documentation:
> ON_ERROR_ROLLBACK
> When on, if a statement in a transaction block generates an error,
> the error is ignored and the transaction continues. When
> interactive, such errors are only ignored in interactive sessions,
> and not when reading script files. When off (the default), a
> statement in a transaction block that generates an error aborts the
> entire transaction. The on_error_rollback-on mode works by issuing
> an implicit SAVEPOINT for you, just before each command that is in
> a transaction block, and rolls back to the savepoint on error.
So with on_error_rollback the transaction continues regardless of
errors:
# begin;
# \set ON_ERROR_ROLLBACK on
# insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
alex # insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
With on_error_rollback disabled, the transaction is implicitly aborted:
# begin;
# \set ON_ERROR_ROLLBACK on
# insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
# insert into t2 values ( 1 );
ERROR: current transaction is aborted, commands ignored until end of
transaction block
The wording of the option (in combination with the value "on") is
admittedly confusing. It's really "on_error_continue".
Alexander.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Ron Johnson 2006-10-27, 7:26 pm |
| -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 10/27/06 08:41, Alexander Staubo wrote:
> On Oct 27, 2006, at 14:56 , Ron Johnson wrote:
>
>
> That is not what is happening. From the documentation:
>
That'll teach me not to assume what a statement means.
[color=darkred]
Hmmm.
I guess COPY will fail also, instead of throwing a warning and
continuing.
[color=darkred]
& #91;snip][color=dark
red]
>
> The wording of the option (in combination with the value "on") is
> admittedly confusing. It's really "on_error_continue".
Isn't that the truth. :\
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFQhxsS9HxQb37
XmcRAkhIAKDkLRIbOOlN
CN4nC1N2DllKfKo5wQCf
S/Gk
xHRyVEytS3cQK9y2F8bX
eGw=
=wzfH
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
|
|
|
|