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

On 07/23/2015 08:20 PM, 林士博 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 <http://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.
> By the way, this is about programming but not postgresql.

Not sure I am following, the OP was asking the best way to catch any
errors Postgres might throw using built in database features as much as
possible. That seems to to be Postgres related.

>
> 2015-07-24 5:58 GMT+09:00 Tim Clarke <tim.clarke@manifest.co.uk
> <mailto:tim.clarke@manifest.co.uk>>:
>
>     Shouldn't be too difficult to import those new rows into one table,
>     write a procedure that inserts them into the real table one by one and
>     logs the validation failure if any - committing good rows and rolling
>     back bad. In fact if you could then write the failures to a third table
>     with a completely relaxed (or no) validation?
>
>     Tim Clarke
>
>     On 23/07/15 21:48, Adrian Klaver wrote:
>      > On 07/23/2015 12:04 PM, Jon Lapham wrote:
>      >> On 07/23/2015 03:02 PM, Adrian Klaver wrote:
>      >>> http://pgloader.io/
>      >>
>      >> Ok, thanks, I'll look into pgloader's data validation abilities.
>      >>
>      >> However, my naive understanding of pgloader is that it is used to
>      >> quickly load data into a database, which is not what I am
>     looking to do.
>      >> I want to validate data integrity *before* putting it into the
>     database.
>      >> If there is a problem with any part of the data, I don't want
>     any of it
>      >> in the database.
>      >
>      > I misunderstood, I thought you just wanted  information on the rows
>      > that did not get in. pgloader does this by including the rejected
>     data
>      > in *.dat and the Postgres log of why it was rejected in *.log.
>      >
>      > <Thinking out loud, not tested>
>      >
>      > I could still see making use of this by using the --before
>      > <file_name>, where file_name contains a CREATE TEMPORARY TABLE
>      > some_table script that mimics the permanent table. Then it would load
>      > against the temporary table, write out any errors and then drop the
>      > table at the end. This would not put data into the permanent table on
>      > complete success though. That would require some magic in AFTER LOAD
>      > EXECUTE that I have not come up with yet:)
>      >
>      > <Thinking out loud, not tested>
>      >>
>      >> -Jon
>      >>
>      >
>      >
>
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> ─repica group──────────────────
> ▼ポイント×電子マネー×メールで店舗販促に必要な機能を全て提供！
> 【point+plus】http://www.repica.jp/pointplus/
>
> ▼フォローアップメールや外部連携に対応！
> 【mail solution】http://ms.repica.jp/
>
> ▼9年連続シェアNo.1 個人情報漏えい対策ソフト
> 【P-Pointer】http://ppointer.jp/
>
> ▼単月導入可能！AR動画再生アプリ
> 【marcs】http://www.arappli.com/service/marcs/
>
> ▼ITビジネスを創造しながら未来を創る
> 【VARCHAR】http://varchar.co.jp/
> ───────────────────────────


--
Adrian Klaver
adrian.klaver@aklaver.com