postgresql中网络地址类型和布尔类型

创建测试用户,表空间,以及数据库,赋予权限

postgres=# create role pguser with encrypted password 'pguser'; CREATE ROLE postgres=# create tablespace tbs_mydb owner pguser location '/data/10/tbs_mydb'; CREATE TABLESPACE                                                            ^ postgres=# create database mydb with owner=pguser template=template0 encoding='UTF8' tablespace=tbs_mydb; CREATE DATABASE postgres=# grant all on database mydb to pguser with grant option; GRANT postgres=# grant all on tablespace tbs_mydb to pguser; GRANT postgres=# alter role pguser login; ALTER ROLE 

1.布尔类型

字符类型名称 存储长度 描述
boolean 1byte 值为TRUE或者FALSE,0,1,yes,no,t,f,y,n

mydb=> create table test_bool(a boolean,b boolean); CREATE TABLE  mydb=> insert into test_bool values('true','false'); INSERT 0 1 mydb=> select * from test_bool;  a | b  ---+---  t | f (1 row)  mydb=> insert into test_bool values('y','n'); INSERT 0 1 mydb=> select * from test_bool;  a | b  ---+---  t | f  t | f (2 rows)  mydb=> insert into test_bool values('t','f'); INSERT 0 1 mydb=> select * from test_bool;  a | b  ---+---  t | f  t | f  t | f (3 rows)  mydb=> insert into test_bool values('1','0'); INSERT 0 1 mydb=> select * from test_bool;  a | b  ---+---  t | f  t | f  t | f  t | f (4 rows)   HINT:  You will need to rewrite or cast the expression. mydb=> insert into test_bool values(null,null); INSERT 0 1  HINT:  You will need to rewrite or cast the expression. mydb=> select * from test_bool;注意插入null之后,是由数据,只不过数据值为空  a | b  ---+---  t | f  t | f  t | f  t | f    |  (5 rows) 

2.网络地址类型

字符类型名称 存储长度 描述
cidr 7/19字节 IPV4/IPV6网络
inet 7/19字节 IPV4/IPV6网络
macaddr 7/19字节 MAC地址
macaddr8 7/19字节 MAC地址(EUI-64格式)

inet和cidr类型存储格式为IP地址/掩码,如果掩码省略,则IPV4掩码为32,IPV6掩码为128

mydb=> create table test_ipaddres(a cidr,b inet); CREATE TABLE mydb=>  mydb=> \d+ test_ipaddres                               Table public.test_ipaddres  Column | Type | Collation | Nullable | Default | Storage | Stats target | Description  --------+------+-----------+----------+---------+---------+--------------+-------------  a      | cidr |           |          |         | main    |              |   b      | inet |           |          |         | main    |              |   mydb=> insert into test_ipaddres values ('192.168.1.10/32','192.168.1.10/16'); INSERT 0 1 mydb=>  mydb=> select * from test_ipaddres  mydb-> ;         a        |        b         -----------------+-----------------  192.168.1.10/32 | 192.168.1.10/16  **inet和cidr类型的数据都会对数据进行是否合法的检查** mydb=> select '192.168.1.300'::cidr; ERROR:  invalid input syntax for type cidr: 192.168.1.300 LINE 1: select '192.168.1.300'::cidr;                ^ mydb=> select '192.168.1.300'::inet; ERROR:  invalid input syntax for type inet: 192.168.1.300 LINE 1: select '192.168.1.300'::inet;                ^ mydb=>  **cidr会默认输出掩码信息,inet不会输出掩码信息** mydb=> select '192.168.1.100'::inet;      inet       ---------------  192.168.1.100 (1 row)  mydb=> select '192.168.1.100'::cidr;        cidr        ------------------  192.168.1.100/32 (1 row)  mydb=>  **cidr会对IP和掩码进行合法性检查,inet不会** mydb=> select '192.168.1.100/24'::inet;        inet        ------------------  192.168.1.100/24 (1 row)  mydb=>  mydb=>  mydb=> select '192.168.1.100/24'::cidr; ERROR:  invalid cidr value: 192.168.1.100/24 LINE 1: select '192.168.1.100/24'::cidr;                ^ DETAIL:  Value has bits set to right of mask. mydb=>   取IP值 mydb=> select host(cidr '192.168.1.232/32');      host       ---------------  192.168.1.232 (1 row) 取IP和掩码 mydb=> select text(cidr '192.168.1.232/32');        text        ------------------  192.168.1.232/32 (1 row)  取子网掩码 mydb=> select netmask(cidr '192.168.1.232/32');      netmask      -----------------  255.255.255.255 (1 row)