Re: summary and request - Mailing list pgsql-general

I like the COALESCE() function for the "insert into ..." statement below.
See:
http://www.postgresql.org/docs/7.3/interactive/functions-conditional.html#AE
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