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