Re: summary and request - correction - Mailing list pgsql-general

Just a note on that example:

I didn't properly end the case commands on the last two fields in the insert
and end should probably be quoted.  No I haven't tested it.  Should be:

echo "insert into contact select
id,
case fm when null then 'xzxzxzxz' else fm end,
case ls when null then 'xzxzxzxz' else ls end,
case addr when null then '123 xzxzxzxz' else addr end,
case city when null then 'xzxzxzxz' else city end,
case st when null then 'xz' else st end,
case c when null then 'x' else c end,
case start when null then 122038 else start end,
case "end" when null then 122038 else "end" end
from import_contact;" | psql -dthedatabase

Rgds,

Jason

On Sat, 16 Aug 2003 01:10 pm, expect wrote:
> On Sat, 16 Aug 2003 09:33:51 +1000
> Jason Godden <jasongodden@optushome.com.au> wrote:
>
>
> Ahh, thanks for this.  And thanks to all the others that helped me on my
> way. Hopefully I'll be able to give something back to the group.  Although
> that might be hard with all the experts here.
>
> Perhaps I can document this and provide it for public consumption.
>
> > create table import_contact (
> > id character(7) not null primary key,
> > fm character(30),
> > ls character(30),
> > addr character(30),
> > city character(25),
> > st character(2),
> > c character(1),
> > start decimal(6),
> > end decimal(6),
> > ) WITHOUT OIDS;
> >
> > cat datafile.dat | psql -dthedatabase -c "copy import_contact from stdin
> > delimiter ',' null ''";
> >
> > echo "insert into contact select
> > id,
> > case fm when null then 'xzxzxzxz' else fm end,
> > case ls when null then 'xzxzxzxz' else ls end,
> > case addr when null then '123 xzxzxzxz' else addr end,
> > case city when null then 'xzxzxzxz' else city end,
> > case st when null then 'xz' else st end,
> > case c when null then 'x' else c end,
> > case start when null then 122038 else start,
> > case end when null then 122038 else end
> > from import_contact;" | psql -dthedatabase
> >
> > Could be one way although it's not atomic.  Can rewrite the copy command
> > to be a copy from file command to do that and use the \i command (or
> > redirect to psql from file/stdin).  Simple but there are many other
> > methods to get this thing to work.  If you don't want to recreate the
> > defaults everytime then you could have subselects that reference the pg
> > system tables extract the default value for the columns you are looking
> > for.
> >
> > Also could create the insert statements with a script on the outside or
> > replace any blank (null in reality) fields with the default value and
> > copy that straight to the table.
> >
> > On Sat, 16 Aug 2003 03:18 am, expect wrote:
> > > I'd like to summarize what I know (or don't know) since this topic has
> > > been hit around a little and I'm new to this.  I'm hoping it will clear
> > > things up, at least for me.  You are all the experts, I want to make
> > > sure I am singing from the same page.
> > >
> > > data sample:
> > >    id   |  fm    | ls          |     addr     | city    | st | z  |c|
> > > start|end
> > > -----------------------------------------------------------------------
> > >---- -------
> > >
> > > 191922C,Bob Cobb,D'Obbalina Sr.,312 Elm
> > > Street,Yountville,CA,94599,5,062001,082009 339111C,Elma Thelma,Velma,98
> > > Oak Lane,St. Louis,MO,63119-2065,,,
> > >
> > >
> > > What I wanted to do was to import lots of these from a text file.  In
> > > the case where there is an empty string  (i.e. no value after a comma)
> > > I wanted to define the column in the table in a way that would accept
> > > the empty string but replace it with the default value for that column.
> > >  I didn't know that the copy command is just some C code that stuffs
> > > the data into the db ala fois grois.
> > >
> > > What I would really benefit from (and I hope some other new soul would
> > > too) is if someone would outline exactly how they would approach this
> > > problem.
> > >
> > > Maybe provide the correct table definition and the copy command.  Or if
> > > that just won't work an alternate approach.  I realize that some of you
> > > have done this partially but there have been too many replies to get
> > > into a single cohesive instruction.
> > >
> > >
> > > Anyway I suppose my initial frustration in trying to do this may have
> > > blinded me from reason.
> > >
> > >
> > > create table contact (
> > > id character(7) NOT NULL,
> > > fm character(30) DEFAULT 'xzxzxzxz',
> > > ls character(30) DEFAULT 'xzxzxzxz',
> > > addr character(30) DEFAULT '123 xzxzxzxz',
> > > city character(25) DEFAULT 'xzxzxzxz',
> > > st character(2) DEFAULT 'xz',
> > > c character(1) DEFAULT 'x',
> > > start decimal(6) DEFAULT 122038,
> > > end decimal(6) DEFAULT 122038,
> > > CONSTRAINT handle PRIMARY KEY (id)
> > > ) WITHOUT OIDS;
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 2: you can get off all lists
> > > at once with the unregister command (send "unregister
> > > YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly