sql - Postgres 案例陈述错误

Question

我正在尝试创建一个基于职位发布状态的 Postgres case 语句。例如:

• 当 status 为 'Closed' 时，我想要 updated_at 时间戳和 created_at 之间的天数差异> 时间戳。
• 当 status 是 'Open' 或 'Pending' 时，我想减去 created_at 时间戳来自当前时间戳。

我目前创建的查询如下所列:

Select users.first_name || " " || users.last_name,
users.email,
organizations.name,
organizations.vertical,
jobs.name,
jobs.id
(Case
When jobs.status = 'Closed' Then jobs.updated_at - jobs.created_at Else 'Not Closed') END AS days_to_hire,
(Case
When jobs.status IN ('Open,'Pending') Then current_timestamp - jobs.created_at Else 'Closed_or_Deleted') END AS days_open,
FROM organizations
JOIN users on organizations.id = users.organization_id
JOIN jobs on user.id= jobs.user.id

不过，它并没有像我预期的那样工作:

ERROR: syntax error at or near ")"

我一直无法弄清楚原因。

如有任何帮助，我们将不胜感激。

Answer 1

简答

您的查询中存在三个语法问题:

1. 缺少逗号
2. 在您的 CASE 语句中错放了右括号
3. 第二个 CASE 语句中的未终止字符串文字

我在下面记录了每一个:

Select users.first_name || " " || users.last_name,
users.email,
organizations.name,
organizations.vertical,
jobs.name,
-- Missing comma added below.
jobs.id,
--jobs.id
-- Closing paren moved to after `END` below.
(Case
When jobs.status = 'Closed' Then jobs.updated_at - jobs.created_at Else 'Not Closed' END) AS days_to_hire, 
--(Case
--When jobs.status = 'Closed' Then jobs.updated_at - jobs.created_at Else 'Not Closed') END AS days_to_hire,
-- Closing paren moved to after `END` and a closing single quote added
-- to terminate the string literal 'Open' below.
(Case
When jobs.status IN ('Open','Pending') Then current_timestamp - jobs.created_at Else 'Closed_or_Deleted' END) AS days_open,
--(Case
--When jobs.status IN ('Open,'Pending') Then current_timestamp - jobs.created_at Else 'Closed_or_Deleted') END AS days_open,
FROM organizations
JOIN users on organizations.id = users.organization_id
JOIN jobs on user.id= jobs.user.id

附录

另外，您的查询很难按照您设置格式的方式阅读。这使得调试变得更加困难。

考虑一种利用缩进和一致的关键字大小写(即小写与大写)的替代方法:

SELECT
    users.first_name || " " || users.last_name,
    users.email,
    organizations.name,
    organizations.vertical,
    jobs.name,
    jobs.id,
    (CASE
         WHEN jobs.status = 'Closed' THEN jobs.updated_at - jobs.created_at
         ELSE 'Not Closed'
     END) AS days_to_hire, 
    (CASE
         WHEN jobs.status IN ('Open', 'Pending') THEN CURRENT_TIMESTAMP - jobs.created_at
         ELSE 'Closed_or_Deleted'
     END) AS days_open,
FROM organizations
JOIN users ON organizations.id = users.organization_id
JOIN jobs ON user.id= jobs.user.id

我想你会同意的，更容易阅读和调试。

就我个人而言，我不喜欢 CASE 语句周围的括号，但与良好、一致地使用缩进和关键字大小写相比，这是一个次要的选择。