About COPY command (and probably file fdw too) - Mailing list pgsql-general

From Stefan Stefanov
Subject About COPY command (and probably file fdw too)
Date
Msg-id CCDEFF456FB4404F8ED9E2C036BFD392@mbmbg.net
Whole thread Raw
Responses Re: About COPY command (and probably file fdw too)  (Nicolas Paris <niparisco@gmail.com>)
List pgsql-general
Hi,
 
I have been using COPY .. FROM a lot these days for reading in tabular data and it does a very good job.  Still there is an inconvenience when a (large) text file contains more columns than the target table or the columns’ order differs. I can imagine three ways round and none is really nice -
- mount the file as a foreign table with all the text file’s columns then insert into the target table a select from the foreign table;
- create an intermediate table with all the text file’s columns, copy into it from the file then insert into the target table and finally drop the intermediate table when no more files are expected;
- remove the unneeded columns from the file with a text editor prior to COPY-ing.
I think that this is happening often in real life and therefore have a suggestion to add this option “[SKIP] COLUMNS <columnslist>”  to the WITH clause of COPY .. FROM. It may be very useful in file fdw too.
To be able to re-arrange columns’ order would come as a free bonus for users.
 
Sincerely,
Stefan Stefanov
 
 

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Consistent state for pg_dump and pg_dumpall
Next
From: Nicolas Paris
Date:
Subject: Re: About COPY command (and probably file fdw too)