sql - 选择子查询 PostgreSQL

Question

我对 postgres 还是个新手。我想在查询的 SELECT 部分有一个 SELECT 语句，但现在我收到一个错误。

SELECT cu.user_name, cu.created_date, cu.updated_date, cu.email_address,
       cua.attribute_name, cua.attribute_value,
       (select to_char(to_timestamp(cua.attribute_value / 1000), 'yyyy-mm-dd HH24:MI:SS')) AS Issue_Update
FROM cwd_user cu
INNER JOIN cwd_user_attribute cua ON cu.id = cua.user_id
WHERE cu.user_name LIKE 'perter%'

我收到以下错误:

ERROR: operator does not exist: character varying / integer LINE 3: (select to_char(to_timestamp(cua.attribute_value / 1000), '... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

显然 cua.attribute_value 被定义为 varchar。错误消息告诉您不能将字符串除以数字。

您需要将 varchar 转换(转换)为整数。而且您根本不需要 select。

这是您当前设计的解决方法:

SELECT cu.user_name, 
       cu.created_date, 
       cu.updated_date, 
       cu.email_address, 
       cua.attribute_name, 
       cua.attribute_value,
       to_char(to_timestamp(cua.attribute_value::bigint / 1000), 'yyyy-mm-dd HH24:MI:SS') AS Issue_Update
FROM cwd_user cu
  JOIN cwd_user_attribute cua ON cu.id = cua.user_id
WHERE cu.user_name LIKE 'perter%';

::bigint 将字符串转换为整数值。这是 ANSI SQL cast(... as bigint) 运算符的 Postgres 特定语法。参见 the manual了解详情。

但如果 cua.attribute_value 包含不能转换为整数的值(空字符串 '' 已经打破这个)。

正确的解决方案是将数字存储在 integer 列中。不要将数字存储为 varchar

---

attribute_name 和 attribute_value 听起来非常像称为“Entity-Attribute-Value”的(反)模式。

如果您确定具有特定名称的属性的时间戳信息是正确的，您可以这样做以避免转换错误:

       CASE WHEN cua.attribute_name = 'timestamp' THEN
         to_char(to_timestamp(cua.attribute_value::bigint / 1000), 'yyyy-mm-dd HH24:MI:SS') 
       END AS Issue_Update

这将为所有 attribute_name 不是 'timestamp' 的行返回 NULL，并为那些是的行返回格式化的时间戳。但这同样只有在该属性的值是有效数字时才有效(当然，您需要调整与字符串文字 'timestamp' 的比较以使用正确的属性名称)