Postgress Crosstab - return and sql tuple descriptions are incompatible

Question

Postgress Crosstab - return and sql tuple descriptions are incompatible

Navigation

#1 by (1 votes)

3

I'm trying to use crosstab in postgres but ...

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

* Fix

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;

* Fix

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 return to be:

* Fix

 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

* Correction : I need to show time1, time2, time ... days 2016-01-01, 2016-01-02, ...

Any idea what I might be doing wrong? Thank you.

sql postgresql

asked by anonymous 17.10.2016 / 16:02

1 answer

Check words in Array php Script or command to add all directories and show the total size

score 1 · Accepted Answer

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

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 rather 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