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

Question

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

想象以下 2 个表。

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 中而不是在应用程序逻辑中执行此操作(或者为什么我不只是将列永久添加到架构中)所以请将您的评论/回答限制在特定请求上，而不是您对数据库“正确性”的意见或任何其他可能冒犯您的问题。

Answer 1

为什么 Rowan's hack工作(大部分)？

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 语句并在任何涉及的列不存在时抛出异常。

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

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

尝试为 extra 选择一个不同的表别名以供您自己查看。

这是一个未记录的 hack，如果 Postgres 决定在未来的版本中改变解析和规划 SQL 字符串的方式，则可能会中断 - 尽管不太可能。

明确

如果您决定使用它，至少使其明确。

单独的表名不是唯一的。名为“tbl”的表可以在同一数据库的多个模式中存在任意多次，这可能会导致非常困惑和完全错误的结果。您需要另外提供架构名称:

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;

更快

由于此查询很难移植到其他 RDBMS，我建议使用 catalog table pg_attribute而不是信息模态视图 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。见:

• What does regclass mean in Postgresql

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

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);

在这里使用带有附加关系的表单，因为事实证明它使用函数会更快。

不过，您只能通过任何这些查询获得列的文本表示。获取实际类型并不那么简单。

基准

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

最快:

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 here
<子>旧sqlfiddle