Hello out there,
There is a line "Allow DISTINCT on views" in the TODO of 6.5.3; I
think my problem below is due to this ... is there a workaround other
than creating a "real" table instead of a view? Is there any
"schedule" for the "view"-problems (just an interested question, by no
means intended to be offensive! I'm quite happy with postgreSQL so
far :-)
Thank you,
Ulf
P.S.: please CC: me in case of an answer, I'm not on the list at the
moment!
----------------------------------------------------------------------
My problem is: I have a table with a date and a time column
combined with a measurement value.
db=> create table test (d date, t time, val float8);
CREATE
[... insert something ...]
Now I want to calculate the mean daily maximal span of the recorded
values for each month. I create a view:
db=> create view span as
db-> select date_part('year', d) as yr,
db-> date_part('month', d) as mon,
db-> max(val)-min(val) as vdiff
db-> from test group by d;
CREATE
Now I try to calculate the average span:
db=> select yr, mon, avg(vdiff)
db-> from span group by yr, mon order by 1, 2;
yr mon avg
----+---+------------------
1997 1 1.62315789473684
1997 1 3.17684210526316
1997 1 2.66842105263158
[...]
There should be only one tupel for each year/month combination
returned. I is also not possible to select e.g. yr "distict":
db=> select distinct yr from span;
yr
----
1997
1997
1997
If there is only a "date_part()"-column in the view, "distinct" works:
db=> create view dpt as
db-> select date_part('month', d) as mon from test;
CREATE
db=> select distinct * from dpt;
mon
---
1
2
3
--
======================================================================
Ulf Mehlig <umehlig@zmt.uni-bremen.de>
Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------
