Re: SQL Newbie Question

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)