Yes! Very true. I always forget about that one...
On Sat, 16 Aug 2003 11:43 pm, Murthy Kambhampaty wrote:
> I like the COALESCE() function for the "insert into ..." statement below.
> See:
> http://www.postgresql.org/docs/7.3/interactive/functions-conditional.html#A
>E N9753
>
> Cheers,
> Murthy
>
> -----Original Message-----
> From: Jason Godden
> To: pgsql-general@postgresql.org
> Sent: 8/15/2003 7:33 PM
> Subject: Re: [GENERAL] summary and request
>
> 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