前端之家收集整理的这篇文章主要介绍了
PostgreSQL学习篇9.1 布尔类型,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
boolean的状态要么是true要么是false,如果是unknown,用NULL表示。
boolean在sql中可以用不带引号的TRUE和FALSE表示,也可以用更多的表示真假的带引号的字符表示,如'true','false','yes','no','1','0'等
测试:
test=# create table t (id int,nan boolean,note text);
CREATE TABLE
test=# insert into t values(1,TRUE,'TRUE');
INSERT 0 1
test=# insert into t values(2,FALSE,'FALSE');
INSERT 0 1
test=# insert into t values(3,tRue,'tRue');
INSERT 0 1
test=# insert into t values(4,fAlse,'fAlse');
INSERT 0 1
test=# insert into t values(5,'tRuE','tRuE');
test=# delete from t where id=5;
DELETE 1
test=# insert into t values(5,'''tRuE''');
INSERT 0 1
test=# insert into t values(6,'fAlsE','''fAlsE''');
INSERT 0 1
test=# insert into t values(7,'t','''t''');
INSERT 0 1
test=# insert into t values(8,'f','''f''');
INSERT 0 1
test=# insert into t values(9,'''yes''');
INSERT 0 1
test=# insert into t values(10,'0','''0''');
INSERT 0 1
test=# select * from t;
id | nan | note
----+-----+---------
1 | t | TRUE
2 | f | FALSE
3 | t | tRue
4 | f | fAlse
5 | t | 'tRuE'
6 | f | 'fAlsE'
7 | t | 't'
8 | f | 'f'
9 | t | 'yes'
10 | f | '0'
(10 rows)
test=# insert into t values(11,'',''''''); ---与Oracle不同,Oracle没有Boolean数据类型。。。
ERROR: invalid input Syntax for type boolean: ""
LINE 1: insert into t values(11,'''''');
^
test=# insert into t values(11,null,'null');
INSERT 0 1
test=# select * from t;
id | nan | note
----+-----+---------
1 | t | TRUE
2 | f | FALSE
3 | t | tRue
4 | f | fAlse
5 | t | 'tRuE'
6 | f | 'fAlsE'
7 | t | 't'
8 | f | 'f'
9 | t | 'yes'
10 | f | '0'
11 | | null
(11 rows)
test=# insert into t values(11,NULL,'NULL');
INSERT 0 1
test=# select * from t;
id | nan | note
----+-----+---------
1 | t | TRUE
2 | f | FALSE
3 | t | tRue
4 | f | fAlse
5 | t | 'tRuE'
6 | f | 'fAlsE'
7 | t | 't'
8 | f | 'f'
9 | t | 'yes'
10 | f | '0'
11 | | null ---注意null在表中存的为空值,并非null这个单词,因为,如果这个单词本身是4个字母的单词。
11 | | NULL
(12 rows)
test=# select * from t where nan='t';
id | nan | note
----+-----+--------
1 | t | TRUE
3 | t | tRue
5 | t | 'tRuE'
7 | t | 't'
9 | t | 'yes'
(5 rows)
test=# select * from t where nan; --挺神奇的查询方式
id | nan | note
----+-----+--------
1 | t | TRUE
3 | t | tRue
5 | t | 'tRuE'
7 | t | 't'
9 | t | 'yes'
(5 rows)
test=# select * from t where nan<>'t';
id | nan | note
----+-----+---------
2 | f | FALSE
4 | f | fAlse
6 | f | 'fAlsE'
8 | f | 'f'
10 | f | '0'
(5 rows)
test=# select * from t where not nan;
id | nan | note
----+-----+---------
2 | f | FALSE
4 | f | fAlse
6 | f | 'fAlsE'
8 | f | 'f'
10 | f | '0'
(5 rows)
test=# select * from t where nan is null;
id | nan | note
----+-----+------
11 | | null
11 | | NULL
(2 rows)
test=# select * from t where nan is unknown; ---神奇
id | nan | note
----+-----+------
11 | | null
11 | | NULL
(2 rows)
test=# select * from t where nan is not null;
id | nan | note
----+-----+---------
1 | t | TRUE
2 | f | FALSE
3 | t | tRue
4 | f | fAlse
5 | t | 'tRuE'
6 | f | 'fAlsE'
7 | t | 't'
8 | f | 'f'
9 | t | 'yes'
10 | f | '0'
(10 rows)
布尔类型的操作符:逻辑操作符和比较操作符
逻辑操作符:and,or,not
需要注意的是:false and null结果为false
比较运算符:is
is true
is false
is not true
is not false
is unknown
is not unknown
is null
is not null
原文链接:https://www.f2er.com/postgresql/194205.html