Re: error-tolerant COPY FROM

Sean Davis zei:
> I use a trigger on tables with foreign key references to either
> ignore
> the insert row or insert an appropriate matching row in the
> referenced
> table, if it does not exist. In the function, I just raise a notice
> that I am doing this. This is a simple example:
> create or replace function tgf_insert_gene_id() returns trigger as
> $$
> declare
> gene_id_chk integer;
> begin
> select into gene_id_chk gene_id from g_main where
> gene_id=NEW.gene_id;
> if (NOT FOUND) THEN
> BEGIN
> INSERT into g_main (gene_id,name) values
> (NEW.gene_id,'INSERTED FOR FOREIGN KEY CHECK');
> RAISE NOTICE 'Inserting a gene ID to meet foreign
> key
> requirements';
> END;
> END IF;
> return NEW;
> end;$$ language plpgsql;
>
> Just make a trigger for the tables of interest (I have several
> tables
> that reference gene_id in g_main) so they can all use the same
> trigger.
>
> This function simply looks up the proposed key to the foreign key
> table
> (gene_id) in the foreign key table. If it is (NOT FOUND) then it
> inserts the key into the foreign table (g_main) the gene_id about to
> be
> inserted and then raises a notice (which goes into the log if you
> have
> logging set up to log notices). Then, the function returns and,
> because we did the insert, foreign key checks work fine. You could
> just as easily choose to NOT insert a record that doesn't meet
> foreign
> key checks, or even do an insert into a separate table instead that
> contains all of the rows that don't match a foreign key.
>
> Hope this helps....
> Sean

Thanks Sean, but in my situation I don't want the database to be so
versatile as to handle all the errors itself, this would create
unneccesary load during all operations on the tables, not only when
running the import.

But I'm getting lots of great feedback from the list, thanks everyone!