From: | Rob Richardson <RDRichardson(at)rad-con(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | How can I correctly handle sparse crosstabs? |
Date: | 2014-02-26 15:56:32 |
Message-ID: | 67D108EDFAD3C148A593E6ED7DCB4BBDD840E817@RADCONWIN2K8PDC.radcon.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
It seems that crosstab queries assume there is going to be a value for every possible row in the source query. That's not what I have, and I am not getting the result I need.
Here is the definition of my source table:
CREATE TABLE plc_values
(
plc_values_key bigserial NOT NULL,
recorded_date timestamp with time zone,
tag_name character varying(120),
tag_value character varying(120),
CONSTRAINT plc_values_pk PRIMARY KEY (plc_values_key )
)
There are 30 possible tag_name values. But for a given recorded_date, there may only be one tag, and the most that I see is five tags. When I run the crosstag query, the data columns are filled in from left to right, regardless of the name of the column.
Here is the query:
select * from
crosstab($$ select recorded_date, tag_name, tag_value from plc_values$$ ||
$$ order by recorded_date, tag_name $$ )
as ct(
read_time timestamp with time zone,
Crane2_Crane_Loaded character varying(120),
Crane2_Crane_LoadWeight character varying(120),
<28 more fields snipped>
)
I always get a value in the Crane2_Crane_Loaded field. It may not have come from that tag, though. Sometimes the value is "True" or "False", or maybe 1 or 0, or maybe even 102598.
If crane2_crane_loadweight has a value, it is guaranteed that crane2_crane_loaded has a value. In general, if a column has a value, then the column to its left has a value.
There is no relation between the value in the column and the tag name the value was associated with in the source table. How can I enforce that relationship in a crosstab? Or am I plain out of luck?
Thanks very much!
RobR
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2014-02-26 16:15:01 | Cancelling of autovacuums considered harmful |
Previous Message | Leonardo M. Ramé | 2014-02-26 15:14:25 | Re: Determine Client Encoding |