Обсуждение: copy from command - quotes and header lines
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
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 sourcefile > 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