NOT LIKE操作符在处理NULL值时的行为

Question

我想获取表中除了serial类型列以外的所有列。我能想到的最接近解决这个问题的查询是:

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

但问题在于它也会将列中值为空的行排除/过滤掉。我不知道为什么Postgres的行为是这样的。所以我不得不把我的查询更改为下面这样：

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

欢迎提出任何更好的建议或原理。

- kushi

---

在大多数/所有数据库系统中，与NULL的比较不使用IS NULL或IS NOT NULL会返回NULL而不是true或false。 - Sam DeHaan

Answer 1

关于null

'anything' NOT LIKE null的结果是null，而不是true。
在WHERE子句中，只有true符合过滤条件。

大多数函数在输入null时返回null（但也有例外）。这是任何正式RDBMS中null的本质。

如果你需要一个单独的表达式，你可以使用：

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

然而，这并没有更短或更快。详情请参阅手册。

正确的查询

您的查询仍然不可靠。在Postgres数据库中，仅表名不足以唯一确定一个表，您需要添加模式名称或依赖于当前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。请参见：Auto increment table column。若要确认，请检查所使用的序列是否被pg_get_serial_sequence(table_name, column_name)“拥有”。

我很少使用信息模式。那些缓慢、臃肿的视图确保在主要版本之间具有可移植性，并且旨在实现对其他符合标准的关系型数据库管理系统的可移植性。但是，许多内容仍然不兼容。Oracle 甚至在 2015 年时都没有实现信息模式。

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

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

更快、更可靠，但不太便携。

手册：

目录项 pg_attrdef 存储列的默认值。有关列的主要信息存储在 pg_attribute 中（请参见下文）。只有明确指定了默认值（在创建表或添加列时）的列才会在此处有条目。

'table1'::regclass 使用 search_path 解析名称以避免歧义。您可以使用模式限定符覆盖：'myschema.table1'::regclass。

相关内容：

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

- Erwin Brandstetter

---

我真的希望他们能够改变标准以使其有意义。NULL表示缺少值，也就是说，当处理NULL值时，LIKE和NOT LIKE应该分别评估为FALSE和TRUE，而不是荒谬的NULL。 - Tom Lint

在SQL中，NULL应该表示“未知”而不是“无值”。因此，它被视为可能是任何值，我们不知道它是否匹配。因此，结果也是NULL。 - Erwin Brandstetter

问题在于，“未知”这种东西并不存在。只有一个值或其缺失，后者使用NULL表示。 “未知”只存在于抽象中。 - Tom Lint

Answer 2

我认为你可以使用：

SELECT column_name *FROM* information_schema.columns
WHERE table_name = 'table1'
AND ( nvl(column_default,0) *NOT LIKE* 'nextval%');

- satish pattar

---

请在您的答案中添加一些解释！ - Mattias Lindberg

nvl/coalesce函数将为column_default字段提供一个值，使其不为空。这似乎很有效，并且比添加额外的where子句参数更容易管理。 - jgaw