检查“空值或空值”的最佳方法

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

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

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.

stringexpression可以是char(n)列，也可以是包含char(n)列和尾随空格的表达式。

What is best way?

最好的方法是什么?

6 个解决方案

#1

The expression stringexpression = '' yields:

表达式stringexpression = " yield:

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

真正的. .for "(或任何只包含具有char(n)数据类型的空格的字符串)为零错误. .做别的事情

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

要检查:"stringexpression is NULL或empty"

(stringexpression = '') IS NOT FALSE

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

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

(stringexpression <> '') IS NOT TRUE

Works for any character type including the obsolescent char(n) which is hardly ever useful.
The manual about comparison operators.

适用于任何字符类型，包括过时的char(n)，它几乎没有任何用处。比较操作手册。

Or use the expression you already had, just without the trim() which would be useless for char(n)(see below), or it would include strings consisting of only spaces in the test for other character types:

或者使用您已经拥有的表达式，而没有对char(n)无用的trim()()(参见下面)，或者它将包含测试中仅包含其他字符类型的空格的字符串:

coalesce(stringexpression, '') = ''

But the expressions at the top are faster.

但是上面的表达式更快。

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

断言相反:“stringexpression既不是空的，也不是空的”甚至更简单:

stringexpression <> ''

About char(n)

Do not confuse this data type with other character types like varchar(n), varchar, text or "char" (with quotes), which are all useful data types. This is about the outdated data type with very limited usefulness: char(n), short for: character(n). Also, char and character are short for char(1) / character(1) (same thing).

不要将此数据类型与其他字符类型(如varchar(n)、varchar、文本或“char”(带引号))混淆，这些都是有用的数据类型。这是因为过时的数据类型用处非常有限:char(n)，缩写为:character(n)。而且，char和character是char(1) / character(1)(相同的事情)的缩写。

In char(n) (unlike other string types!) 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 this works for char(n) as well:

在char(n)中(与其他字符串类型不同!)空字符串与其他仅包含空格的字符串没有区别。所有这些在char(n)中按类型定义折叠为n个空格。根据逻辑，这对char(n)也适用:

coalesce(stringexpression, '') = ''

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

当转换为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  

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

用char(n)测试“空字符串”:

SELECT stringexpression 
      ,stringexpression = ''                    AS simple_test
      ,(stringexpression = '')  IS NOT FALSE    AS test1
      ,(stringexpression <> '') IS NOT TRUE     AS test2
      ,coalesce(stringexpression, '') = ''      AS test_coalesce1
      ,coalesce(stringexpression, '  ') = '  '  AS test_coalesce2
      ,coalesce(stringexpression, '') = '  '    AS test_coalesce3
FROM  (
   VALUES
     ('foo'::char(5))
   , ('')
   , (NULL)
   , ('   ')                -- not different from '' in char(n)
   ) sub(stringexpression);

 stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3
------------------+-------------+-------+-------+----------------+----------------+----------------
 foo              | f           | f     | f     | f              | f              | f
                  | t           | t     | t     | t              | t              | t
                  |             | t     | t     | t              | t              | t
                  | t           | t     | t     | t              | t              | t

Test for "null or empty string" with text

用文本测试“空字符串”

SELECT stringexpression 
      ,stringexpression = ''                    AS simple_test
      ,(stringexpression = '')  IS NOT FALSE    AS test1
      ,(stringexpression <> '') IS NOT TRUE     AS test2
      ,coalesce(stringexpression, '') = ''      AS test_coalesce1
      ,coalesce(stringexpression, '  ') = '  '  AS test_coalesce2
      ,coalesce(stringexpression, '') = '  '    AS test_coalesce3
FROM  (
   VALUES
     ('foo'::text)
   , ('')
   , (NULL)
   , ('   ')                -- different from '' in a sane character type like text
   ) sub(stringexpression);

 stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3
------------------+-------------+-------+-------+----------------+----------------+----------------
 foo              | f           | f     | f     | f              | f              | f
                  | t           | t     | t     | t              | f              | f
                  |             | t     | t     | t              | t              | f
                  | f           | f     | f     | f              | f              | f

dbfiddle here
_{Old SQL Fiddle}

dbfiddle是旧的SQL提琴

Related:

相关:

• Any downsides of using data type "text" for storing strings?
• 使用数据类型“文本”来存储字符串有什么缺点吗?

#2

To check for null and empty:

检查空和空:

coalesce(string, '') = ''

To check for null, empty and spaces (trim the string)

检查空、空和空格(修剪字符串)

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

#3

If there may be empty trailing spaces, probably there isn't better solution. COALESCE is just for problems like yours.

如果可能存在空的尾随空格，那么可能没有更好的解决方案。合并只是针对像你这样的问题。

#4

Something that I saw people using is stringexpression > ''. This may be not the fastest one, but happens to be one of the shortest.

我看到人们使用的是stringexpression > "这可能不是最快的，但碰巧是最短的。

Tried it on MS SQL as well as on PostgreSQL.

在MS SQL和PostgreSQL上都尝试过。

#5

My preffered way to compare nullable fields is: NULLIF(nullablefield, :ParameterValue) IS NULL AND NULLIF(:ParameterValue, nullablefield) IS NULL . This is cumbersome but is of universal use while Coalesce is impossible in some cases.

我将可空字段进行比较的方法是:NULLIF(nullablefield，:ParameterValue)为NULL, NULLIF(:ParameterValue, nullablefield)为NULL。这是麻烦的，但是通用的，而合并在某些情况下是不可能的。

The second and inverse use of NULLIF is because "NULLIF(nullablefield, :ParameterValue) IS NULL" will always return "true" if the first parameter is null.

第二个也是对NULLIF的反用是因为“NULLIF(nullablefield，:ParameterValue)是NULL”，如果第一个参数为NULL，则始终返回“true”。

#6

If database having large number of records then null check can take more time you can use null check in different ways like : 1) where columnname is null 2) where not exists() 3) WHERE (case when columnname is null then true end)

如果数据库有大量的记录，那么null检查会花费更多的时间，您可以用不同的方式使用null检查，比如:1)其中columnname为null 2)，而不存在()3)其中(当columnname为null，则为true end)