The standard syntax for literal values is = to put=20 single quotes around all the strings and not around the numbers eg <= /DIV>

insert into table values ('London', 12,=20 15.7);

However when bulk loading from files it ne= eds an=20 extra step to find out which fields to put the quotes around. By=20 experimenting, I have found that the INSERT command will allow quotes aroun= d all=20 the fields eg

insert into table values ('London', '12', = '15.7');=20

but not the converse

insert into table values (London, 12,=20 15.7);

On the other hand the COPY FROM file comma= nd has it=20 the other way round - if the separators are semi-colons then the source=20 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 work= s it is=20 certainly convenient as well as surprising, provided one knows what to=20 do!

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

Regards

Kenneth Evans

------=_NextPart_000_0007_01C293DD.E96A2BA0-- From peter_e@gmx.net Tue Nov 26 23:44:42 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2B89D475F81 for ; Tue, 26 Nov 2002 18:44:42 -0500 (EST) Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id B2309475F09 for ; Tue, 26 Nov 2002 18:44:06 -0500 (EST) Received: (qmail 17412 invoked by uid 0); 26 Nov 2002 23:44:08 -0000 Received: from pd902f0b1.dip0.t-ipconnect.de (217.2.240.177) by mail.gmx.net (mp015-rz3) with SMTP; 26 Nov 2002 23:44:08 -0000 Date: Wed, 27 Nov 2002 00:52:26 +0100 (CET) From: Peter Eisentraut X-X-Sender: peter@localhost.localdomain To: Kenneth Evans Cc: pgsql-sql@postgresql.org Subject: Re: copy from command - quotes and header lines In-Reply-To: <000a01c293dd$eb9bb1a0$ab91200a@computer> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/411 X-Sequence-Number: 10553 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