salah jubeh, 26.07.2011 19:02:
Hello,
suppose the following scenario
the car speed is 240
the car has an airbag
Here the first value is integer and the second value is boolean. Consider that I have this table structure
feature (feature id feature name)
car (car id, .... )
car_feature (car id, feature id, value). the value attribute might have different domains. How can I model this using postgres and using ANSI compliant design ?
Regards
Have a look at the hstore contrib module.
It allows you to store key/value pairs (lots of them) in a single column.
create table car
(
car_id integer,
features hstore
);
insert into car (car_id, features)
values
(1, 'speed => 240, airbag => true');
insert into car (car_id, features)
values
(2, 'speed => 140, airbag => false');
insert into car (car_id, features)
values
(3, 'speed => 140, flux_capacitor => true');
-- show the airbag attribute for all cars
-- will return null for those that don't have that attribute
select car_id, (features -> 'airbag') as airbag_flag
from car;
-- return all rows that have an attribute named flux_capacitor with the value true
select *
from car
where features @> ('flux_capacitor => true')
Note that the only drawback of this solution is that you don't have any datatypes for the attributes and you can't create a foreign key constraint to a "feature" table. But it's probably the most flexible way to deal with such a requirement in Postgres.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
