pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: zhihuifan1213@163.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: Damir <dam.bel07@gmail.com>
Cc: torikoshia <torikoshia@oss.nttdata.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Cc: anisimow.d@gmail.com, HukuToc@gmail.com, Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Cc: Alena Rybakina <lena.ribackina@yandex.ru>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)
Date: Thu, 09 Nov 2023 12:33:34 +0800
Message-ID: <878r77mtlt.fsf@163.com> (raw)
In-Reply-To: <752672.1699474336@sss.pgh.pa.us>
References: <483E1B59-4A80-450B-8685-7CC12762FD5A@anarazel.de>
	<0DE0602F-CC12-40ED-B259-3AB91FB02C3B@yesql.se>
	<8ad8492ff9fae3481d87c7aab4e0aed0@oss.nttdata.com>
	<CALH1LguAEsoTYJTCsXNB-7z2Hu9UGEpsXA4kj0FOTmoP=6Wp3Q@mail.gmail.com>
	<F2BEB24D-A6AB-4BB1-AC75-9A060E5338E0@yesql.se>
	<9a94e684d6528dff74de5959e97032f3@oss.nttdata.com>
	<8e5c596e47435e3b37b7a751ebcd9569@oss.nttdata.com>
	<20230322175000.qbdctk7bnmifh5an@awork3.anarazel.de>
	<16e09747fcfbb21c30e4c1009c416aa4@oss.nttdata.com>
	<19551e8c2717c24689913083f841ddb5@oss.nttdata.com>
	<CALH1LguOY-VvJBWvjpLR2W3t0Z2p=LGSK5tzLrKwtzZ2UsqwGg@mail.gmail.com>
	<43399b33fe573f0548410893f41f84b6@oss.nttdata.com>
	<CALH1LgseNT7mxpGzQsQwOJz_UwYBo6L-4vR9tmJqb-WNPkt6Bg@mail.gmail.com>
	<8de9d5e1-2f81-ee2e-ece4-b1a6ede1f285@yandex.ru>
	<b74c1c3e3cc1c5d7ad5fb527b27d2b60@oss.nttdata.com>
	<c0fb57b82b150953f26a5c7e340412e8@oss.nttdata.com>
	<CALH1Lgtg39crESw644KDH+ejBWuW3Vospe67F-ZWXNeBMEE=PA@mail.gmail.com>
	<58fa3b63e88ffd0a09273e38aec0661e@oss.nttdat!>
	<B082C6BE-D2F2-4DD4-8649-E81E24A6840E@yesql.se>
	<752672.1699474336@sss.pgh.pa.us>


Tom Lane <tgl@sss.pgh.pa.us> writes:

> Daniel Gustafsson <daniel@yesql.se> writes:
>>> On 8 Nov 2023, at 19:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I think an actually usable feature of this sort would involve
>>> copying all the failed lines to some alternate output medium,
>>> perhaps a second table with a TEXT column to receive the original
>>> data line.  (Or maybe an array of text that could receive the
>>> broken-down field values?)  Maybe we could dump the message info,
>>> line number, field name etc into additional columns.
>
>> I agree that the errors should be easily visible to the user in some way.  The
>> feature is for sure interesting, especially in data warehouse type jobs where
>> dirty data is often ingested.
>
> I agree it's interesting, but we need to get it right the first time.
>
> Here is a very straw-man-level sketch of what I think might work.
> The option to COPY FROM looks something like
>
> 	ERRORS TO other_table_name (item [, item [, ...]])
>
> where the "items" are keywords identifying the information item
> we will insert into each successive column of the target table.
> This design allows the user to decide which items are of use
> to them.  I envision items like

While I'm pretty happy with the overall design, which is 'ERRORS to
other_table_name' specially. I'm a bit confused why do we need to
write the codes for (item [, item [, ...]]), not only because it
requires more coding but also requires user to make more decisions.
will it be anything wrong to make all of them as default? 

> LINENO	bigint		COPY line number, counting from 1
> LINE	text		raw text of line (after encoding conversion)
> FIELDS	text[]		separated, de-escaped string fields (the data
> 			that was or would be fed to input functions)
> FIELD	text		name of troublesome field, if field-specific
> MESSAGE	text		error message text
> DETAIL	text		error message detail, if any
> SQLSTATE text		error SQLSTATE code
>


-- 
Best Regards
Andy Fan






view thread (119+ messages)  latest in thread

Message-ID: <878r77mtlt.fsf@163.com>
Permalink:  ../878r77mtlt.fsf@163.com/
Also on:    postgresql.org/message-id/878r77mtlt.fsf@163.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: zhihuifan1213@163.com, tgl@sss.pgh.pa.us, daniel@yesql.se, dam.bel07@gmail.com, torikoshia@oss.nttdata.com, andres@anarazel.de, a.lepikhov@postgrespro.ru, lena.ribackina@yandex.ru, pgsql-hackers@lists.postgresql.org
  Subject: Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)
  In-Reply-To: <878r77mtlt.fsf@163.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox