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: [EMAIL PROTECTED] > 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 > > [EMAIL PROTECTED]) > > > ---------------------------(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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
