带有NULL值的NOT LIKE的行为 [英] 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%')

任何

推荐答案

关于 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 输入（有例外）。这就是 any 适当的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:

相关：

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

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开头的列不会生成序列。请参阅：

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

• 自动增量表列

• Auto increment table column

请确定，所使用的序列是否归 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中表列的默认值？

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

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