当列不存在时，Postgres 返回一个默认值 [英] Postgres return a default value when a column doesn't exist

问题描述

我有一个查询，如果缺少某个列，我基本上想要一个回退值.我想知道我是否可以纯粹在我的查询中处理这个问题(而不是先探测并发送一个单独的查询.本质上，我正在寻找一个与 COALESCE 等效的处理缺失列的情况.

I have a query where I essentially want a fallback value if a certain column is missing. I was wondering if I can handle this purely in my query (rather than probing first and sending a seperate query. In essence i'm looking for an equivalent to COALESCE that handles the case of a missing column.

想象以下 2 个表格.

Imagine the following 2 tables.

T1
id | title | extra
1    A     | value

- and -

T2
id | title
1    A

我希望能够使用相同的查询从这些表中的任何一个中进行选择.

I'd like to be able to SELECT from either of these tables WITH THE SAME QUERY.

例如，如果 t2 实际上有一个我可以使用的额外"列

eg, if t2 actually had an 'extra' column I could use

 SELECT id,title, COALESCE(extra, 'default') as extra

但这仅在列值为 NULL 时才有效，当列完全丢失时无效.

But that only works if the column value is NULL, not when the column is missing entirely.

我更喜欢 SQL 版本，但我也可以接受 PLPGSQL 函数(其行为类似于 COALLESCE).

I would prefer an SQL version but I can accept a PLPGSQL function (with a behaviour similiar to COALLESCE) too.

SQL 纯粹主义者注意:我真的不想争论为什么要在 SQL 中而不是在应用程序逻辑中执行此操作(或者为什么我不只是将列永久添加到架构中)所以请将您的评论/回答限制在特定请求上，而不是您对数据库正确性"或其他任何可能冒犯您的问题的看法.

推荐答案

一种方法是查找信息模式表并用它做一点魔法.

One way is to look up the information schema table and do a little magic with it.

类似于:

SELECT id, title, CASE WHEN extra_exists THEN extra ELSE 'default' END AS extra
FROM mytable
CROSS JOIN (
SELECT EXISTS (SELECT 1 
FROM information_schema.columns 
WHERE table_name='mytable' AND column_name='extra') AS extra_exists) extra

需要为要查询的表传入 'mytable' 的位置.

Where 'mytable' needs to be passed in for the table you want to query.

这篇关于当列不存在时，Postgres 返回一个默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！