PostgreSQL常用数据类型及实践二

05月

1683 0 0

七、网络地址类型

PostgreSQL也提供网络地址类型，以用于存储两大IP家族(IPv4 IPv6地址)地址和MAC地址的数据类型。

cidr（无类别域间路由，Classless Inter-Domain Routing）类型，保存一个IPv4或IPv6网络地址。声明网络格式为address/y，address表示IPv4或者IPv6地址，y表示子网掩码的二进制位数。如果省略y，则掩码部分使用已有类别的网络编号系统进行计算，但要求输入的数据已经包括了确定掩码所需的所有字节。

inet类型在一个数据区域内保存主机的IPv4或IPv6地址，以及一个可选子网。主机地址中网络地址的位数表示子网（“子网掩码”）。如果子网掩码是32并且地址是IPv4，则这个值不表示任何子网，只表示一台主机。在IPv6里，地址长度是128位，因此128位表示唯一的主机地址。

该类型的输入格式是address/y，address表示IPv4或者IPv6地址，y是子网掩码的二进制位数。如果省略/y，则子网掩码对IPv4是32，对IPv6是128，所以该值表示只有一台主机。如果该值表示只有一台主机，/y将不会显示。

inet和cidr类型之间的基本区别是inet接受子网掩码，而cidr不接受。

macaddr类型存储MAC地址，也就是以太网卡硬件地址（尽管MAC地址还用于其它用途）。

示例：

