Search Postgresql Archives

Re: Using the database to validate data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx
<mailto:tim.clarke@xxxxxxxxxxxxxx>>:

    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@xxxxxxxxxxxxxx
    <mailto:pgsql-general@xxxxxxxxxxxxxx>)
    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@xxxxxxxxxxx


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux