From: | Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Temporal data storage |
Date: | 2010-04-05 16:28:18 |
Message-ID: | 20100405162818.GA28333@gheift.kawo1.rwth-aachen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have person to group mapping in which I want to store the time, when this
mapping is valid. I can store the valid time in an array or in seperate rows.
in rows:
create table group_mapping (
group name not null,
person integer not null,
valid_since timestamptz not null,
valid_until timestamptz not null
);
insert into group_mapping values('test', 1, '2000-1-1', '2001-1-1');
insert into group_mapping values('test', 1, '2002-1-1', '2002-1-1');
or in an array:
create type period as (
since timestamptz,
"until" timestamptz
);
create table group_mapping_array (
group name not null,
person integer not null,
valid_time period[] not null
);
insert into group_mapping_array values('test', 1,
array[
('2000-1-1', '2001-1-1'),
('2002-1-1', '2003-1-1')
]::period[]
);
some advantages and disadvantages:
with rows:
+ a gist index already exists (must be modified)
+ can add other attributes to the valid time
- prevent overlapping is very complex
- binary operations like "and", "or" and "not" operates on multiple rows
with array:
+ overlapping can simply done with a constraint and a function
+ binary operations like "and", "or" and "not" are easy to implement
- a gist index must be written nearly from scratch
- adding other attributes is complex
Are there other (dis)advantages I have forgotten?
Which solution is better?
Are there any drawbacks by using arrays in the rows?
Thanks,
Gerhard
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2010-04-05 17:34:13 | Re: How to fix set-valued function called in contextthat cannot accept a set in earlier versions |
Previous Message | Tim Landscheidt | 2010-04-05 15:22:40 | Re: Null vs. Empty String in Postgres 8.3.8 |