Re: 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(at)postgresql(dot)org)