检查“空值或空值"的最佳方法 [英] Best way to check for "empty or null value"

问题描述

在 Postgres sql 语句中检查值是否为空或空字符串的最佳方法是什么?

值可以是长表达式，所以最好只写一次检查.

目前我正在使用:

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

但是看起来有点丑.

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

最好的方法是什么?

解决方案

表达式 stringexpression = '' 产生:

TRUE .. 对于 ''(或对于 any 字符串，只包含数据类型为 char(n) 的空格)
NULL .. for NULL
FALSE .. 对于其他任何事情

因此要检查:stringexpression 是 NULL 还是空":

(stringexpression = '') 不是假的

或者相反的方法(可能更容易阅读):

(stringexpression <> '') IS NOT TRUE

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

或使用您的原始表达式而不使用trim()，这对于 char(n)(见下文)来说是代价高昂的噪声，或者对于其他字符类型是不正确的:仅由空格组成的字符串将作为空字符串传递.

coalesce(stringexpression, '') = ''

但是顶部的表达式更快.<​​/p>

断言相反更简单:stringexpression 既不是 NULL 也不是空":

stringexpression <>''

关于char(n)

这是关于数据类型char(n)，简称:character(n).(char/character 是 char(1)/character(1) 的缩写.)它的用途是 在 Postgres 中不鼓励:

<块引用>

在大多数情况下，应该使用 text 或 character changed 来代替.

不要将 char(n) 与其他有用的字符类型混淆 varchar(n)、varchar、text 或 char"(带双引号).

在char(n) 中，空字符串 与任何其他仅由空格组成的字符串没有区别.根据类型的定义，所有这些都被折叠到 char(n) 中的 n 个空格.从逻辑上讲，上述表达式也适用于 char(n) - 与这些(不适用于其他字符类型)一样多:

coalesce(stringexpression, ' ') = ' '合并(字符串表达式，'') = ' '

演示

当转换为 char(n) 时，空字符串等于任何空格字符串:

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

结果:

 eq1 |方程2 |方程3----+-----+----吨|吨|吨

测试空或空字符串"；使用 char(n):

SELECT 字符串表达式, stringexpression = '' AS base_test, (stringexpression = '') 与 test1 不同, (stringexpression <> '') 作为 test2 不正确，coalesce(stringexpression, '') = '' AS coalesce1,coalesce(stringexpression, ' ') = ' ' AS coalesce2,coalesce(stringexpression, '') = ' ' AS coalesce3从  (价值观('foo'::char(5)), (''), (' ') -- 与 char(n) 中的 '' 没有区别， (空值))子(字符串表达式)；

结果:

<前>字符串表达式 |base_test |测试1 |测试2 |合并1 |合并2 |聚结3------------------+-----------+-------+-------+-----------+-----------+-----------富|f |f |f |f |f |F|吨|吨|吨|吨|吨|吨|吨|吨|吨|吨|吨|吨空 |空 |吨|吨|吨|吨|吨

测试空或空字符串"；带有 text:

SELECT 字符串表达式, stringexpression = '' AS base_test, (stringexpression = '') 与 test1 不同, (stringexpression <> '') 作为 test2 不正确，coalesce(stringexpression, '') = '' AS coalesce1,coalesce(stringexpression, ' ') = ' ' AS coalesce2,coalesce(stringexpression, '') = ' ' AS coalesce3从  (价值观('foo'::文本), (''), (' ') -- 不同于正常字符类型中的 ''， (空值))子(字符串表达式)；

结果:

<前>字符串表达式 |base_test |测试1 |测试2 |合并1 |合并2 |聚结3------------------+-----------+-------+-------+-----------+-----------+-----------富|f |f |f |f |f |F|吨|吨|吨|吨|f |F|f |f |f |f |f |F空 |空 |吨|吨|吨|吨|F

db<>fiddle 这里
_旧sqlfiddle

相关:

• 使用数据的任何缺点输入文本"用于存储字符串?

What is best way to check if value is null or empty string in Postgres sql statements?

Value can be long expression so it is preferable that it is written only once in check.

Currently I'm using:

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

But it looks a bit ugly.

stringexpression may be char(n) column or expression containing char(n) columns with trailing spaces.

What is best way?

解决方案

The expression stringexpression = '' yields:

TRUE   .. for '' (or for any string consisting of only spaces with the data type char(n))
NULL   .. for NULL
FALSE .. for anything else

So to check for: "stringexpression is either NULL or empty":

(stringexpression = '') IS NOT FALSE

Or the reverse approach (may be easier to read):

(stringexpression <> '') IS NOT TRUE

Works for any character type including char(n). The manual about comparison operators.

Or use your original expression without trim(), which is costly noise for char(n) (see below), or incorrect for other character types: strings consisting of only spaces would pass as empty string.

coalesce(stringexpression, '') = ''

But the expressions at the top are faster.

Asserting the opposite is even simpler: "stringexpression is neither NULL nor empty":

stringexpression <> ''

About char(n)

This is about the data type char(n), short for: character(n). (char / character are short for char(1) / character(1).) Its use is discouraged in Postgres:

In most situations text or character varying should be used instead.

Do not confuse char(n) with other, useful, character types varchar(n), varchar, text or "char" (with double-quotes).

In char(n) an empty string is not different from any other string consisting of only spaces. All of these are folded to n spaces in char(n) per definition of the type. It follows logically that the above expressions work for char(n) as well - just as much as these (which wouldn't work for other character types):

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

Demo

Empty string equals any string of spaces when cast to char(n):

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

Result:

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

Test for "null or empty string" with char(n):

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

Result:

 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

Test for "null or empty string" with text:

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 a sane character types
   , (NULL)
   ) sub(stringexpression);

Result:

 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<>fiddle here
_{Old sqlfiddle}

Related:

• Any downsides of using data type "text" for storing strings?

这篇关于检查“空值或空值"的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！