检查“空值或null值”的最佳方式

Question

如何在Postgres SQL语句中检查值是否为null或空字符串？

值可能是一个较长的表达式，因此最好只在检查中写一次。

目前我正在使用以下方法：

coalesce( trim(stringexpression),'')=''

但它看起来有点丑陋。

stringexpression 可以是带有尾随空格的 char(n) 列或包含 char(n) 列的表达式。

什么是最好的方式？

- Andrus

---

由于填充（和由此产生的空间浪费）的缘故，使用char几乎总是错误的选择。但除此之外：我认为没有更好的解决方案。 - user330315

为什么丑？逻辑清晰易读。 - klin

@a_horse_with_no_name: 我认为有。 - Erwin Brandstetter

Answer 1

表达式 stringexpression = '' 的结果如下：

true   .. 对于 ''（或者对于任何只包含空格的字符串，数据类型为 char(n)）
null   .. 对于 null
false .. 对于其他任何情况

因此，要检查："stringexpression 是 null 或者为空"：

(stringexpression = '') IS NOT FALSE

或者相反的方法（可能更容易阅读）：

(stringexpression <> '') IS NOT TRUE

适用于任何字符类型，包括char(n)。有关比较运算符的手册。

或者使用不含trim()的原始表达式，这对于char(n)来说是昂贵的噪音（请参见下文），对于其他字符类型来说是不正确的：只包含空格的字符串会被视为空字符串。

coalesce(stringexpression, '') = ''

但是顶部的表达更快。

断言相反的情况更简单："stringexpression既不为空也不为null"：

stringexpression <> ''

关于char(n)的说明

这是关于数据类型char(n)的说明，简称：character(n)。（char / character 是char(1) / character(1)的简称。）在Postgres中不鼓励使用它：

在大多数情况下，应该使用text或character varying。

请不要将char(n)与其他有用的字符类型混淆{{link3：varchar(n)，varchar，text或"char"}}（带双引号）。

在char(n)中，空字符串与仅由空格组成的任何其他字符串没有区别。根据类型定义，所有这些都折叠为n个空格在char(n)中。从逻辑上讲，上述表达式同样适用于char(n) - 就像这些（对其他字符类型无效的情况）一样。

coalesce(stringexpression, '  ') = '  '
coalesce(stringexpression, '') = '       '

演示

将空字符串转换为char(n)时，等同于任何由空格组成的字符串：

SELECT ''::char(5) = ''::char(5)     AS eq1
     , ''::char(5) = '  '::char(5)   AS eq2
     , ''::char(5) = '    '::char(5) AS eq3;

结果：

 eq1 | eq2 | eq3
 ----+-----+----
 t   | t   | t

使用 char(n) 进行“null 或空字符串”测试：

SELECT stringexpression 
     , stringexpression = ''                   AS base_test
     , (stringexpression = '')  IS NOT FALSE   AS test1
     , (stringexpression <> '') IS NOT TRUE    AS test2
     , coalesce(stringexpression, '') = ''     AS coalesce1
     , coalesce(stringexpression, '  ') = '  ' AS coalesce2
     , coalesce(stringexpression, '') = '  '   AS coalesce3
FROM  (
   VALUES
     ('foo'::char(5))
   , ('')
   , ('   ')                -- not different from '' in char(n)
   , (null)
   ) sub(stringexpression);

结果：

stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 ------------------+-----------+-------+-------+-----------+-----------+----------- foo | f | f | f | f | f | f | t | t | t | t | t | t | t | t | t | t | t | t null | null | t | t | t | t | t

使用`text`进行“null或空字符串”的测试。

SELECT stringexpression 
     , stringexpression = ''                   AS base_test
     , (stringexpression = '')  IS NOT FALSE   AS test1
     , (stringexpression <> '') IS NOT TRUE    AS test2
     , coalesce(stringexpression, '') = ''     AS coalesce1
     , coalesce(stringexpression, '  ') = '  ' AS coalesce2
     , coalesce(stringexpression, '') = '  '   AS coalesce3
FROM  (
   VALUES
     ('foo'::text)
   , ('')
   , ('   ')                -- different from '' in sane character types
   , (null)
   ) sub(stringexpression);

结果:

 stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 
------------------+-----------+-------+-------+-----------+-----------+-----------
 foo              | f         | f     | f     | f         | f         | f
                  | t         | t     | t     | t         | f         | f
                  | f         | f     | f     | f         | f         | f
 null             | null      | t     | t     | t         | t         | f

db<>小折 在这里
_{旧的 sqlfiddle}

相关:

使用数据类型"text"来存储字符串有什么不足之处吗？

- Erwin Brandstetter

---

@a_horse_with_no_name：OP询问“检查值是否为null或空字符串的最佳方法”。trim()调用（相对而言）较昂贵 - 而且并不必要。我添加了更多关于char(n)和“空字符串”的内容。 - Erwin Brandstetter

