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

On 25 February 2014 23:30, Rob Richardson <RDRichardson(at)rad-con(dot)com> 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