Re: Using the database to validate data

From: 林士博 <lin(at)repica(dot)co(dot)jp>
To: Tim Clarke <tim(dot)clarke(at)manifest(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using the database to validate data
Date: 2015-07-24 03:20:49
Message-ID: CACudzGgft6bGODGmeGgYnROoJ9Qk0sw1qp5OCr9=QS3MQhZrfQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

By the way, this is about programming but not postgresql.

2015-07-24 5:58 GMT+09:00 Tim Clarke <tim(dot)clarke(at)manifest(dot)co(dot)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(at)postgresql(dot)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/
───────────────────────────

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-07-24 04:09:08 Re: Using the database to validate data
Previous Message Rich Shepard 2015-07-24 01:49:33 Re: Schema Help Needed To Get Unstuck