Re: force defaults - Mailing list pgsql-general
| From | Sim Zacks |
|---|---|
| Subject | Re: force defaults |
| Date | |
| Msg-id | 50504DF5.1070203@compulab.co.il Whole thread Raw |
| In response to | force defaults (Willy-Bas Loos <willybas@gmail.com>) |
| List | pgsql-general |
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
body p { margin-bottom: 0cm; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" text="#000000"
bgcolor="#FFFFFF">
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:
<blockquote
cite="mid:CAHnozTjpO3Yn9VfcAdZYj6A1tPkAOC26HFnsvL2mosHi6189sw@mail.gmail.com"
type="cite">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
<img
moz-do-not-send="true" class="ajT"
src="https://mail.google.com/mail/u/0/images/cleardot.gif">
--
"Quality comes from focus and clarity of purpose" -- Mark
Shuttleworth
pgsql-general by date: