On Thu, Nov 9, 2023 at 4:12 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Daniel Gustafsson <dan...@yesql.se> writes: > >> On 8 Nov 2023, at 19:18, Tom Lane <t...@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 > > 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 >
just SAVE ERRORS automatically create a table to hold the error. (validate auto-generated table name uniqueness, validate create privilege). and the table will have the above related info. if no error then table gets dropped.
