Search Postgresql Archives

Re: dynamic crosstab

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Feb 14, 2008, at 10:56 AM, Balázs Klein wrote:

-----Original Message-----
From: Erik Jones [mailto:erik@xxxxxxxxxx]
Sent: Thursday, February 14, 2008 5:15 PM
To: Balázs Klein
Cc: 'Tino Wildenhain'; 'SunWuKung'; pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  dynamic crosstab


On Feb 14, 2008, at 2:04 AM, Balázs Klein wrote:

Hi,
ye, hundreds of columns - but there is no helping it, that’s the
way many questionnaire are and the representation of the responses
(when not in a database) is always one person per row. I would need
this for exporting, but also to show results online.

Although it’s a good idea I am afraid that an array could only help
me when the info I store about all the persons in the query are
exactly the same (there wouldn’t be empty cells in a crosstab) -
it’s very useful for some cases but in general that sounds like a
dangerous presumption for me.

As of versions >= 8.2 you can store NULL values in arrays.  Perhaps
you could have a Question -> Index table and then use an array per
person for their answers.


I think this is a generic shortcoming of Postgres - whenever you
are forced to create an EAV (Entity-Attribute-Value) model you have
no generic or way of going back to the usual one entity per row
model. This is something that Access has been able to do (up to 255
columns) as far as I can remember. When I google about this topic I
find that the majority of people are still referring to that
solution as the easiest for this purpose. Tablefunc crosstab is so
close to a good solution for this with the syntax where you could
specify the columns with a query - the only shortcoming is that you
still have to enumerate the columns and their datatype. I always
hope that somebody might have something similar but generic - eg.
create those columns automatically and just treat them all as text.

Have a look at http://www.varlena.com/varlena/GeneralBits/110.php for
a totally different approach to questionnaires.

Erik Jones


Hi,
the part that I don't know is how to put those NULLs in.
It could well be doable I just can't do it myself.

How does the query look like that produces from this input:
PersonID AttributeID Value
1	1	aaa
1	2	bbb
1	3	ccc
2	1	ddd
2	3	eee

this output, without manually enumerating the attributeids:
1 (aaa,bbb,ccc)
2 (ddd,NULL,eee)

Thx.
B.

My point was to get rid of the the EAV setup.  Something like:

CREATE TABLE questions (
question_id serial primary key,
question text not null
);

CREATE TABLE people (
person_id serial primary key,
....
);

CREATE TABLE answers (
person_id integer references people,
answers text[]
);

where the indexes into answers are ids from questions. You don't get any easy foreign keys for those indexes into the questions table, which you definitely don't have with the EAV setup anyway, but with this you don't need any kind of pivot/crosstab functionality.

Erik Jones

DBA | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux