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

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.

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

Imagine the following 2 tables.

想象一下以下2个表格。

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.

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

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

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

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

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

但是只有在列值为NULL时才有效,而不是在列完全丢失时。

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

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

NOTE to SQL purists: I don't really feel like debating why I want to do this in SQL and not in application logic (or why I won't just add the column permanently to the schema) so please restrict your comments/answers to the specific request and not your opinion on database 'correctness' or whatever else might offend you about this question.

对SQL纯粹主义者的注意:我真的不想辩论为什么我想在SQL中而不是在应用程序逻辑中这样做(或者为什么我不会只是将列永久地添加到模式中)所以请限制你的评论/答案具体请求,而不是您对数据库“正确性”的看法,或者其他任何可能会冒犯您的问题。

2 个解决方案

#1

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

一种方法是查找信息模式表并使用它做一些魔术。

Something like:

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

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

编辑:需要为要查询的表传递'mytable'。

#2

Why does Rowan's hack work (mostly)?

SELECT id, title, CASE WHEN extra_exists THEN extra::text
                                         ELSE 'default'::text 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
   ) AS extra

Normally, it would not work at all. Postgres parses the SQL statement and throws an exception if any of the involved columns does not exist.

通常,它根本不起作用。如果任何涉及的列不存在,Postgres会解析SQL语句并抛出异常。

The trick is to introduce a table name (or alias) with the same name as the column name in question. extra in this case. Every table name can be referenced as a whole, which results in the whole row being returned as type record. And since every type can be cast to text, we can cast this whole record to text. This way, Postgres accepts the query as valid.

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

Since column names take precedence over table names, extra::text is interpreted to be the column mytable.extra if the column exists. Otherwise, it would default to returning the whole row of the table extra - which never happens.

由于列名优先于表名,因此如果列存在,则extra :: text将被解释为mytable.extra列。否则,它将默认返回表的整行 - 这从未发生过。

Try to pick a different table alias for extra to see for yourself.

尝试选择一个不同的表别名,以便额外查看。

This is an undocumented hack and might break if Postgres decides to change the way SQL strings are parsed abd planned in future versions - even though this seems unlikely.

如果Postgres决定改变未来版本中计划的SQL字符串解析方式,那么这是一个未记录的黑客攻击并且可能会破坏 - 尽管这似乎不太可能。

Unambiguous

If you decide to use this, at least make it unambiguous.

如果你决定使用它,至少要明确它。

A table name alone is not unique. A table named "mytable" can exist any number of times in multiple schemas of the same database, which could lead to very confusing and completely false results. You need to supply the schema name additionally:

仅表名不是唯一的。名为“mytable”的表可以在同一数据库的多个模式中存在任意次数,这可能导致非常混乱且完全错误的结果。您还需要提供架构名称:

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

Faster

Since this query is hardly portable to other RDBMS, I suggest to use the catalog table pg_attribute instead of the information schema view information_schema.columns. About 10 times faster.

由于此查询几乎不可移植到其他RDBMS,我建议使用目录表pg_attribute而不是信息架构视图information_schema.columns。大约快10倍。

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

Also using the more convenient and secure cast to regclass - explained in detail here:
What does regclass signify in Postgresql

还使用更方便和安全的强制转换来进行注册 - 这里详细解释:regclass在Postgresql中表示什么

You can attach the needed alias to fool Postgres to any table, including the primary table itself. You don't need to join to another relation at all, which should be fastest:

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

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

Convenience

You could encapsulate the test for existence in a simple SQL function (once), arriving (almost) at the function you have been asking for:

您可以在一个简单的SQL函数(一次)中封装测试存在,到达(几乎)到您一直要求的函数:

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

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

Simplifies the query to:

将查询简化为:

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

Using the form with additional relation here, since it turned out to be faster with the function.

在这里使用具有附加关系的表单,因为事实证明该功能更快。

Still, you only get the text representation of the column with any of these queries. It's not as simple to get the actual type.

但是,您只能使用任何这些查询获取列的文本表示。获得实际类型并不简单。

Benchmark

I ran a quick benchmark with 100k rows on pg 9.1 and 9.2 to find these to be fastest:

我在pg 9.1和9.2上运行了一个带有10万行的快速基准测试,以发现这些最快:

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

-- 2nd fastest
SELECT id, title, CASE WHEN col_exists THEN extra::text
                                       ELSE 'default'::text END AS extra
FROM   mytable
CROSS  JOIN col_exists('mytable', 'extra') AS extra(col_exists);

-> SQLfiddle demo.

- > SQLfiddle演示。