Re: [GENERAL] How can I use the crosstab() function?

[Thom Brown]

On 25 February 2014 23:30, Rob Richardson  wrote:

>  Hello!
>
>
>
> I am trying to use the crosstab() function in PostgreSQL 9.0 under Windows
> 7.  My table has three columns: a timestamp, a tag name and a tag value.  I
> am trying to generate a table that has one column for every distinct value
> in the tag name field.  Each row of the crosstab table will have the
> timestamp, plus values for every tag name that had a value recorded at that
> time.  Of course, many of the fields in each row will be null.
>
>
>
> It seems to me that the crosstab() function will do exactly what I want,
> but I can't get it to work.  If I try:
>
> select * from
>
> crosstab('select recorded_date, tag_name, value from plc_values')
>
> as ct(recorded_date timestamp with time zone, tag_name text, tag_value
> text);
>
> I get "function crosstab(unknown) does not exist".
>
>
>
> According to documentation, I think I need to add the tablefunc extension
> first.  But when I run
>
> create extension tablefunc;
>
> I get a syntax error pointing to the word "extension".
>
>
>
> What haven't I done?
>

Hi Rob,

Extensions weren't introduced until PostgreSQL 9.1, so you'll have to
install it the old-fashioned way.  See the bottom of 9.0's page on contrib
modules for what the method is:

http://www.postgresql.org/docs/9.0/static/contrib.html

-- 
Thom

[GENERAL] How can I use the crosstab() function?

[Rob Richardson]

Hello!

I am trying to use the crosstab() function in PostgreSQL 9.0 under Windows 7.  
My table has three columns: a timestamp, a tag name and a tag value.  I am 
trying to generate a table that has one column for every distinct value in the 
tag name field.  Each row of the crosstab table will have the timestamp, plus 
values for every tag name that had a value recorded at that time.  Of course, 
many of the fields in each row will be null.

It seems to me that the crosstab() function will do exactly what I want, but I 
can't get it to work.  If I try:
select * from
crosstab('select recorded_date, tag_name, value from plc_values')
as ct(recorded_date timestamp with time zone, tag_name text, tag_value text);
I get "function crosstab(unknown) does not exist".

According to documentation, I think I need to add the tablefunc extension 
first.  But when I run
create extension tablefunc;
I get a syntax error pointing to the word "extension".

What haven't I done?

Thank you very much.

RobR