3
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 wish the comeback was:
* 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
* Correction : I need to show time1, time2, time... of days 2016-01-01, 2016-01-02,...
Any idea what I might be doing wrong? Thank you.
The desired return data is not in the table. It has some hidden logic or simply you did not update the table when updating the return?
– Clodoaldo Neto
Had not updated the post. Thank you.
– Mamga