Обсуждение: SQL Newbie Question

Hello Folks

Have a look at this Table:

CREATE TABLE foo(
id serial,
a_name text,
CONSTRAINT un_name UNIQUE (a_name));

Obviously, inserting a string twice results in an error (as one would
expect). But: is there any known possibility to ingnore an errorneous
INSERT like SQLite's "conflict algorithm" (SQLite:"INSERT OR
[IGNORE|ABORT] INTO foo [...]")?
I tried to use a trigger before INSERT takes place, but it seems that
before firing a trigger the constraints are checked...
Background: I'd like to INSERT a few thousand lines in one transaction,
where some values will be appear twice.

thx in Advance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/25/07 09:30, Inoqulath wrote:
> Hello Folks
>
> Have a look at this Table:
>
> CREATE TABLE foo(
> id serial,
> a_name text,
> CONSTRAINT un_name UNIQUE (a_name));
>
> Obviously, inserting a string twice results in an error (as one would
> expect). But: is there any known possibility to ingnore an errorneous
> INSERT like SQLite's "conflict algorithm" (SQLite:"INSERT OR
> [IGNORE|ABORT] INTO foo [...]")?
> I tried to use a trigger before INSERT takes place, but it seems that
> before firing a trigger the constraints are checked...
> Background: I'd like to INSERT a few thousand lines in one transaction,
> where some values will be appear twice.

No.

  Unique \U*nique"\, a. [F. unique; cf. It. unico; from L. unicus,
     from unus one. See {One}.]
     Being without a like or equal; unmatched; unequaled;
     unparalleled; single in kind or excellence; sole. --
     {U*nique"ly}, adv. -- {U*nique"ness}, n.
     [1913 Webster]


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFuNAMS9HxQb37XmcRAh4XAJ99ebAGyuHTFc9+bLiuW5ewPJkIYgCgguLP
1UDAUlXSGnZrKQb4Czoqp5w=
=Wm9P
-----END PGP SIGNATURE-----

Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 01/25/07 09:30, Inoqulath wrote:
>
>> Hello Folks
>>
>> Have a look at this Table:
>>
>> CREATE TABLE foo(
>> id serial,
>> a_name text,
>> CONSTRAINT un_name UNIQUE (a_name));
>>
>> Obviously, inserting a string twice results in an error ...is there any "conflict algorithm" (SQLite:"INSERT OR
>> [IGNORE|ABORT] INTO foo [...]")?...
>>
> No.
>
>   Unique \U*nique"\, a. [F. unique; cf. It. unico; from L. unicus,
>      from unus one. See {One}.]
>      Being without a like or equal; ...
>

I think he is not asking "How do I insert duplicate rows into a
unique-constrained column?", but rather that he wants to have the insert
transaction proceed successfully, ignoring the duplicates, i.e., the
resulting inserted rows will number less than the original source rows
by exactly the subset of duplicate source rows.

My suggestion would be to load the data into an unconstrained temporary
table, then select distinct from that for insertion into your actual
working table.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/25/07 09:54, btober@ct.metrocast.net wrote:
> Ron Johnson wrote:
>> On 01/25/07 09:30, Inoqulath wrote:
[snip]
> I think he is not asking "How do I insert duplicate rows into a
> unique-constrained column?", but rather that he wants to have the insert
> transaction proceed successfully, ignoring the duplicates, i.e., the
> resulting inserted rows will number less than the original source rows
> by exactly the subset of duplicate source rows.

Ah, ok.

> My suggestion would be to load the data into an unconstrained temporary
> table, then select distinct from that for insertion into your actual
> working table.

That works on tables of a few thousand rows.  Even a few million
rows.  Doesn't scale, though.

Savepoints might be a workaround, also.

Still, pg *really* needs a "not rollback on error" mode.  Throw the
exception, let the app handle it and keep on going.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFuNSWS9HxQb37XmcRAvoUAJ4r7RIxj+JH9gcZNadQrQFaI/NTnwCeM6Al
ZdpFvGuV4AemAYTXbY+Vgaw=
=GxBV
-----END PGP SIGNATURE-----

Good hint. I think that should work for me.
Thanks

(At last, now I know what "unique" means ;-)  )

On Jan 25, 2007, at 10:30 AM, Inoqulath wrote:
> CREATE TABLE foo(
> id serial,
> a_name text,
> CONSTRAINT un_name UNIQUE (a_name));
>
> Obviously, inserting a string twice results in an error (as one
> would expect). But: is there any known possibility to ingnore an
> errorneous INSERT like SQLite's "conflict
> algorithm" (SQLite:"INSERT OR [IGNORE|ABORT] INTO foo [...]")?
> I tried to use a trigger before INSERT takes place, but it seems
> that before firing a trigger the constraints are checked...
> Background: I'd like to INSERT a few thousand lines in one
> transaction, where some values will be appear twice.

No, though there's a TODO about how to handle MERGE that might
eventually do what you want. In the meantime you'll probably want to
load into a temp table and look for dupes.

Also, I recommend avoiding using 'id' as a field name. It's easy to
get confused when joining a number of tables together when you have
"bare" id's floating all over. Plus, if you use foo_id everywhere you
get to use the USING clause on joins.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)