pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy from command - quotes and header lines
2+ messages / 2 participants
[nested] [flat]
* copy from command - quotes and header lines
@ 2002-11-24 17:21 Kenneth Evans <unicorn@unicorn.homechoice.co.uk>
2002-11-26 23:52 ` Re: copy from command - quotes and header lines Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Kenneth Evans @ 2002-11-24 17:21 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: copy from command - quotes and header lines
2002-11-24 17:21 copy from command - quotes and header lines Kenneth Evans <unicorn@unicorn.homechoice.co.uk>
@ 2002-11-26 23:52 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Peter Eisentraut @ 2002-11-26 23:52 UTC (permalink / raw)
To: Kenneth Evans <unicorn@unicorn.homechoice.co.uk>; +Cc: pgsql-sql
Kenneth Evans writes:
> 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!
The COPY input data is a separate data format, so you canno expect quoting
and other features of SQL to work. Read the documentation if you're
curious about details.
> 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.
> My current workaround uses a perl pipe but I would prefer a cleaner
> solution.
What's unclean about that?
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-11-26 23:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-24 17:21 copy from command - quotes and header lines Kenneth Evans <unicorn@unicorn.homechoice.co.uk>
2002-11-26 23:52 ` Peter Eisentraut <peter_e@gmx.net>
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