PostgreSQL中crosstab添加WHERE时间筛选条件的报错问题

表结构及数据 #

首先定义response表并插入数据：

CREATE TABLE response(session_id int, seconds int, question_id int,
  response varchar(500),file bytea);

INSERT INTO response(session_id, seconds, question_id, response, file)
VALUES 
(652,1459721866,31,'0',NULL),(652,1459721866,32,'1',NULL),(652,1459721866,33,'0',NULL),
(652,1459721866,34,'0',NULL),(652,1459721866,35,'0',NULL),(652,1459721866,36,'0',NULL),
(652,1459721866,37,'0',NULL),(652,1459721866,38,'0',NULL),(656,1460845066,31,'0',NULL),
(656,1460845066,32,'0',NULL),(656,1460845066,33,'0',NULL),(656,1460845066,34,'0',NULL),
(656,1460845066,35,'1',NULL),(656,1460845066,36,'0',NULL),(656,1460845066,37,'0',NULL),
(656,1460845066,38,'0',NULL),(657,1463782666,31,'0',NULL),(657,1463782666,32,'0',NULL),
(657,1463782666,33,'0',NULL),(657,1463782666,34,'0',NULL),(657,1463782666,35,'1',NULL),
(657,1463782666,36,'0',NULL),(657,1463782666,37,'0',NULL),(657,1463782666,38,'0',NULL);

注：seconds列存储Unix时间戳。

正常的crosstab查询 #

以下行转列查询可正常返回结果：

SELECT * FROM
    crosstab ('select session_id, question_id, response from response order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

查询结果：

session_id  not_moving foot    bike    motor   car bus metro   train   other
  652           0        1      0        0      0    0   0       0     null
  656           0        0      0        0      1    0   0       0     null
  657           0        0      0        0      1    0   0       0     null

错误尝试分析 #

尝试筛选2016年4月数据时出现两种错误：

错误写法1：在crosstab结果后加WHERE子句 #

SELECT * FROM
    crosstab ('select session_id, question_id, response from response order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500))
WHERE to_timestamp(seconds) BETWEEN '2016-4-1' AND '2016-4-30';

报错信息：

ERROR:  column "seconds" does not exist
LINE 12: WHERE to_timestamp(seconds) BETWEEN '2016-4-1' AND '2016-4-3...

原因：定义的aresult结构中没有seconds列，crosstab返回的结果不包含该字段，因此无法在外部WHERE中引用。

错误写法2：在crosstab的SQL字符串中直接加WHERE子句 #

SELECT * FROM
    crosstab ('select session_id, question_id, response from response WHERE to_timestamp(seconds) BETWEEN '2016-4-1' AND '2016-4-30' order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

报错信息：

ERROR:  syntax error at or near "2016"
LINE 2: ...rom response WHERE to_timestamp(seconds) BETWEEN '2016-4-1' ...
                                                             ^

原因：SQL字符串内部的单引号未转义，导致数据库解析时认为字符串提前结束，出现语法错误。

正确解决方案 #

方案1：转义内部SQL字符串的单引号 #

将内部SQL中的单引号替换为两个连续单引号（PostgreSQL中用于转义字符串内的单引号），同时推荐用半开区间替代BETWEEN，避免遗漏4月最后一秒的数据：

SELECT * FROM
    crosstab ('select session_id, question_id, response from response WHERE to_timestamp(seconds) >= ''2016-04-01'' AND to_timestamp(seconds) < ''2016-05-01'' order by session_id,question_id') AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

方案2：使用美元符($$)包裹内部SQL字符串 #

PostgreSQL支持用美元符作为字符串分隔符，彻底避免单引号转义的麻烦，写法更简洁：

SELECT * FROM
    crosstab ($$select session_id, question_id, response from response WHERE to_timestamp(seconds) >= '2016-04-01' AND to_timestamp(seconds) < '2016-05-01' order by session_id,question_id$$) AS aresult (session_id int,
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

方案3：保留seconds列用于外部筛选 #

如果需要后续对seconds进行其他操作，可以在crosstab的查询中加入该字段（同一个session_id的seconds值一致），并在结果结构中添加对应列，之后在外部WHERE中筛选：

SELECT session_id, seconds, not_moving, foot, bike, motor, car, bus, metro, train, other FROM
    crosstab ($$select session_id, 'seconds'::text, max(seconds)::varchar from response group by session_id
                union all
                select session_id, question_id::text, response from response
                WHERE to_timestamp(seconds) >= '2016-04-01' AND to_timestamp(seconds) < '2016-05-01'
                order by session_id, 2$$) AS aresult (session_id int,
        seconds varchar(500),
        not_moving varchar(500),
        foot varchar(500),
        bike varchar(500),
        motor varchar(500),
        car varchar(500),
        bus varchar(500),
        metro varchar(500),
        train varchar(500),
        other varchar(500));

内容的提问来源于stack exchange，提问作者Amina Umar