I have a simple perl script to find out what cause "parse error near .."
The idea is just parse input file and copy to DB using psql line by line.
It's stupid but it helps me several times.
file has following structure ( it's what I prepare for import to DB,
but import failed on some reason):
COPY XXX FROM stdin using delimiters '|';
..............
\.
COPY YYY FROM stdin using delimiters '|';
..............
\.
......
etc
#!/usr/local/bin/perl
# simple wrapper for psql, good to see why copy fails
# tt.pl < file.out | grep ERR
# Oleg Bartunov
my $PSQL = '/usr/local/pgsql/bin/psql -q';
my $DB = 'flats';
my $FOOT = q^\.^;
my $i = 1;
while (<>) { chomp; $COPYLINE = $_; if ( /^COPY .*;$/ ) { # skip HEADOFBUFFER $HEAD = $_;
# # print "HEAD = $HEAD\n"; next; } next if ( $COPYLINE =~ /^\\\./ ); # skip ENDOFBUFFER next if ( $COPYLINE
=~/^--/ ); # skip comment line
# print "$i:$COPYLINE\n"; open(OUT, "| $PSQL $DB"); print OUT "$HEAD\n$COPYLINE\n$FOOT"; close(OUT); print "ERROR!!!
$i:$COPYLINE\n"if( $? ); $i++;
}
You got the idea :-)
Regards, Oleg
On Tue, 25 May 1999, Zot O'Connor wrote:
> Date: Tue, 25 May 1999 10:31:58 -0700
> From: Zot O'Connor <zot@ZotConsulting.com>
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Stupid SQl question on inserting items
>
> This is a dumb basic level SQL question.
>
> is there any other way to insert records without having to fill in all
> of the values as in
>
> INSERT into table_name WITH VALUES (blah, blah, blah...)
>
> or the COPY method?
>
> It is becoming a chore to sync up a table with a lot of fields. I am
> porting several DB into postgres and using a variety of techniques to do
> it (some of these are poorly formatted text files). Most of the fields
> are blank, so when I goof up, I spend 15 minutes comparing files to find
> the problem. The elusive "parse error near '" does not help much :)
>
> I thought I knew how to do this with Postgres but can't seem to find it
> in my notes.
>
> I thought something along the lines of insert a blank (except for id)
> record than updating it with name pairs might work.
>
>
> --
> Zot O'Connor
>
> www.ZotConsulting.com
> www.WhiteKnightHackers.com
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83