copy from command - quotes and header lines

From: "Kenneth Evans" <unicorn(at)unicorn(dot)homechoice(dot)co(dot)uk>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: copy from command - quotes and header lines
Date: 2002-11-24 17:21:23
Message-ID: 000a01c293dd$eb9bb1a0$ab91200a@computer
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

a) First an observation about populating tables.
The standard syntax for literal values is to put single quotes around all the strings and not around the numbers eg
insert into table values ('London', 12, 15.7);
However when bulk loading from files it needs an extra step to find out which fields to put the quotes around. By experimenting, I have found that the INSERT command will allow quotes around all the fields eg
insert into table values ('London', '12', '15.7');
but not the converse
insert into table values (London, 12, 15.7);

On the other hand the COPY FROM file command has it the other way round - if the separators are semi-colons then the source file
London;12;15.7
Paris;13;22.2
will work

but
'London'; '12'; '15.7'
'Paris';'13';'22.2'
will not!

Any comments? If this behaviour works it is certainly convenient as well as surprising, provided one knows what to do!

b) My source files for populating the database tables have three header lines. It would be convenient if the COPY FROM command had another parameter eg "HEADERS n" meaning skip first n lines of input file. Assuming I have not missed something in the manual, would it be possible to request an enhancement along these lines? The R statistical package allows one to skip n header lines so this must be a common situation. My current workaround uses a perl pipe but I would prefer a cleaner solution.

Regards
Kenneth Evans

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message javaholic 2002-11-25 12:57:31 How does postgres handle non literal string values
Previous Message Arcadius A. 2002-11-23 22:09:37 SQL query help!