Re: Syntax Error?

On 02/03/2011 10:38 AM, Rich Shepard wrote:
> On Thu, 3 Feb 2011, Ewan, Michael wrote:
>
>> The quotes should not be in the data. One of the "features" of COPY is
>> that the input is delimited so you don't need quotes, at least that is my
>> experience. I'll send you a sample script I wrote that creates a COPY
>> script that can be run directly from the command line. Give me a few
>> minutes to get back to my desk.
>
> Ah, so! I've been using SQLite3 the past couple of years embedded in my
> models and columns are flexible in the data they accept.
>
> So, when I export the data from the spreadsheets I should either not
> define a text quote or remove them in emacs before I COPY them into the
> table.
>
> This is highly valuable information!
>
> Rich
>

Here's a sample Perl script for reading rows of data and filtering for
the write format for the COPY. Using the COPY ... from stdin; syntax
greatly simplifies bulk input. You would take the resulting file from
this filter and run it with :

psql --single-transaction -U user -d database -h hostname -f foo.sql

#!/usr/bin/perl

use strict;
my $inp;
my ($col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8);

print STDOUT "COPY table1 (col1, col2, col3, col4, col5, col6, col7,
col8) from stdin;\n";
while ($inp = <>) {
chomp($inp);
($col1, $col2, $col3, $col4, $col5, $col6, $col7, $col8) =
split(/\s+/, $inp);
# the end of record delimiter is new line
print STDOUT
"$col1\t$col2\t$col3\t$col4\t$col5\t$col6\t$col7\t$col8\n";
}
# the EOF delimiter for COPY from stdin is '\.'
print STDOUT "\\.\n";