From: | Michael Ewan <michael(dot)ewan(at)intel(dot)com> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
Cc: | "pdxpug(at)postgresql(dot)org" <pdxpug(at)postgresql(dot)org> |
Subject: | Re: Syntax Error? |
Date: | 2011-02-03 18:52:58 |
Message-ID: | 4D4AF98A.6060509@intel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pdxpug |
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";
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Ewan | 2011-02-03 18:56:43 | Re: More Cleanup Guidance Needed |
Previous Message | Rich Shepard | 2011-02-03 18:42:17 | More Cleanup Guidance Needed |