On Oct 3, 2007, at 1:29 , Stefan Schwarzer wrote:
>>> As others have noted, the query *can* be written. But it appears
>>> to me
>>> that you are struggling against your table layout.
>>
>> The current schema he has is commonly called EAV (entity-attribute-
>> value) and is generally frowned upon. Now, in his particular case
>> it may be justified if the "value" column values are actually all
>> of the same type, such as currency amounts for each category. If
>> this is the case, I suggest renaming the column to be more
>> descriptive of what is actually stored: likewise the id_variable
>> column.
>
> Having 500 statistical global national variables for about 240
> countries/territories. Need to do regional aggregations, per Capita
> calculations and some completeness computations on-the-fly.
> id_variable | year | value | id_country
Both Steve and I have given you alternatives and reasons for choosing
alternative schema. You haven't provided any additional information
to really help us guide you in any particular direction from what we
already have. For example, in the section from me which you quoted
above, I wrote that this schema may be appropriate if the "value"
column values are actually all of the same type (e..g, all currency
amounts, all masses, all counts). You haven't said whether or not
this is the case. We can't read your mind :)
Again, one thing that would help is if you use a more descriptive
column name than "value" that gives an indication of what *kind* of
values are in the column.
> I thought (and did ask) about the possibility to put nevertheless -
> with the new table design - the variables into different tables,
> but nobody really got my on a track for that.
Steve first suggested it and I provided an example of what that would
look like (using "gdp" and "fish_catches" tables) in the same post
you quoted from above.
http://archives.postgresql.org/pgsql-general/2007-10/msg00108.php
Is this not what you mean?
Michael Glaesemann
grzm seespotcode net