[SQL] ENUM vs DOMAIN vs FKyed loookup table
When you need to choose between enum types, domain types or lookup
tables with foreign keys, what do you usualy choose?
Only recently I started using domains, but that seems to be painful, as
Joshua Drake explains here:
http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/
For instance, I have an application that tracks codes. I have table that
looks like this:
CREATE TABLE codes (
code_id integer,
code_value integer,
code_type enum_code_type
)
CREATE TYPE enum_code_type AS ENUM
('Unapproved',
'ApprovedByEmail',
'ApprovedByAdmin');
Now, let's say I have around 5.000.000 codes in my table. If I want to
add new code type, i'm in deep pain. I can't do ALTER TYPE to change
enum definition, I need to do hocus-pocus to achieve what I want.
If I used domain, I can do ALTER DOMAIN DROP CONSTRAINT, ALTER DOMAIN
ADD... Takes a bit of time, but it is more flexible than ENUMs.
Joshua made some benchmarking of the enum/domain types, and it turns out
that domain type is slowest one. I'll be posting some of my benchmarks soon.
I abandoned the lookup tables because of the JOINining.
What are your practices, when do you use ENUMs and when Domains?
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ENUM vs DOMAIN vs FKyed loookup table
On Wednesday 08 April 2009 20:08:55 Mario Splivalo wrote: > What are your practices, when do you use ENUMs and when Domains? When given the choice, pretty much ENUMs. Domains weren't really conceived for this sort of thing in the first place, so it's good to move away from them. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] join help
i'm in need of some psql advise, believe its rather a trivial issue, but confusing for me... facing following issue: got 2 tables like: CREATE TABLE td_fetch_by_rrd_id ( f_rrd_id numeric NOT NULL, f_timestamp numeric NOT NULL, f_ds numeric, f_us numeric, CONSTRAINT td_fetch_by_rrd_id_pkey PRIMARY KEY (f_rrd_id, f_timestamp) ) and: CREATE TABLE td_fetch1m_by_rrd_id ( f_rrd_id numeric NOT NULL, f_timestamp numeric NOT NULL, f_ds numeric, f_us numeric, CONSTRAINT td_fetch1m_by_rrd_id_pkey PRIMARY KEY (f_rrd_id, f_timestamp) ) only difference is: first table stores data per 'f_rrd_id' evey 5min, and the second table every single minute. I want to run a query that would return for the same 'f_rrd_id' all values from both tables sorted by f_timestamp, of course a set would only have values from the 5m table if the timestamp was present there too (every 5th set only) being a sql-lamer, i used some query builder help to build my query (which served me quite well in the past for all my 'complicated' sqls), and was suggested for f_rrd_id=444 to use something as: SELECT td_fetch1m_by_rrd_id.f_timestamp, td_fetch_by_rrd_id.f_ds, td_fetch_by_rrd_id.f_ds, td_fetch1m_by_rrd_id.f_ds, td_fetch1m_by_rrd_id.f_us FROM td_fetch_by_rrd_id RIGHT JOIN td_fetch1m_by_rrd_id ON td_fetch_by_rrd_id.f_timestamp=td_fetch1m_by_rrd_id.f_timestamp WHERE td_fetch1m_by_rrd_id.f_rrd_id=444 ORDER BY td_fetch1m_by_rrd_id.f_timestamp; and this works quite fine and as expected in the source env (some gui-sqler). but when i take this into psql, i get totally messed up results, the values just dont make any sense... assume it is a simple 'shoot the monkey messing with sql' type of an issue, and was hoping real sqlers could help out quickly? :) an example set in the 1m table would look like: 444;20090408135500;15049;3898 444;20090408135600;11760;1023 444;20090408135700;21956;13913 444;20090408135800;14313;3427 444;20090408135900;12876;1007 444;2009040814;13307;2101 444;20090408140100;25905;5611 and the other table would only have every 5th ts matching, with minor diffs in the f_us/f_ds columns, e.g. like: 444;20090408135500;15054;3958 444;2009040814;13322;2131 many tia! -k -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] join help
Kashmir wrote:only difference is: first table stores data per 'f_rrd_id' evey 5min, and the second table every single minute. I want to run a query that would return for the same 'f_rrd_id' all values from both tables sorted by f_timestamp, of course a set would only have values from the 5m table if the timestamp was present there too (every 5th set only) being a sql-lamer, i used some query builder help to build my query (which served me quite well in the past for all my 'complicated' sqls), and was suggested for f_rrd_id=444 to use something as: SELECT td_fetch1m_by_rrd_id.f_timestamp, td_fetch_by_rrd_id.f_ds, td_fetch_by_rrd_id.f_ds, td_fetch1m_by_rrd_id.f_ds, td_fetch1m_by_rrd_id.f_us FROM td_fetch_by_rrd_id RIGHT JOIN td_fetch1m_by_rrd_id ON td_fetch_by_rrd_id.f_timestamp=td_fetch1m_by_rrd_id.f_timestamp WHERE td_fetch1m_by_rrd_id.f_rrd_id=444 ORDER BY td_fetch1m_by_rrd_id.f_timestamp; and this works quite fine and as expected in the source env (some gui-sqler). but when i take this into psql, i get totally messed up results, the values just dont make any sense... The sql is joining on a time stamp??Using the time stamp i would expect odd ball results because a several unique f_rr_id could have the same timestamp especially if its heavy write table . every 5th set only What does this mean what makes something the 5th set. I normally avoid table aliasing but these names i'm having a real tough time reading so we are going to use 1Minute = td_fetch1m_by_rrd_id and the 5Minute = td_fetch_by_rrd_id from here on out. You want to join whats in the 1Minute table to whats in the 5Minute only if it is in the 5Minute table and only return from 1Minute table where the timestamps is in the 5Minute table If my understanding is correct this will work minus any typos. To create a join condition we need a composite identity to join on. So what i did is cast F_rr_id and F_timestamp to text adding them together to create a unique condition to join on. Also there is a typo above noted in bold f_ds is listed twice i believe that is a mistake. SELECT OneM.f_timestamp, FiveM.f_ds, FiveM.f_us, OneM.f_ds, OneM.f_us FROM td_fetch1m_by_rrd_id OneM, left Join (select f_rrd_id, f_ds, f_us, f_timestamp from td_fetch_by_rrd_id ) FiveM ON (OneM.f_rrd_id::text || OneM.f_timestamp::text) = (FiveM.f_rrd_id::text || FiveM.f_timestamp::text) ORDER BY OneM.f_timestamp;
Re: [SQL] join help
thx for the help! this did it then for an individual collection and its fast :-): SELECT OneM.f_timestamp, FiveM.f_ds, FiveM.f_us, OneM.f_ds, OneM.f_us FROM td_fetch1m_by_rrd_id OneM left join (select f_rrd_id, f_ds, f_us, f_timestamp from td_fetch_by_rrd_id where f_rrd_id = 444) FiveM ON (OneM.f_timestamp = FiveM.f_timestamp) where OneM.f_rrd_id = 444 ORDER BY OneM.f_timestamp; From: Justin To: Kashmir Cc: pgsql-sql@postgresql.org Sent: Wednesday, April 8, 2009 9:38:22 PM Subject: Re: [SQL] join help Kashmir wrote:only difference is: first table stores data per 'f_rrd_id' evey 5min, and the second table every single minute. I want to run a query that would return for the same 'f_rrd_id' all values from both tables sorted by f_timestamp, of course a set would only have values from the 5m table if the timestamp was present there too (every 5th set only) being a sql-lamer, i used some query builder help to build my query (which served me quite well in the past for all my 'complicated' sqls), and was suggested for f_rrd_id=444 to use something as: SELECT td_fetch1m_by_rrd_id.f_timestamp, td_fetch_by_rrd_id.f_ds, td_fetch_by_rrd_id.f_ds, td_fetch1m_by_rrd_id.f_ds, td_fetch1m_by_rrd_id.f_us FROM td_fetch_by_rrd_id RIGHT JOIN td_fetch1m_by_rrd_id ON td_fetch_by_rrd_id.f_timestamp=td_fetch1m_by_rrd_id.f_timestamp WHERE td_fetch1m_by_rrd_id.f_rrd_id=444 ORDER BY td_fetch1m_by_rrd_id.f_timestamp; and this works quite fine and as expected in the source env (some gui-sqler). but when i take this into psql, i get totally messed up results, the values just dont make any sense... The sql is joining on a time stamp??Using the time stamp i would expect odd ball results because a several unique f_rr_id could have the same timestamp especially if its heavy write table . every 5th set only What does this mean what makes something the 5th set. I normally avoid table aliasing but these names i'm having a real tough time reading so we are going to use 1Minute = td_fetch1m_by_rrd_id and the 5Minute = td_fetch_by_rrd_id from here on out. You want to join whats in the 1Minute table to whats in the 5Minute only if it is in the 5Minute table and only return from 1Minute table where the timestamps is in the 5Minute table If my understanding is correct this will work minus any typos. To create a join condition we need a composite identity to join on. So what i did is cast F_rr_id and F_timestamp to text adding them together to create a unique condition to join on. Also there is a typo above noted in bold f_ds is listed twice i believe that is a mistake. SELECT OneM.f_timestamp, FiveM.f_ds, FiveM.f_us, OneM.f_ds, OneM.f_us FROM td_fetch1m_by_rrd_id OneM, left Join (select f_rrd_id, f_ds, f_us, f_timestamp from td_fetch_by_rrd_id ) FiveM ON (OneM.f_rrd_id::text || OneM.f_timestamp::text) = (FiveM.f_rrd_id::text || FiveM.f_timestamp::text) ORDER BY OneM.f_timestamp;
