pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] Stupid SQl question on inserting items
5+ messages / 5 participants
[nested] [flat]
* Re: [SQL] Stupid SQl question on inserting items
@ 1999-05-25 17:28 Gregory W Burnham <gburnham@sfu.ca>
parent: Zot O'Connor <zot@ZotConsulting.com>
3 siblings, 0 replies; 5+ messages in thread
From: Gregory W Burnham @ 1999-05-25 17:28 UTC (permalink / raw)
To: Zot O'Connor <zot@ZotConsulting.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Stupid SQl question on inserting items
@ 1999-05-25 17:31 Zot O'Connor <zot@ZotConsulting.com>
0 siblings, 4 replies; 5+ messages in thread
From: Zot O'Connor @ 1999-05-25 17:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Stupid SQl question on inserting items
@ 1999-05-25 17:33 Oleg Bartunov <oleg@sai.msu.su>
parent: Zot O'Connor <zot@ZotConsulting.com>
3 siblings, 0 replies; 5+ messages in thread
From: Oleg Bartunov @ 1999-05-25 17:33 UTC (permalink / raw)
To: Zot O'Connor <zot@ZotConsulting.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Stupid SQl question on inserting items
@ 1999-05-25 18:12 Oliver Elphick <olly@lfix.co.uk>
parent: Zot O'Connor <zot@ZotConsulting.com>
3 siblings, 0 replies; 5+ messages in thread
From: Oliver Elphick @ 1999-05-25 18:12 UTC (permalink / raw)
To: Zot O'Connor <zot@ZotConsulting.com>; +Cc: pgsql-sql
"Zot O'Connor" wrote:
>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...)
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 ID 32B8FAA1
========================================
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Stupid SQl question on inserting items
@ 1999-05-25 19:31 D'Arcy J.M. Cain <darcy@druid.net>
parent: Zot O'Connor <zot@ZotConsulting.com>
3 siblings, 0 replies; 5+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-05-25 19:31 UTC (permalink / raw)
To: zot@ZotConsulting.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-05-25 19:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-25 17:31 Stupid SQl question on inserting items Zot O'Connor <zot@ZotConsulting.com>
1999-05-25 17:28 ` Gregory W Burnham <gburnham@sfu.ca>
1999-05-25 17:33 ` Oleg Bartunov <oleg@sai.msu.su>
1999-05-25 18:12 ` Oliver Elphick <olly@lfix.co.uk>
1999-05-25 19:31 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox