PostgreSQL学习篇9.1 布尔类型

前端之家收集整理的这篇文章主要介绍了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

猜你在找的Postgre SQL相关文章