SQL NOT LIKE 与 NULL 值的行为

Behaviour of NOT LIKE with NULL values

提问by kushi

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%'

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:

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

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.

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

回答by Erwin Brandstetter

About NULL

关于 NULL

'anything' NOT LIKE NULLyields NULL, not TRUE.
And only TRUEqualifies for filter expressions in a WHEREclause.

'anything' NOT LIKE NULL产量NULL，不是TRUE。
并且仅TRUE适用于WHERE子句中的过滤器表达式。

Most functions return NULLon NULLinput (there are exceptions). That's the nature of NULLin anyproper RDBMS.

大多数函数NULL在NULL输入时返回（有例外）。这是性质NULL的任何适当的RDBMS。

If you desire a singleexpression, you coulduse:

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

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

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

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

Proper query

正确查询

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_pathto find the first match in it:

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

Related:

有关的：

• How does the search_path influence identifier resolution and the "current schema"

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

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

更好，但仍然不是防弹的。以 'nextval' 开头的列默认值不会生成serial, 。看：

• Auto increment table column

• 自增表列

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

可以肯定的是，检查正在使用的序列是否由带有 的列“拥有” pg_get_serial_sequence(table_name, column_name)。

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

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

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

此外，信息模式中缺少有用的 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) <> ''
   );

Faster and more reliable, but less portable.

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

The manual:

手册：

The catalog pg_attrdefstores 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.

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

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

'table1'::regclass使用search_path来解析名称，从而避免歧义。您可以对要否决的名称进行架构限定：'myschema.table1'::regclass。

Related:

有关的：

• Find the referenced table name using table, field and schema name
• Get the default values of table columns in Postgres?

• 使用表、字段和架构名称查找引用的表名称
• 获取 Postgres 中表列的默认值？

回答by satish pattar

I think you can use :

我认为你可以使用：

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