Postgress Crosstab – return and sql tuple descriptions are incompatible

Question:

I'm trying to use crosstab in postgres but…

CREATE TABLE tb_testect
(
  datahora timestamp without time zone,
  teste integer
);

* Correction

INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 08:30:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 09:45:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 15:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 18:00:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 20:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 21:00:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 11:30:00',1);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 12:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-01 16:30:00',2);
INSERT INTO tb_testect VALUES (TIMESTAMP '2016-01-02 17:30:00',2);

select * from tb_testect;

* Correction

datahora            | teste
--------------------+-------
2016-01-01 08:30:00 |  1
2016-01-01 09:45:00 |  2
2016-01-02 15:30:00 |  2
2016-01-02 18:00:00 |  1
2016-01-02 20:30:00 |  2
2016-01-02 21:00:00 |  1
2016-01-01 11:30:00 |  1
2016-01-01 12:30:00 |  2
2016-01-01 16:30:00 |  2
2016-01-02 17:30:00 |  2

When I run the following SQL:

SELECT * FROM crosstab('
    select teste, date(datahora), "time"(datahora)
    from tb_testect ORDER  BY 1,2') 
    AS ct ("teste" int, "data" timestamp , "hora1"  timestamp, "hora2" timestamp);

I would like the feedback to be:

* Correction

 teste |    data    |  hora1   |  hora2   |  hora3
-------+------------+----------+----------+---------
     1 | 2016-01-01 | 08:30:00 | 11:30:00 |
     1 | 2016-01-02 | 18:45:00 | 21:00:00 |
     2 | 2016-01-01 | 18:00:00 | 21:00:00 | 16:30:00
     2 | 2016-01-02 | 13:30:00 | 20:30:00 | 17:30:00

But what is returning is the following error:

ERROR:  return and sql tuple descriptions are incompatible

* Fix : I need to show hour1, hour2, hour… of the days 2016-01-01, 2016-01-02,…

Any idea what I might be doing wrong? Thanks.

Answer:

The second column of the query given to the crosstab exists for sorting only and is not returned. In your case, just extract the date from one of the returned timestamp columns:

select teste, hora1::date as data, hora1::time, hora2::time
from
    crosstab ('
        select teste, date(datahora), datahora
        from tb_testect
        order  by 1,2
    ') as ct (teste int, hora1 timestamp, hora2 timestamp )
;
 teste |    data    |  hora1   |  hora2   
-------+------------+----------+----------
     1 | 2016-01-01 | 08:30:00 | 18:00:00
     2 | 2016-01-01 | 09:45:00 | 15:30:00

New

With the review of the question it seems that what you want is not crosstab but grouping:

select
    teste, datahora::date as data,
    min(datahora)::time as hora1, max(datahora)::time as hora2
from tb_testect
group by 1,2
order by 1,2
;
 teste |    data    |  hora1   |  hora2   
-------+------------+----------+----------
     1 | 2016-01-01 | 08:30:00 | 11:30:00
     1 | 2016-01-02 | 18:00:00 | 21:00:00
     2 | 2016-01-01 | 09:45:00 | 12:30:00
     2 | 2016-01-02 | 15:30:00 | 20:30:00
Scroll to Top
AllEscort