hrdb=> --创建IP地址及MAC地址表
hrdb=> CREATE TABLE tab_icm(col1 cidr,col2 inet,col3 macaddr);
CREATE TABLE
hrdb=> --字段注释
hrdb=> COMMENT ON COLUMN tab_icm.col1 IS '存储IPv4或IPv6网络地址类型';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col2 IS '存储IPv4或IPv6网络地址类型及子网';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col3 IS '存储设备MAC地址';
COMMENT
hrdb=> --插入数据
hrdb=> INSERT INTO tab_icm VALUES('10.10.20.10/32','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10.10.20/24','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10.10/16','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10/8','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('fe80::81a7:c17c:788c:7723/128','fe80::81a7:c17c:788c:7723','00-50-56-C0-00-01');
INSERT 0 1
hrdb=> --查询数据                                                                                                  
SELECT * FROM  tab_icm;
             col1              |           col2            |       col3        
-------------------------------+---------------------------+-------------------
 10.10.20.10/32                | 10.10.20.10               | 00:50:56:c0:00:08
 10.10.20.0/24                 | 10.10.20.10               | 00:50:56:c0:00:08
 10.10.0.0/16                  | 10.10.20.10               | 00:50:56:c0:00:08
 10.0.0.0/8                    | 10.10.20.10               | 00:50:56:c0:00:08
 fe80::81a7:c17c:788c:7723/128 | fe80::81a7:c17c:788c:7723 | 00:50:56:c0:00:01
(5 rows)

八、几何数据类型

PostgreSQL支持集合数据类型，用于存储GIS（地理信息系统）环境中的几何数据，用于地图测绘，城市交通轨迹，地图圈图等场景。

PostgreSQL支持以下几何数据类型：

点
线(射线)
线段
矩形
路径(包含开放路径【开放路径类似多边形】和闭合路径)
多边形
圆

对于以上几何类型而言，点是其它几何类型的基础。

对于所有的几何数据类型，都是使用二维坐标上面的横坐标和纵坐标来实现的。计算也是在二维坐标中进行的。

示例：

hrdb=> --创建几何数据类型表
hrdb=> CREATE TABLE tab_geometric(col1 point,col2 lseg,col3 box,col4 path,col5 path,col6 polygon,col7 circle);
CREATE TABLE
hrdb=> --字段注释
hrdb=> COMMENT ON COLUMN tab_geometric.col1 IS '二维几何的基本构造点(x,y)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col2 IS '线段((x1,y1),(x2,y2))';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col3 IS '矩形((x1,y1),(x1,y2),(x2,y1),(x2,y1)),';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col4 IS '开放路径((x1,y1),(x2,y2),(x3,y3),...)';
COMMENT
hrdb=> drop table tab_geometric ;
DROP TABLE
hrdb=> --创建几何数据类型表
hrdb=> CREATE TABLE tab_geometric(col1 point,col2 lseg,col3 box,col4 path,col5 path,col6 polygon,col7 circle);
CREATE TABLE
hrdb=> --字段注释
hrdb=> COMMENT ON COLUMN tab_geometric.col1 IS '二维几何的基本构造点(x,y)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col2 IS '线段[(x1,y1),(x2,y2)]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col3 IS '矩形((x1,y1),(x1,y2)),';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col4 IS '开放路径[(x1,y1),(x2,y2),(x3,y3),...]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col5 IS '闭合路径[(x1,y1),(x2,y2),(x3,y3),...,(xn,yn)]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col6 IS '多边形,相当于闭合路径((x1,y1),(x2,y2),(x3,y3),...,(xn,yn)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col7 IS '一组坐标点作为圆心和半径r构成<(x,y),r>';
COMMENT
hrdb=> --插入数据 
hrdb=> INSERT INTO tab_geometric
hrdb-> VALUES('(1,2)',
hrdb(>        '[(1,2),(2,3)]',
hrdb(>        '((1,2),(1,3))',
hrdb(>        '[(1,2),(2,3),(2,4),(1,3),(0,2)]',
hrdb(>        '[(1,2),(2,3),(3,4)]',
hrdb(>        '((1,2),(2,3),(2,4),(1,3),(0,2))',
hrdb(>        '<(2,3),3>');
INSERT 0 1
hrdb=> --查询数据
hrdb=> SELECT * FROM tab_geometric;
 col1  |     col2      |    col3     |              col4               |        col5         |              col6               |   col7    
-------+---------------+-------------+---------------------------------+---------------------+---------------------------------+-----------
 (1,2) | [(1,2),(2,3)] | (1,3),(1,2) | [(1,2),(2,3),(2,4),(1,3),(0,2)] | [(1,2),(2,3),(3,4)] | ((1,2),(2,3),(2,4),(1,3),(0,2)) | <(2,3),3>

九、JSON数据类型

JSON数据类型可以用来存储JSON（JavaScript Object Notation）数据。数据可以存储为text，但是JSON数据类型更有利于检查每个存储的数值是可用的JSON值。

在 PostgreSQL中，JSON数据类型有两种，原生JSON和JSONB。最主要的区别就是效率不同。JSON 数据类型对于输入文本进行复制，因此在解析时需要进行转换，输入速度块。而JSONB是对输入文本进行分解并以二进制存储，因此在解析时不需要进行转换，处理速度块，但是输入速度相对会慢。除此之外，JSONB数据类型还支持索引。

示例：

hrdb=> --创建JSON数据类型表
hrdb=> CREATE TABLE tab_json(col1 json,col2 jsonb);
CREATE TABLE
hrdb=> --字段注释
hrdb=> COMMENT ON COLUMN tab_json.col1 IS '存储json输入文本';
COMMENT
hrdb=> COMMENT ON COLUMN tab_json.col1 IS '存储json转换后的二进制文本';
COMMENT
hrdb=> --插入数据
hrdb=> --插入数据
hrdb=> INSERT INTO tab_json
hrdb-> VALUES('{"江苏省":"南京市","甘肃省":"兰州市","北京市":"北京市"}',
hrdb(>        '{"湖北省":"武汉市","四川省":"成都市","陕西省":"西安市"}');
INSERT 0 1
hrdb=> --给col1创建索引，将会不被支持。col2支持索引
hrdb=> CREATE INDEX idx_col1 ON tab_json USING GIN(col1);
ERROR:  data type json has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
hrdb=> CREATE INDEX idx_col2 ON tab_json USING GIN(col2);
CREATE INDEX
hrdb=> --查询数据
hrdb=> SELECT * FROM tab_json;
                          col1                           |                             col2                             
---------------------------------------------------------+--------------------------------------------------------------
 {"江苏省":"南京市","甘肃省":"兰州市","北京市":"北京市"} | {"四川省": "成都市", "湖北省": "武汉市", "陕西省": "西安市"}

温馨提示：

使用jsonb类型，可以使用PL/PYTHON映射为Python中表示的字典，列表等。

十、数组数据类型

PostgreSQL支持数组数据类型，同时支持多维数组。数组最大的优点就是按照数组下标访问，此时下标相当于一个索引，处理速度快。但是同时数组也有劣势，比如在删除或者添加数组元素需要对数组中的元素进行向前或者向后移动，这样导致删除或者添加数组元组时比较慢。

示例：

hrdb=> --创建数组表
hrdb=> CREATE TABLE tab_array(col1 text[],col2 integer[][],col3 integer ARRAY[3]);
CREATE TABLE
hrdb=> --字段注释
hrdb=> COMMENT ON COLUMN tab_array.col1 IS '文本类型一维数组';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col2 IS '整型类型二维数组';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col3 IS '声明长度为3的数组';
COMMENT
hrdb=> --插入数据
hrdb=> INSERT INTO tab_array
hrdb-> VALUES('{"江苏省","甘肃省","北京市"}',
hrdb(>        '{1,2,3,4,5}',
hrdb(>        '{21,22,31}');
INSERT 0 1
hrdb=> INSERT INTO tab_array
hrdb-> VALUES('{"天津市","湖北省","陕西市"}',
hrdb(>        '{5,4,3,2,1}',
hrdb(>        '{21,22,31,44}');
INSERT 0 1
hrdb=> --查询数据
hrdb=> SELECT * FROM tab_array;
          col1          |    col2     |     col3      
------------------------+-------------+---------------
 {江苏省,甘肃省,北京市} | {1,2,3,4,5} | {21,22,31}
 {天津市,湖北省,陕西市} | {5,4,3,2,1} | {21,22,31,44}
(2 rows)

hrdb=> --访问指定列中某个数组的元素
hrdb=> SELECT col1[1],col2[3],col3[4] FROM tab_array;
  col1  | col2 | col3 
--------+------+------
 江苏省 |    3 |     
 天津市 |    3 |   44

通过上述示例，可以发现，在PostgreSQL中，虽然声明了数组的长度，但是PostgreSQL对于数组的长度不会做任何限制。

同时访问数组元素从下标1开始，并且在PostgreSQL中并不会出现数组越界异常，如果数组的下标访问超过元素的长度，那么PostgreSQL便会返回一行空值。

以上就是常用数据类型介绍。但是在PostgreSQL中，除了上述数据类型外，还有其它的数据类型，比如XML数据类型，文本搜索数据类型，UUID数据类型，复合数据类型，范围类型，伪类型如any,anyelement,internal等等，在此不做一一介绍。

作者：宋少华，曾服务于国家电网冀北电力有限公司建设大数据平台，为人社局和北京市卫计委构建IT基础服务，为多家银行和证券公司构建web 服务器，系统及数据库维护；具有对税务局、国家电网、银行等政府行业和民营企业的IT培训经验；为相关安全行业设计DW数据仓库模型，使用PostgreSQL,Greenplum,HUAWEIGaussDB,Vertica和Clickhouse 做数据基础服务，开发TB级数据落地程序及百TB级别数据迁移程序。

标签： PostgreSQL

你还没有登录，请先登录或注册！

还没有人评论，欢迎说说您的想法！