From: Kenneth Evans <unicorn@unicorn.homechoice.co.uk>
To: pgsql-sql@postgresql.org
Subject: copy from command - quotes and header lines
Date: Sun, 24 Nov 2002 17:21:23 -0000
Message-ID: <000a01c293dd$eb9bb1a0$ab91200a@computer> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: unicorn@unicorn.homechoice.co.uk
Subject: Re: copy from command - quotes and header lines
In-Reply-To: <000a01c293dd$eb9bb1a0$ab91200a@computer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox