postgresql 的 is distinct from、is not distinct from
程序员文章站
2022-04-28 19:17:35
...
os: centos 7.4
db: postgresql 11.5
在 postgresql 开发过程中有时会用到 is not distinct from 和 is not distinct from 这个功能
is distinct from
功能描述
A和B的数据类型、值不完全相同返回 true
A和B的数据类型、值完全相同返回 false
将空值视为相同。
postgres=# \x
Expanded display is on.
postgres=# select 1 is distinct from 1,
1 is distinct from 2,
1 is distinct from '1',
'1' is distinct from '1',
1 is distinct from null,
null is distinct from null
;
-[ RECORD 1 ]
?column? | f
?column? | t
?column? | f
?column? | f
?column? | t
?column? | f
is not distinct from
功能描述
A和B的数据类型、值不完全相同返回 false
A和B的数据类型、值完全相同返回 true
将空值视为相同。
postgres=# \x
Expanded display is on.
postgres=# select 1 is not distinct from 1,
1 is not distinct from 2,
1 is not distinct from '1',
'1' is not distinct from '1',
1 is not distinct from null,
null is not distinct from null
;
-[ RECORD 1 ]
?column? | t
?column? | f
?column? | t
?column? | t
?column? | f
?column? | t
第三条看起来有点不太符合规则
postgres=# select 1 is not distinct from '1';
-[ RECORD 1 ]
?column? | t
为 t ,这怎么理解了?
postgres=# select pg_typeof(1),pg_typeof('1');
-[ RECORD 1 ]------
pg_typeof | integer
pg_typeof | unknown
有意思吧,pg_typeof(‘1’) 居然是 unknown, 而我们把它想象成字符串了。
postgres=# select 1 is not distinct from cast('1' as varchar);
ERROR: operator does not exist: integer = character varying
LINE 1: select 1 is not distinct from cast('1' as varchar);
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
postgres=# select 1 is not distinct from cast('1' as int);
?column?
----------
t
(1 row)
postgres=# select cast('1' as varchar) is not distinct from 1;
ERROR: operator does not exist: character varying = integer
LINE 1: select cast('1' as varchar) is not distinct from 1;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
postgres=# select cast('1' as varchar) is not distinct from cast('1' as varchar);
?column?
----------
t
(1 row)
参考:
下一篇: vue.js开发环境搭建教程
推荐阅读
-
记录无法安装mysql-Invalid GPG Key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql的解决办法
-
mysql中错误:1093-You can’t specify target table for update in FROM clause的解决方法
-
INSERT INTO SELECT语句与SELECT INTO FROM语句的一些区别
-
SQL中distinct 和 row_number() over() 的区别及用法
-
详解Python中的from..import绝对导入语句
-
select * from sp_who的解决方案
-
linux下tar命令遇到error:"Error exit delayed from previous errors"的问题及解决
-
sql server的 update from 语句的深究
-
Mac python matplotlib Glyph xxxxx missing from current font的解决方案
-
使用distinct在mysql中查询多条不重复记录值的解决办法