i'm in need of some psql advise,
believe its rather a trivial issue, but confusing for me...
facing following issue:
got 2 tables like:
CREATE TABLE td_fetch_by_rrd_id ( f_rrd_id numeric NOT NULL, f_timestamp numeric NOT NULL, f_ds numeric, f_us numeric,
CONSTRAINTtd_fetch_by_rrd_id_pkey PRIMARY KEY (f_rrd_id, f_timestamp)
)
and:
CREATE TABLE td_fetch1m_by_rrd_id ( f_rrd_id numeric NOT NULL, f_timestamp numeric NOT NULL, f_ds numeric, f_us
numeric,CONSTRAINT td_fetch1m_by_rrd_id_pkey PRIMARY KEY (f_rrd_id, f_timestamp)
)
only difference is:
first table stores data per 'f_rrd_id' evey 5min, and the second table every single minute.
I
want to run a query that would return for the same 'f_rrd_id' all
values from both tables sorted by f_timestamp, of course a set would
only have values from the 5m table if the timestamp was present there
too (every 5th set only)
being a sql-lamer, i used some query
builder help to build my query (which served me quite well in the past
for all my 'complicated' sqls), and was suggested for f_rrd_id=444 to
use something as:
SELECT
td_fetch1m_by_rrd_id.f_timestamp,
td_fetch_by_rrd_id.f_ds,
td_fetch_by_rrd_id.f_ds,
td_fetch1m_by_rrd_id.f_ds,
td_fetch1m_by_rrd_id.f_us
FROM td_fetch_by_rrd_id
RIGHT JOIN td_fetch1m_by_rrd_id ON td_fetch_by_rrd_id.f_timestamp=td_fetch1m_by_rrd_id.f_timestamp
WHERE td_fetch1m_by_rrd_id.f_rrd_id=444
ORDER BY td_fetch1m_by_rrd_id.f_timestamp;
and this works quite fine and as expected in the source env (some gui-sqler).
but when i take this into psql, i get totally messed up results, the values just dont make any sense...
assume it is a simple 'shoot the monkey messing with sql' type of an issue, and was hoping real sqlers could help out
quickly?
:)
an example set in the 1m table would look like:
444;20090408135500;15049;3898
444;20090408135600;11760;1023
444;20090408135700;21956;13913
444;20090408135800;14313;3427
444;20090408135900;12876;1007
444;20090408140000;13307;2101
444;20090408140100;25905;5611
and the other table would only have every 5th ts matching,
with minor diffs in the f_us/f_ds columns, e.g. like:
444;20090408135500;15054;3958
444;20090408140000;13322;2131
many tia!
-k