Thread: 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
> 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...) Yes, you can INSERT into table_name (field_one, field_two, field_five) VALUES (value_one, value_two, value_five); Gregory W Burnham Software Engineer / Systems Administrator Excite Labs Simon Fraser University Vancouver, BC V5A 1S6 604 291 3615 ph 604 291 5679 fx
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
Thus spake Zot O'Connor
> 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...)
Yes. Specify the fields in the insert.
INSERT INTO table_name (field1, field2, field4) VALUES (val1, val2, val4);
This is a good idea in any case even when inserting to all fields in case
something changes in the database schema.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
"Zot O'Connor" wrote: >This is a dumb basic level SQL question. > >is there any other way to insert records without havingto fill in all >of the values as in > >INSERT into table_name WITH VALUES (blah, blah, blah...) INSERT INTO table (column1, column5, column8) VALUES (val1, val5, val8) This lets you insert the values you are interested in and puts nulls (or default values) in the rest. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "And Jesus answering said unto them, They that are whole need not a physician; but they that are sick. I come not to call the righteous, but sinners to repentance." Luke 5:31,32