postgresql - 从 WHERE 子句中的日期中提取年份

Question

我需要在 WHERE 子句中包含 EXTRACT() 函数，如下所示:

SELECT * FROM my_table WHERE EXTRACT(YEAR FROM date) = '2014';

我收到这样的消息:

pg_catalog.date_part(unknown, text) doesn't exist** 
SQL State 42883

这是my_table内容(gid INTEGER, date DATE):

  gid  |    date
-------+-------------
  1    | 2014-12-12
  2    | 2014-12-08
  3    | 2013-17-15

我必须这样做，因为查询是从网站上的表单发送的，该表单包含一个“年份”字段，用户可以在该字段中输入 4 位数字的年份。

Answer 1

问题是您的列是数据类型 text , 而 EXTRACT()仅适用于 date / time types .

您应该将您的列转换为适当的数据类型。

ALTER TABLE my_table ALTER COLUMN date TYPE date;

它更小(文本为 4 个字节而不是 11 个字节)、更快、更清晰(不允许非法日期和大多数拼写错误)。
如果您有非标准格式，请添加带有转换表达式的 USING 子句。示例:

• Alter character field to date

此外，为了在 date 上使用普通索引快速查询，您应该使用 sargable谓词。喜欢:

SELECT * FROM my_table
WHERE    date >= '2014-01-01'
AND      date <  '2015-01-01';

或者，输入 4 位数的年份:

SELECT * FROM my_table
WHERE    date >= to_date('2014', 'YYYY')
AND      date <  to_date('2015', 'YYYY');

你也可以更明确:

to_date('2014' || '0101', 'YYYYMMNDD')

两者都产生相同的日期 '2014-01-01'。

旁白:date 是一个 reserved word在标准 SQL 中和 Postgres 中的基本类型名称。不要将其用作标识符。