How can I correctly handle sparse crosstabs?

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

Responses

Browse pgsql-general by date

  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