Is this doable using Postgresql crosstab or some other function?

From: <mika(at)digikartta(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Is this doable using Postgresql crosstab or some other function?
Date: 2012-04-10 08:52:02
Message-ID: e13779ad97d3938218f2474c5d13dc0e@digikartta.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi folkes,
this is my first message on this list.
I have quite a challenging problem and my own skills seem not to be
adequate for resolving it.
I have a relational model where the basic idea is to store data
vertically instead using traditional horizontal approach. So instead of
having a row in a table with n columns, I have n rows with value columns
for different data types. This makes the model very dynamic but also
difficult to use. I try to describe the (simplified) core of the model
(or at least the parts which have some meaning) in the following pseudo
definition:

Table values
ID serial pk
instanceID integer fk1 (never mind this)
parametertypeID integer fk2
value_integer integer
value_varchar character varying
value_date date
stamp timestamp

Table parameters
parametertypeID serial pk
typeid integer fk1
parameternameID integer fk2

Table parameternames
paramaternameID serial pk
parametername character varying
parameterdatatype integer or like enum(1,2,3) (this defines whether to
use value_integer, value_varchar or value_date)

From these three tables I would like to create a select statement where
the response is the following (where parameters.typeid = x and
values.timestamp = dd.mm.yyyy hh.mm.ss.xx)

instanceID integer
parameternames.parametername#1, value and datatype from value_integer,
value_varchar or value_date
parameternames.parametername#2, value and datatype from value_integer,
value_varchar or value_date
...
parameternames.parametername#n, value and datatype from value_integer,
value_varchar or value_date

Quite challanging, right?

- mika -

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2012-04-10 08:58:46 Re: Is this doable using Postgresql crosstab or some other function?
Previous Message 乔志强 2012-04-10 08:09:17 答复: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?