load data
infile '/home/mike/pgsql/t_pr_npi.dat'
into table t_pr_npi
fields terminated by '|' optionally enclosed by '"'
trailing nullcols
(
num_npi,
dte_assign "decode(:dte_as sign,null,'2099 0101',:dte_assi gn)",
dte_end "decode(:dte_en d,null,'2099010 1',:dte_end)",
nam_npi "decode(:nam_np i,null,'*',:nam _npi)",...
pgnovice
Last Activity: Aug 13 '07, 08:10 PM
Joined: Apr 27 '07
Location:
-
Stored functions
This goes along, in some way, with my previous posting about books on functions. Is there something similar to the:
SELECT *
FROM table
EXCEPTION WHEN NO_DATA_FOUND
that can be used in Oracle?
Also can you access "sqlcode" within the function?
Thanks -
Stored functions books
I'm looking for a good manual on stored functions in postgres. Other then using the postgres web site, I haven't really found something I can keep on my desk for a good reference. My current book "PostgresSq l Developer's Handbook" should have stayed in the forest. Thanks for your help. -
Constraints vs Indexes
Indexes are good for "select statement", bad for "inserts, updates, deletes", how does a constraint differ? I've created a constraint on a table as "unique", and later went in a created 6 indexes (not primary or foreign). Would it be better to build these as constraints even though they will only be used in a "select" clause?
thank you -
Temporary tables
Can you build a temporary table with in a sql function?
Ex:
[CODE=sql]
CREATE FUNCTION fnTest(integer) RETURNS bigint AS'
create temporary table mfb (business_name varchar(50) not null);
insert into mfb
select business_prov_n ame from mco61_provider
where npi = $1;
select count(1) from mfb;
'
LANGUAGE SQL;
[/CODE]
I'm getting an err... -
Thanks for the insightLeave a comment:
-
I've attached the table build, and will follow it with some default data.
...Code:1 drop table mco61_provider; 2 3 create table mco61_provider 4 ( 5 business_prov_name varchar(30) not null, 6 business_ind char(1) not null default '*', 7 svr_addr_ln1 varchar(30) not null, 8 svr_addr_ln2 varchar(20) default '*', 9 svr_addr_city varchar(20) not null, 10 svr_addr_state
Leave a comment:
-
psql copy
I've greated my table with "not null default 0000" on several fields. Some of them are char and others numeric. When loading my flat file, and there is no data between the delimiters, shouldn't the default value get loaded? By having a default value defined, and the section on the flat file is comming in null, will the default value get loaded?
Thanks
No activity results to display
Leave a comment: