postgresql中网络地址类型和布尔类型(Network address type and boolean type in 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)
————————

< strong > create test users, tablespaces, and databases, and grant < / strong > permissions

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 type < / strong >

Character type name storage length description
Boolean 1byte value is true or 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. Network address type < / strong >

Character type name storage length description
CIDR 7 / 19 byte IPv4 / IPv6 network
INET 7 / 19 byte IPv4 / IPv6 network
Mac addr 7 / 19 byte MAC address
Mac addr8 7 / 19 byte MAC address (eui-64 format)

The storage format of INET and CIDR types is IP address / mask. If the mask is omitted, the IPv4 mask is 32 and the IPv6 mask is 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)