Re: summary and request - Mailing list pgsql-general

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
>
>