Re: Using the database to validate data

From: JPLapham <lapham(at)jandr(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Using the database to validate data
Date: 2015-07-24 13:51:32
Message-ID: 1437745892647-5859237.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

林士博 wrote
> If I am following correctly, you can do it in your application as follows.
> 1.begin transaction
> 2.insert each data. Catch db exception,
> and save exception message and other information you need to array.
> 3.in the end ,you can get all the information about the wrong data in
> array
> if there is any.
> and then you can decide whether it is need to rollback or to commit.

Yes, I agree that I could do that, which I believe is my "IDEA 1" from my
original message. This method will naturally work, but it is a very slow
iterative process because you can only catch the *first* error, after which
new INSERTS are not allowed. If you have a data input with say 1000 record,
and there are 50 errors, it would require 50 iterations of fixing the input
data, running it again, to find them all.

林士博 wrote
> By the way, this is about programming but not postgresql.

I was hoping that there would be a way to have Postgresql run in a mode
where it allows INSERTS within a transaction even after an error. Naturally
when the error condition occurs, COMMIT would not be allowed at the end of
the transaction block.

But, this way, you could collect all the error information in one pass.
Seemed postgresql related to me. :)

--
View this message in context: http://postgresql.nabble.com/Using-the-database-to-validate-data-tp5859046p5859237.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message JPLapham 2015-07-24 13:55:10 Re: Using the database to validate data
Previous Message Adrian Klaver 2015-07-24 13:36:55 Re: Delete rule does not prevent truncate