I haven't checked more recent versions, but in 8.2 using
    case when new.val is null then 'U' else new.val end
worked a hell of a lot faster then coalesce.
However, just going into the trigger is significant overhead.

Alban's suggestion of using a rule would work as well.
If you don't need the value in real time, you can either have a cron job update all the wrong values on a regular basis or using listen/notify you can get it close to real time.

Sim


On 09/12/2012 09:49 AM, Willy-Bas Loos wrote:
Hi,

I want to force deafults, and wonder about the performance.
The trigger i use (below) makes the query (also below) take 45% more time.
The result is the same now, but i do have a use for using the trigger (see "background info").

Isn't there a more efficient way to force the defaults (in the database) when the application layer explicitly inserts a NULL?

Cheers,

WBL


<backgorund info>
 I'm building an application where users can upload data to a database, with a website as a gui.
 
 In the legacy database that we're "pimping", some attributes are optional, but there are also codes that explicitly mark the attribute as 'Unknown' in the same field.
 That would mean that there are 2 things that mean the same thing: NULL and 'U' for unknown.
 I don't want to bother our customer with the NULL issues in queries, so i would like to make those fields NOT NULL.

 The users will use an Excel or CSV form to upload the data and they can just leave a blank for the optional fields if they like.
 We'll use php to insert the data in a table, from which we'll check if the input satisfies our demands before inserting into the actual tables that matter.

 When the users leave a blank, php is bound to insert a NULL (or even an empty string) into the upload table.
 I want to use a default, even if php explicitly inserts a NULL.
</backgorund info>


--the TRIGGER
create or replace function force_defaults () returns trigger as $$
begin
    new.val:=coalesce(new.val, 'U');
return new;
end;
$$ language plpgsql;

--the QUERIES (on my laptop, no postgres config, pg 9.1):
create table accounts (like pgbench_accounts including all);
--(1)
alter table accounts add column val text default 'U';
insert into accounts(aid, bid, abalance, filler) select * from pgbench_accounts;
 INSERT 0 50000000
 Time: 538760.542 ms

--(2)
alter table accounts alter column val set default null;
create trigger bla before insert or update on accounts for each row ...etc
vacuum accounts;
insert into accounts(aid, bid, abalance, filler) select * from pgbench_accounts;
 INSERT 0 50000000
 Time: 780421.041 ms


--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth