Re: error tolerant inserting of data

On Wed, 5 Mar 2003, Jules Alberts wrote:

> Hello everybody,
>
> I'm working on a new pg database that will contain all data that's
> currently in our old system. The data is already in the db, in tables
> with names like import_table1, import_table2 etc., now I have to
> transfer them to the real tables. The real tables have some
> constraints, like trigger functions to prevent overlapping date-
> periods.
>
> I have written pl/pgsql code to do the importing bit, but there is one
> big drawback: as soon as anything in a function (or in a FOR ... SELECT
> ... LOOP) goes wrong, everything is rolled back. Normally this
> behaviour would be fine, but not in this situation, because my old data
> contains rows sometimes conflicts with the constraints.
>
> What I want is all conflicting rows to be not imported, just plainly be
> refused, and let everything that's OK be imported. What is the best way
> to do this?

I think the only way is to do the conflict testing in your code and not try the
insert if it's going to fail.

It'll slow the process down but if you think in terms of issuing a begin at the
start of a application and you are hoping for a commit at the end then
obviously you have to protect against situations that you know will give rise
to an error. For example, if you have some data from a form that a user
completes when you come to save that data you may not know just from that data
that it a foreign key constraint will pass so you have to check that before
trying an insert otherwise or transaction will abort.

--
Nigel J. Andrews