如何在PostgreSQL（psql）中设置日期类型变量？

1. to_date函数基础用法 #

to_date函数可将字符串转换为日期类型：

select  to_date('2024-01-01','YYYY-MM-DD');
  to_date   
------------
 2024-01-01
(1 row)

验证转换后的数据类型：

select  pg_typeof(to_date('2024-01-01','YYYY-MM-DD'));
 pg_typeof 
-----------
 date

2. 遇到的问题 #

尝试用\set定义日期变量后查询，直接报错：

\set mydate to_date('2024-01-01','YYYY-MM-DD')
select :mydate;
ERROR:  column "yyyy" does not exist
LINE 1: select to_date(2024-01-01,YYYY-MM-DD);
                                  ^
Time: 0.212 ms

3. 问题根源 #

psql的\set是纯文本替换工具，并非执行SQL表达式的命令。当你定义mydate变量时，它只是把to_date('2024-01-01','YYYY-MM-DD')存成字符串；替换到select :mydate语句里时，单引号会被剥离，导致'2024-01-01'被解析成数值计算2024-01-01，'YYYY-MM-DD'被当成列名YYYY-MM-DD，最终触发“列不存在”的错误。

4. 解决办法 #

办法一：转义单引号，包裹表达式 #

定义变量时用单引号把整个表达式包起来，内部的单引号用双写转义，查询时正常调用：

\set mydate 'to_date(''2024-01-01'',''YYYY-MM-DD'')'
select :mydate;

办法二：直接用日期字面量 #

如果是固定日期，没必要用to_date，直接用PostgreSQL支持的日期格式，加上类型转换：

\set mydate '2024-01-01'::date
select :mydate;

或者分开写类型转换：

\set mydate '2024-01-01'
select :mydate::date;

办法三：用psql的表达式赋值（12+版本） #

PostgreSQL 12及以上版本，\set支持用:语法执行表达式并赋值：

\set mydate :'to_date(''2024-01-01'',''YYYY-MM-DD'')'
select :mydate;

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