Thread: force defaults
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
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