Skip site navigation (1) Skip section navigation (2)

Re: Syntax Error?

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 (view raw or flat)
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";

In response to

Responses

pdxpug by date

Next:From: Michael EwanDate: 2011-02-03 18:56:43
Subject: Re: More Cleanup Guidance Needed
Previous:From: Rich ShepardDate: 2011-02-03 18:42:17
Subject: More Cleanup Guidance Needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group