Re: Error in crosstab using date_trunc

On Sat, Dec 5, 2009 at 12:11 PM, Jose Maria Terry Jimenez
<jtj(at)tssystems(dot)net> wrote:
> Hello all,
>
> I'm trying to do a crosstab from data that row names are times.
>
> These times are timestamps and  i  want to  use they truncating to minutes
>  this works for me:
>
> select distinct date_trunc('minute',"timestamp") as "timestamp" from
> historico order by "timestamp";
>
> Getting times "normalized" without seconds.
>
> If i do a crosstab using that date_trunc function i get errors. If i do:
>
> select *
> from crosstab
> (
> 'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
> historico order by 1,2'
> )
> as
> (anet timestamp without time zone,
> re1 numeric,
> re2 numeric,
> re3 numeric
> )
> ;

Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:

select *
from crosstab
(
$outer$ select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2 $outer$
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;

And see if that helps.