当列不存在时 Postgres 返回默认值

我有一个查询，如果缺少某个列，我本质上需要一个后备值。我想知道我是否可以纯粹在查询中处理这个问题（而不是先探测并发送单独的查询。本质上我正在寻找相当于COALESCE处理缺失列的情况。

想象一下以下两张表。

T1
id | title | extra
1    A     | value

- and -

T2
id | title
1    A

我希望能够使用相同的查询从这些表中进行选择。

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

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

但只有当列值为 NULL 时才有效，而当列完全丢失时则无效。

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

SQL 纯粹主义者注意：我真的不想争论为什么我想在 SQL 中而不是在应用程序逻辑中执行此操作（或者为什么我不会将列永久添加到架构中），因此请将您的评论/答案限制为具体请求，而不是您对数据库“正确性”的看法或任何其他可能冒犯您的问题。

为什么罗文的黑客攻击 https://stackoverflow.com/a/18951791/939860工作（大部分）？

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

正常情况下，它根本不起作用。 Postgres 解析 SQL 语句并抛出异常，如果any所涉及的列不存在。

诀窍是引入与相关列名同名的表名（或别名）。extra在这种情况下。每个表名都可以作为一个整体引用，这会导致整行作为类型返回record。由于每种类型都可以转换为text，我们可以将整个记录投射到text。这样，Postgres 就会接受有效的查询。

由于列名优先于表名，extra::text被解释为列tbl.extra如果该列存在。否则，它将默认返回表的整行extra- 这永远不会发生。

尝试选择不同的表别名extra亲自看看。

这是一无证黑客攻击，可能会破坏如果 Postgres 决定在未来版本中改变 SQL 字符串的解析和规划方式 - 尽管不太可能。

明确

If你决定至少使用这个使其明确.

表名本身并不唯一。名为“tbl”的表可以在同一数据库的多个模式中存在任意多次，这可能会导致非常混乱且完全错误的结果。你need另外提供模式名称：

SELECT id, title
     , CASE WHEN col_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN (
   SELECT EXISTS (
      SELECT FROM information_schema.columns 
      WHERE  table_schema = 'public'
      AND    table_name = 'tbl'
      AND    column_name = 'extra'
      ) AS col_exists
   ) extra;

Faster

由于这个查询很难移植到其他 RDBMS，我建议使用目录表pg_attribute https://www.postgresql.org/docs/current/catalog-pg-attribute.html而不是信息模式视图information_schema.columns。大约快10倍。

SELECT id, title
     , CASE WHEN col_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN (
   SELECT EXISTS (
      SELECT FROM pg_catalog.pg_attribute
      WHERE  attrelid = 'myschema.tbl'::regclass  -- schema-qualified!
      AND    attname  = 'extra'
      AND    NOT attisdropped    -- no dropped (dead) columns
      AND    attnum   > 0        -- no system columns
      )
   ) extra(col_exists);

还使用更方便、更安全地投射到regclass. See:

• regclass 在 Postgresql 中意味着什么 https://stackoverflow.com/questions/13289107/what-does-regclass-signify-in-postgresql/13290020#13290020

您可以附加所需的别名来欺骗 Postgresany表，包括主表本身。您根本不需要加入另一个关系，这应该是最快的：

SELECT id, title
     , CASE WHEN EXISTS (SELECT FROM pg_catalog.pg_attribute
                         WHERE  attrelid = 'tbl'::regclass
                         AND    attname  = 'extra'
                         AND    NOT attisdropped
                         AND    attnum   > 0)
            THEN extra::text
            ELSE 'default' END AS extra
FROM tbl AS extra;

方便

您可以将存在性测试封装在一个简单的 SQL 函数中（一次），（几乎）到达您一直要求的函数：

CREATE OR REPLACE FUNCTION col_exists(_tbl regclass, _col text)
  RETURNS bool
  LANGUAGE sql STABLE AS
$func$
SELECT EXISTS (
   SELECT FROM pg_catalog.pg_attribute
   WHERE  attrelid = $1
   AND    attname  = $2
   AND    NOT attisdropped
   AND    attnum   > 0
   )
$func$;

COMMENT ON FUNCTION col_exists(regclass, text) IS
'Test for existence of a column. Returns TRUE / FALSE.
$1 .. exact table name (case sensitive!), optionally schema-qualified
$2 .. exact column name (case sensitive!)';

将查询简化为：

SELECT id, title
     , CASE WHEN col_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN col_exists('tbl', 'extra') AS extra(col_exists);

在这里使用具有附加关系的形式，因为事实证明使用该函数更快。

尽管如此，你只能得到文本表示具有任何这些查询的列。获得它并不那么简单实际类型.

基准

我在第 9.1 和 9.2 页上运行了 100k 行的快速基准测试，发现这些是最快的：

Fastest:

SELECT id, title
     , CASE WHEN EXISTS (SELECT FROM pg_catalog.pg_attribute
                         WHERE  attrelid = 'tbl'::regclass
                         AND    attname  = 'extra'
                         AND    NOT attisdropped
                         AND    attnum   > 0)
            THEN extra::text
            ELSE 'default' END AS extra
FROM   tbl AS extra;

第二快：

SELECT id, title
     , CASE WHEN col_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN col_exists('tbl', 'extra') AS extra(col_exists);

db<>fiddle
_{Old sqlfiddle http://www.sqlfiddle.com/#!12/dc1c2/5}