Re: Using the database to validate data - Mailing list pgsql-general

From 林士博
Subject Re: Using the database to validate data
Date
Msg-id CACudzGheqWkLMJ-H+WWrCQY15S7XW92+Gqhg8rrtBWC2rX=Rpg@mail.gmail.com
Whole thread Raw
In response to Re: Using the database to validate data  (JPLapham <lapham@jandr.org>)
List pgsql-general
No, when you catch an insert exception , you can save the information you needed to array or something likes that, and continue to do the next insert.
In your application,  you can write codes as follows.

begin transaction
loop
    try
        do insert
    catch exception
        save error info
end loop
if have any error
   print erro
   rollback transaction
else
  commit transaction

or you can you that in postgresql procedure.




2015-07-24 22:51 GMT+09:00 JPLapham <lapham@jandr.org>:
林士博 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.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
─repica group──────────────────
▼ポイント×電子マネー×メールで店舗販促に必要な機能を全て提供!

▼フォローアップメールや外部連携に対応!
【mail solution】http://ms.repica.jp/

▼9年連続シェアNo.1 個人情報漏えい対策ソフト
【P-Pointer】http://ppointer.jp/

▼単月導入可能!AR動画再生アプリ

▼ITビジネスを創造しながら未来を創る
───────────────────────────

pgsql-general by date:

Previous
From: Alex Magnum
Date:
Subject: Re: Sorting CSV string and removing Duplicates
Next
From: Spiros Ioannou
Date:
Subject: Re: Lots of stuck queries after upgrade to 9.4