Re: Using the database to validate data

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(dot)clarke(at)manifest(dot)co(dot)uk
> <mailto: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
> <mailto: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/
> ───────────────────────────

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com