PostgreSQL如何提取interval字段数值并转换为整数

原有写法的兼容性问题 #

你写的SQL无法在PostgreSQL中正常运行，存在3个语法/逻辑问题：

• PostgreSQL没有内置CHARINDEX函数，该函数是SQL Server等数据库的专有函数，PG中查找子串位置的原生函数为strpos()或标准SQL的position()
• 别名#days包含特殊字符#，在PostgreSQL中如果不给特殊标识符加双引号包裹，会直接触发语法错误
• 逻辑上LEFT(days, CHARINDEX(' ', days))截取的字符串会包含数字后的空格，虽然PostgreSQL做整数转换时会自动忽略首尾空白，但写法不够严谨，遇到不规则空格场景容易出问题

符合PostgreSQL规范的实现方式 #

根据不同的场景需求，可以选择以下两种稳定实现：

1. 固定格式场景最简写法 #

如果字段值严格遵循「数字 + 单个空格 + day/days」的存储格式，用split_part()按空格分割取第一段即可，逻辑最简单、执行效率最高：

SELECT
  CAST(split_part(days, ' ', 1) AS INTEGER) AS days_num
FROM daily_table;

split_part会按指定分隔符切割字符串，直接返回序号对应的分段，这里取第1段就是纯数字内容，不会带多余字符。

2. 存在格式波动的高容错写法 #

如果字段可能存在多空格、单位拼写不一致等不规范情况，可以用正则表达式直接提取开头的连续数字，容错性更强：

SELECT
  CAST((regexp_match(days, '^(\d+)'))[1] AS INTEGER) AS days_num
FROM daily_table;

该写法会从字符串起始位置匹配连续的数字字符，和后面的单位内容完全解耦，只要数字在字符串开头就能正确提取。

额外注意事项 #

• 如果业务必须用#days作为别名，需要给别名加双引号，写成AS "#days"，但日常开发更建议用字母、数字、下划线组合的常规别名，避免不同查询工具的兼容问题
• 如果表中存在不符合格式的脏数据（比如非数字开头、空值），直接转换会触发报错，可以先加格式校验做容错处理：

SELECT
  CASE
    WHEN days ~ '^\d+\s+day(s)?$' THEN CAST(split_part(days, ' ', 1) AS INTEGER)
    ELSE NULL
  END AS days_num
FROM daily_table;

该写法只会转换符合「数字+空格+day/days」格式的记录，不符合的记录返回空值，避免整个查询失败。

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