pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [SQL] Stupid SQl question on inserting items
5+ messages / 5 participants
[nested] [flat]

* Stupid SQl question on inserting items
@ 1999-05-25 17:31 Zot O'Connor <zot@ZotConsulting.com>
  1999-05-25 17:28 ` Re: [SQL] Stupid SQl question on inserting items Gregory W Burnham <gburnham@sfu.ca>
  1999-05-25 17:33 ` Re: [SQL] Stupid SQl question on inserting items Oleg Bartunov <oleg@sai.msu.su>
  1999-05-25 18:12 ` Re: [SQL] Stupid SQl question on inserting items Oliver Elphick <olly@lfix.co.uk>
  1999-05-25 19:31 ` Re: [SQL] Stupid SQl question on inserting items D'Arcy J.M. Cain <darcy@druid.net>
  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:31 Stupid SQl question on inserting items Zot O'Connor <zot@ZotConsulting.com>
@ 1999-05-25 17:28 ` Gregory W Burnham <gburnham@sfu.ca>
  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

* Re: [SQL] Stupid SQl question on inserting items
  1999-05-25 17:31 Stupid SQl question on inserting items Zot O'Connor <zot@ZotConsulting.com>
@ 1999-05-25 17:33 ` Oleg Bartunov <oleg@sai.msu.su>
  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 17:31 Stupid SQl question on inserting items Zot O'Connor <zot@ZotConsulting.com>
@ 1999-05-25 18:12 ` Oliver Elphick <olly@lfix.co.uk>
  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 17:31 Stupid SQl question on inserting items Zot O'Connor <zot@ZotConsulting.com>
@ 1999-05-25 19:31 ` D'Arcy J.M. Cain <darcy@druid.net>
  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