<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" text="#000000"
bgcolor="#FFFFFF">
<div class="moz-cite-prefix">I haven't checked more recent versions,
but in 8.2 using <br>
case when new.val is null then 'U' else new.val end <br>
worked a hell of a lot faster then coalesce.<br>
However, just going into the trigger is significant overhead.<br>
<br>
Alban's suggestion of using a rule would work as well. <br>
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.<br>
<br>
Sim<br>
<br>
<br>
On 09/12/2012 09:49 AM, Willy-Bas Loos wrote:<br>
</div>
<blockquote
cite="mid:CAHnozTjpO3Yn9VfcAdZYj6A1tPkAOC26HFnsvL2mosHi6189sw(at)mail(dot)gmail(dot)com"
type="cite">Hi,<br>
<br>
I want to force deafults, and wonder about the performance.<br>
The trigger i use (below) makes the query (also below) take 45%
more time.<br>
The result is the same now, but i do have a use for using the
trigger (see "background info").<br>
<br>
Isn't there a more efficient way to force the defaults (in the
database) when the application layer explicitly inserts a NULL?<br>
<br>
Cheers,<br>
<br>
WBL<br>
<br>
<br>
<backgorund info><br>
I'm building an application where users can upload data to a
database, with a website as a gui.<br>
<br>
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.<br>
That would mean that there are 2 things that mean the same thing:
NULL and 'U' for unknown.<br>
I don't want to bother our customer with the NULL issues in
queries, so i would like to make those fields NOT NULL.<br>
<br>
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.<br>
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.<br>
<br>
When the users leave a blank, php is bound to insert a NULL (or
even an empty string) into the upload table.<br>
I want to use a default, even if php explicitly inserts a NULL.<br>
</backgorund info><br>
<br>
<br>
--the TRIGGER<br>
create or replace function force_defaults () returns trigger as $$<br>
begin<br>
new.val:=coalesce(new.val, 'U');<br>
return new;<br>
end;<br>
$$ language plpgsql;<br>
<br>
--the QUERIES (on my laptop, no postgres config, pg 9.1):<br>
create table accounts (like pgbench_accounts including all);<br>
--(1)<br>
alter table accounts add column val text default 'U';<br>
insert into accounts(aid, bid, abalance, filler) select * from
pgbench_accounts;<br>
INSERT 0 50000000<br>
Time: 538760.542 ms<br>
<br>
--(2)<br>
alter table accounts alter column val set default null;<br>
create trigger bla before insert or update on accounts for each
row ...etc<br>
vacuum accounts;<br>
insert into accounts(aid, bid, abalance, filler) select * from
pgbench_accounts;<br>
INSERT 0 50000000<br>
Time: 780421.041 ms
<div class="yj6qo ajU">
<div id=":1hv" class="ajR" tabindex="0"><img
moz-do-not-send="true" class="ajT"
src="https://mail.google.com/mail/u/0/images/cleardot.gif"></div>
</div>
<br clear="all">
<br>
-- <br>
"Quality comes from focus and clarity of purpose" -- Mark
Shuttleworth<br>
<br>
</blockquote>
<br>
</body>
</html>
