检查“空值或空值”的最佳方式 [英] Best way to check for "empty or null value"
问题描述
在Postgres sql语句中检查值为null或空字符串的最好方法是什么？
值可以是长表达式，所以最好只写
目前我正在使用：
coalesce （trim（stringexpression），''）=''
但看起来有点丑。 p>
stringexpression 可以是 char（n） char（n）具有尾随空格的列。
什么是最佳方式？
表达式 stringexpression =''产生：
TRUE .. ''（或任何数据类型为 char（n）的空格）
NULL $ c> NULL
FALSE ..用于任何
$ b
为了检查： stringexpression 为空或空
（stringexpression =''）IS NOT FALSE
或者相反的方法（可能更容易阅读）：
<> ''）is not TRUE
适用于任何字符类型，包括过时的 char（n）几乎没有用。
有关比较运算符的手册。
或 使用您已有的表达式，只是没有 trim（）这对于 char（n）（见下文）是无用的，或者它将包括测试中只有空格的字符串对于其他字符类型：
coalesce（stringexpression，''）=''
但是，顶部的表达式更快。</ p>
em>
stringexpression既不是NULL也不是空 更简单：stringexpression<> ''
关于
char（n）
请勿将此数据类型与其他字符类型混淆，例如
varchar（n），varchar，text或char（含引号），这些都是有用的数据类型。这是关于过时的数据类型，其用途非常有限：char（n），简写为：character（n）。此外，char和字符是char（1）c（c）> /字符（1）（与其他字符串类型不同！）空字符串与仅由空格组成的任何其他字符串不同。所有这些都被折叠到char（n）中每个类型定义的 n 它在逻辑上遵循char（n）：coalesce（stringexpression，''）=''
工作其他字符类型）：
coalesce（stringexpression，''）=''
coalesce（stringexpression， '）=''
演示
空字符串在转换为
char（n）时等于任何空格字符串：SELECT'':: char（5）='':: char（5）AS eq1
，'':: char（5）='':: char ，'':: char（5）='':: char（5）AS eq3;
eq1 | eq2 | eq3
---- + ----- + ----
t | t | t
测试null或空字符串 $ c> char（n）：
SELECT stringexpression
，stringexpression = as simple_test
，（stringexpression =''）IS NOT FALSE as test1
，（stringexpression<&'）is not TRUE as test2
，coalesce（stringexpression，''）= 'as AS test_coalesce1
，coalesce（stringexpression，''）='AS test_coalesce2
，coalesce（stringexpression，''）=''AS test_coalesce3
FROM（
VALUES
（'foo':: char（5））
，（''）
，（NULL）
， 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
测试null或空字符串 $ c> text
选择字符串表达式
，stringexpression ='AS 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）
，（''） - 在类似文本的纯字符类型中不同于''
）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 此处
< a href =http://sqlfiddle.com/#!15/d41d8/2066 =nofollow noreferrer> Old SQL Fiddle
相关：
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 the obsolescent char(n) which is hardly ever useful.
The manual about comparison operators.
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:
coalesce(stringexpression, '') = ''
But the expressions at the top are faster.
Asserting the opposite: "stringexpression is neither NULL nor empty" is even simpler:
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).
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:
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):
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):
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
Related:
这篇关于检查“空值或空值”的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
