空值、NULL的对比（tdsqlVSPG）

NULL值的对比

PG\mysql中空字符串与null是不同的；而oracle中，空字符串与null等同。

PG :索引可以存null

Oracle 和 PostgreSQL 默认将 NULL 作为最大值，升序时排在最后；MySQL、SQL Server 和 SQLite 默认将 NULL 作为最小值，升序时排在最前。

测试''和NULL关联查询

postgres=# create table t1(id int ,name varchar);
CREATE TABLE
postgres=# insert into t1 values (1,'a');
INSERT 0 1
postgres=# insert into t1 values (2,'');
INSERT 0 1
postgres=# insert into t1(id) values (3);
INSERT 0 1
postgres=#  insert into t1 values (4,NULL);
INSERT 0 1
postgres=# insert into t1 values (9,NULL);
INSERT 0 1


postgres=#  insert into t2  values (5,'b');
INSERT 0 1
postgres=# insert into t2 values (6,'');                 
INSERT 0 1
postgres=# insert into t2  values (7);                   
INSERT 0 1
postgres=# insert into t2  values (8,NULL);  




postgres=# select * from t1;
 id | name 
----+------
  1 | a
  2 | 
  3 | 
  4 | 
  9 | 
(5 rows)

postgres=# select * from t2;
 id2 | name2 
-----+-------
   5 | b
   6 | 
   7 | 
   8 | 
(4 rows)

postgres=# select * from t1 a ,t2 b where a.name=b.name2;
 id | name | id2 | name2 
----+------+-----+-------
  2 |      |   6 | 
(1 row)

只有空值参与了比较，NULL值没有

同理如果一个唯一索引上，有null，也不能约束到他

postgres=# create table t3 (id int ,name varchar);
CREATE TABLE
postgres=# create unique INDEX idx_t3 on t3(name);
CREATE INDEX
postgres=# 
postgres=# 
postgres=# insert into t3 values (11,'c');
INSERT 0 1
postgres=# insert into t3 values (12,''); 
INSERT 0 1
postgres=# insert into t3 values (13,NULL);
INSERT 0 1
postgres=# insert into t3 values (14,NULL);
INSERT 0 1
postgres=# insert into t3 values (14,'');  
ERROR:  duplicate key value violates unique constraint "idx_t3"
DETAIL:  Key (name)=() already exists.
postgres=# select * from t3;
 id | name 
----+------
 11 | c
 12 | 
 13 | 
 14 | 
(4 rows)

TXSQL8.0.24测试

txsql> select * from t3 ;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 |      |
|  3 | NULL |
|  4 | NULL |
|  9 | NULL |
+----+------+
5 rows in set (0.00 sec)

txsql> create unique INDEX idx_t3 on t3(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

txsql> insert into t3 values (10,NULL);
Query OK, 1 row affected (0.00 sec)

txsql> insert into t3 values (11,'');
ERROR 1062 (23000): Duplicate entry '' for key 't3.idx_t3'
txsql> insert into t3 values (11,' ');
ERROR 1062 (23000): Duplicate entry ' ' for key 't3.idx_t3'

'' 和null，是不一样的，索引里面'' 和 ' '是一样的。唯一约束 约束不到NULL，可以约束到''

txsql> create table dy (id int primary key,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

txsql> create unique INDEX idx_dy on dy(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

txsql> insert into dy values (1,'a');
Query OK, 1 row affected (0.00 sec)

txsql> insert into dy values (2,'');
Query OK, 1 row affected (0.00 sec)

txsql> insert into dy values (3,' ');
ERROR 1062 (23000): Duplicate entry ' ' for key 'dy.idx_dy'
txsql> select * from dy where name ='';
+----+------+
| id | name |
+----+------+
|  2 |      |
+----+------+
1 row in set (0.00 sec)

PG-12测试

''和' '可以插入，但是select 是不一样的，唯一约束对NULL没区别

postgres=#  create table dy (id int primary key,name varchar(10));
CREATE TABLE
postgres=# create unique INDEX idx_dy on dy(name);
CREATE INDEX
postgres=# insert into dy values (1,'a');
INSERT 0 1
postgres=# insert into dy values (2,'');
INSERT 0 1
postgres=# insert into dy values (3,' ');
INSERT 0 1
postgres=# select * from dy where name ='';
 id | name 
----+------
  2 | 
(1 row)

postgres=# select * from dy where name =' ';
 id | name 
----+------
  3 |  
(1 row)
postgres=# insert INTO dy values (4,NULL);
INSERT 0 1
postgres=# insert INTO dy values (5,NULL);
INSERT 0 1
postgres=# insert INTO dy values (6,'');
ERROR:  duplicate key value violates unique constraint "idx_dy"
DETAIL:  Key (name)=() already exists.
postgres=# insert INTO dy values (6,' ');
ERROR:  duplicate key value violates unique constraint "idx_dy"
DETAIL:  Key (name)=( ) already exists.
postgres=# select * from dy where name is null;
 id | name 
----+------
  4 | 
  5 | 
(2 rows)