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

From Brent Wood
Subject Re: About COPY command (and probably file fdw too)
Date
Msg-id 1432241212028.40357@niwa.co.nz
Whole thread Raw
In response to Re: About COPY command (and probably file fdw too)  (Nicolas Paris <niparisco@gmail.com>)
List pgsql-general

You can already do that, natively in Linux/Mac & by adding some simple tools to try & make Windows useful:


cat <FILE> | grep <filter> | psql -d <DB> -c "copy ....;"


between grep, sed, tr, awk you can do almost any in-line filtering or text manipulation you are likely to need. Or a bit of Perl/Python...


Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz
NIWA
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.

From: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> on behalf of Nicolas Paris <niparisco@gmail.com>
Sent: Friday, May 22, 2015 8:33 AM
To: Stefan Stefanov
Cc: Forums postgresql
Subject: Re: [GENERAL] About COPY command (and probably file fdw too)
 

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


2015-05-20 22:16 GMT+02:00 Stefan Stefanov <stefanov.sm@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.​





Attachment

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Allowing postgresql to accept 0xff syntax for data types that it makes sense for?
Next
From: Gilles Darold
Date:
Subject: Re: date with month and year