你写道，任何只包含空格的字符串表达式都等于 ''。我能否删除 trim 并使用 coalesce(stringexpression,'')='' 进行检查？相比你的答案，这看起来更易读。 - Andrus

select coalesce(' ', '') = '' 返回的是 false。因此需要使用 TRIM() 函数。 - Andrus

但是 coalesce(' '::char(5), '') = '' 不行。无论如何，我会使用前两个表达式中的一个，它们适用于任何字符类型，而且速度最快、最干净。 - Erwin Brandstetter

使用 (stringexpression = '') IS NOT FALSE 是一个糟糕的建议，因为可读性差。当 stringexpression 为空时，这可能会让读者感到困惑。使用 coalesce 更好。可读性很重要。如果有人误解了你的代码，可能会导致错误。很多代码会被多个人阅读，他们的经验不同，时间跨度也不同。 - godfryd

Answer 2

检查是否为 null 和空值：

coalesce(string, '') = ''

检查是否为null、空或包含空格（修剪字符串）

coalesce(TRIM(string), '') = ''

- user4010677

---

顺便提一下，coalesce(a, ...b) 返回第一个非空值 (https://www.postgresql.org/docs/8.1/functions-conditional.html)。 - bendytree

这个操作可能执行了线性搜索并忽略了索引，所以你应该首先基于索引字段进行过滤。 - undefined

Answer 3

检查字符串的长度也可以起到作用，且非常简洁：

where length(stringexpression) > 0;

- yglodt

---

你检查了这个 NULL 情况吗？ - Flinsch

是的，我做到了。它不会返回空字符串或null字符串字段。 - yglodt

如果您只需要检查空值，则可以尝试使用以下代码：where length(stringexpression) = 0;。这对我很有效。 - Kushan Gunasekera

我喜欢这个解决方案，只是要注意，在某些情况下，计算精确长度可能是比较昂贵的操作。 - noonex

在Postgres中，length(NULL) > 0返回的是NULL，而不是False。 - Automatico

我在Postico的SQL查询界面中进行了测试，但它对于NULL无效。 - therealrodk

Answer 4

很多答案都是最短的方式，但如果列中有很多空值，则不一定是最好的方式。将检查分开可以让优化器更快地评估检查，因为它不必在其他条件上工作。

很多答案都是最短的方式，但如果列中有很多空值，则不一定是最好的方式。将检查分开可以让优化器更快地评估检查，因为它不必在其他条件上工作。

(stringexpression IS NOT NULL AND trim(stringexpression) != '')

由于第一个条件为假，因此无需评估字符串比较。

- John VE

---

谢谢！这个代码有效，并且是明确的代码，可以提高可读性。 - therealrodk

Answer 5

另一种方法是

nullif(trim(stringExpression),'') is not null

- Mowazzem Hosen

Answer 6

如果存在空尾随空格，可能没有更好的解决方案。 COALESCE 仅针对像您的问题这样的问题。

- Świstak35

Answer 7

我看到人们使用的一种方法是stringexpression >''。这可能不是最快的方法，但恰好是其中最短的之一。

我在MS SQL和PostgreSQL上都试过了。

- TarasB

Answer 8

我遇到了一个类似的情况，需要进行此操作。我的表定义如下：

id(bigint)|name (character varying)|results(character varying)
1 | "Peters"| [{"jk1":"jv1"},{"jk1":"jv2"}]
2 | "Russel"| null

要过滤掉结果列中的空值或空白值，有效的方法是：

SELECT * FROM tablename where results NOT IN  ('null','{}'); 

这个查询返回所有结果中非空的行。

我不确定该如何修改这个查询，以便返回相同的所有结果中非空的行。

SELECT * FROM tablename where results is not null; 

--- 嗯，我漏掉了什么，强制类型转换吗？有任何建议吗？

- Alferd Nobel

Answer 9

我喜欢yglodt的回答，但对于大集合和大字符串计算精确长度可能会很昂贵，所以我选择：

coalesce(trim('a') > '','f')

- noonex

Answer 10

我发现这篇文章是在寻找“不要显示空数据或 null”的解决方案。在我的情况下，我们只想显示已填写这些值的用户记录。希望这个回答能帮助到其他人寻找相同的方法。以上回答在我的情况下不起作用。

我们的应用程序在 Rails 和 Postgres 中运行。查看 Rails 如何构建查询以使用 .where.not(company_website: [nil, ''])，它在我们的应用程序中可以正常工作，我可以在控制台中看到生成的 SQL 语句。 WHERE NOT ((contacts.company_website = '' OR contacts.company_website IS NULL)) 我添加了这部分内容，它的效果符合预期。

- Ethan Allen