some external sql not working in psql

From: Kashmir <kashmir_us_1999(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: some external sql not working in psql
Date: 2009-04-08 20:08:06
Message-ID: 725888.21701.qm@web31905.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


i'm in need of some psql advise,
believe its rather a trivial issue, but confusing for me...
(and hope this is the correct list for this?)

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,
CONSTRAINT td_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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2009-04-08 20:10:03 Re: No return from trigger function
Previous Message Tom Lane 2009-04-08 20:06:33 Re: No return from trigger function