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>
User-agent: mu4e 1.10.7; emacs 29.1
From: zhihuifan1213@163.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Gustafsson <daniel@yesql.se>, Damir <dam.bel07@gmail.com>,
 torikoshia <torikoshia@oss.nttdata.com>, PostgreSQL Hackers
 <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>,
 anisimow.d@gmail.com, HukuToc@gmail.com, Andrey Lepikhov
 <a.lepikhov@postgrespro.ru>, Alena Rybakina <lena.ribackina@yandex.ru>,
 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
In-reply-to: <752672.1699474336@sss.pgh.pa.us>
Message-ID: <878r77mtlt.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/878r77mtlt.fsf%40163.com>
Precedence: bulk


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