NOT LIKE 与 NULL 值的行为 [英] Behaviour of NOT LIKE with NULL values

问题描述

我想获取表的所有列，但串行类型的列除外.我能够提出这个问题的最接近这个问题的查询:

I want to fetch all columns of a table except of columns of type serial. The closest query to this problem I was able to come up with this one:

SELECT column_name FROM information_schema.columns
WHERE table_name = 'table1' AND column_default NOT LIKE 'nextval%'

但问题是它也排除/过滤了 column_default 具有空值的行.我不知道为什么 Postgres 的行为是这样的.所以我不得不将我的查询更改为这样的:

But the problem is its also excluding/filtering rows having empty values for column_default.I don't know why the behaviour of Postgres is like this. So I had to change my query to something like this:

SELECT column_name FROM information_schema.columns
WHERE table_name = 'table1'
AND ( column_default IS NULL OR column_default NOT LIKE 'nextval%')

欢迎任何更好的建议或背后的理由.

Any better suggestions or rationale behind this are welcome.

推荐答案

关于NULL

'anything' NOT LIKE NULL 产生 NULL，而不是 TRUE.
并且只有 TRUE 有资格用于 WHERE 子句中的过滤器表达式.

About NULL

'anything' NOT LIKE NULL yields NULL, not TRUE.
And only TRUE qualifies for filter expressions in a WHERE clause.

大多数函数在 NULL 输入时返回 NULL(也有例外).这就是任何适当的 RDBMS 中 NULL 的本质.

Most functions return NULL on NULL input (there are exceptions). That's the nature of NULL in any proper RDBMS.

如果你想要一个单个表达式，你可以使用:

If you desire a single expression, you could use:

AND   (column_default LIKE 'nextval%')  IS NOT TRUE;

不过，这几乎不会更短或更快.手册中的详细信息.

That's hardly shorter or faster, though. Details in the manual.

您的查询仍然不可靠.Postgres 数据库中单独的表名不是唯一的，您需要另外指定模式名或依赖当前的search_path 来查找其中的第一个匹配项:

Your query is still unreliable. A table name alone is not unique in a Postgres database, you need to specify the schema name in addition or rely on the current search_path to find the first match in it:

相关:

• 如何search_path 影响标识符解析和当前模式"

SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'hstore1'
AND    table_schema = 'public'   -- your schema
AND   (column_default IS NULL OR
       column_default NOT LIKE 'nextval%');

更好，但仍然不是防弹的.以 'nextval' 开头的默认列不会生成 serial.见:

Better, but still not bullet-proof. A column default starting with 'nextval' does not make a serial, yet. See:

• 自增表列

可以肯定的是，检查使用的序列是否由 pg_get_serial_sequence(table_name, column_name).

To be sure, check whether the sequence in use is "owned" by the column with pg_get_serial_sequence(table_name, column_name).

我自己很少使用信息模式.那些缓慢、臃肿的视图保证了跨主要版本的可移植性 - 并旨在可移植到其他符合标准的 RDBMS.但无论如何，太多是不相容的.Oracle 甚至没有实现信息模式(截至 2015 年).

I rarely use the information schema myself. Those slow, bloated views guarantee portability across major versions - and aim at portability to other standard-compliant RDBMS. But too much is incompatible anyway. Oracle does not even implement the information schema (as of 2015).

此外，信息模式中缺少有用的 Postgres 特定的列.对于这种情况，我可能会像这样查询系统目录:

Also, useful Postgres-specific columns are missing in the information schema. For this case I might query the the system catalogs like this:

SELECT *
FROM   pg_catalog.pg_attribute a
WHERE  attrelid = 'table1'::regclass
AND    NOT attisdropped   -- no dropped (dead) columns
AND    attnum > 0         -- no system columns
AND   NOT EXISTS (
   SELECT FROM pg_catalog.pg_attrdef d
   WHERE  (d.adrelid, d.adnum) = (a.attrelid, a.attnum)
   AND    d.adsrc LIKE 'nextval%'
   AND    pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) <> ''
   );

更快、更可靠，但便携性较差.

Faster and more reliable, but less portable.

手册:

目录pg_attrdef 存储列默认值.主要的有关列的信息存储在 pg_attribute 中(见下文).仅有的显式指定默认值的列(当表是创建或添加列)将在此处有一个条目.

The catalog pg_attrdef stores column default values. The main information about columns is stored in pg_attribute (see below). Only columns that explicitly specify a default value (when the table is created or the column is added) will have an entry here.

'table1'::regclass 使用 search_path 来解析名称，避免歧义.您可以对要否决的名称进行模式限定:'myschema.table1'::regclass.

'table1'::regclass uses the search_path to resolve the name, which avoids ambiguity. You can schema-qualify the name to overrule: 'myschema.table1'::regclass.

相关:

• 找到引用的使用表、字段和模式名称的表名
• 获取表列的默认值在 Postgres 中?

这篇关于NOT LIKE 与 NULL 值的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！