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

I understand what you want with regards to skipping columns in input, but
rather than wait to see if that feature is added to a future version of
PostgreSQL, probably the best work around is to

1. CREATE an intermediate table with all columns in the input text file.

2. COPY into the intermediate table.
3. INSERT into your table
SELECT cola, col2, coln from intermediate table.
4. TRUNCATE intermediate table and repeat steps 2 > 4 as needed.

On Thu, May 21, 2015 at 4:33 PM, Nicolas Paris <niparisco(at)gmail(dot)com> wrote:

> 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(dot)sm(at)abv(dot)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 niparisco(at)gmail(dot)com
>> Относно: Re: [GENERAL] About COPY command (and probably file fdw too)
>> До: Stefan Stefanov <stefanov(dot)sm(at)abv(dot)bg>
>> Изпратено на: 20.05.2015 23:21
>>
>>
>> 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.​
>>
>>
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.