Re: About COPY command (and probably file fdw too)

2015-05-20 22:16 GMT+02:00 Stefan Stefanov <stefanov(dot)sm(at)abv(dot)bg>:

> 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
>
>
>

​Hi,

I guess it already does (from documentation):

COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

Then you can order the column_name as the source file has.​