Re: variant column type

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.