Hi,
To me this would be great. Why not the ability to restrict lines too
COPY stafflist (userid, username, staffid)
FROM 'myfile.txt'
WITH (FORMAT text, DELIMITER E'\t', COLUMNS (1, 2, 7),
LINES(2:1000,2000:3000), ENCODING 'windows-1250')
=> subset of full data.
2015-05-21 22:25 GMT+02:00 Stefan Stefanov <stefanov...@abv.bg>:
> Hi,
>
> Maybe I need to clarify a little.
> The suggested option “[SKIP] COLUMNS <columnslist>” would contain
> columns' positions in the file so that only some of the columns in a text
> file would be read into a table.
> Example: copy the first, second and seventh columns form myfile.txt into
> table "stafflist". myfile.txt has many columns.
> COPY stafflist (userid, username, staffid)
> FROM 'myfile.txt'
> WITH (FORMAT text, DELIMITER E'\t', COLUMNS (1, 2, 7), ENCODING
> 'windows-1250')
>
> BR, Stefan
>
>
>
> -------- Оригинално писмо --------
> От: Nicolas Paris nipari...@gmail.com
> Относно: Re: [GENERAL] About COPY command (and probably file fdw too)
> До: Stefan Stefanov <stefanov...@abv.bg>
> Изпратено на: 20.05.2015 23:21
>
>
> 2015-05-20 22:16 GMT+02:00 Stefan Stefanov <stefanov...@abv.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.
>
>
