Thread: Stupid SQl question on inserting items

Stupid SQl question on inserting items

From
"Zot O'Connor"
Date:
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


Re: [SQL] Stupid SQl question on inserting items

From
"Gregory W Burnham"
Date:
> 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



Re: [SQL] Stupid SQl question on inserting items

From
Oleg Bartunov
Date:
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



Re: [SQL] Stupid SQl question on inserting items

From
"D'Arcy" "J.M." Cain
Date:
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.


Re: [SQL] Stupid SQl question on inserting items

From
"Oliver Elphick"
Date:
"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