PostgreSQL数据类型好丰富！

呆呆的私房菜
18 阅读7分钟

PostgreSQL数据类型好丰富！

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

业务范围：数据库安装部署、日常维护、主备切换、故障处理、性能优化、技术培训等。
需要的伙伴或者商业合作请移步 公众号【呆呆的私房菜】获取联系方式。

阅读本文可以了解PostgreSQL丰富的数据类型，以及相关的数据类型扩展插件。

01 数据类型概述

在计算机科学与编程中，数据类型主要用于存储和操作数据。PostgreSQL也具备丰富的数据类型，分类如下：

字符串类型char(n)、varchar(n)，text
数值类型smallint，int，bigint，decimal，numeric，real，double precision，smallserial，serial，bigserial
布尔类型boolean
时间/日期类型date，time，timestamp，interval
枚举类型point、line、lseg、box、path、polygon、circle
几何类型composite
网络地址类型cidr，inet、macaddr、macaddr8
位串类型bit、
数组类型array
复合类型enum
文本搜索类型tsvector、tsquery
XML类型xml
JSON类型json、jsonb

02 数据类型详解

2.1 字符串类型

test=# CREATE TABLE test1 (a character(4));
CREATE TABLE
test=# INSERT INTO test1 VALUES ('ok');
INSERT 0 1
test=# SELECT a, char_length(a) FROM test1; -- (1)
  a   | char_length
------+-------------
 ok   |           2
(1 行记录)

test=# CREATE TABLE test2 (b varchar(5));
CREATE TABLE
test=# INSERT INTO test2 VALUES ('ok');
INSERT 0 1
test=# INSERT INTO test2 VALUES ('good      ');
INSERT 0 1
test=# INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
test=#
test=# INSERT INTO test2 VALUES ('too long'::varchar(5)); --显示截断数据，只展示5个字符
INSERT 0 1
test=# SELECT b, char_length(b) FROM test2;
   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5
(3 行记录)

2.2 数值类型

## 注意：numeric数字类型中，NaN被认为不等于任何其他数字值（包括NaN）。
## 为了允许numeric值可以被排序和使用基于树的索引，PostgreSQL把NaN值视为相等，并且比所有非NaN值都要大。

##在对值进行圆整时，numeric类型圆到远离零的整数，而（在大部分机器上）real和double precision类型会圆到最近的偶数上。
test=# SELECT value,
  round(value::numeric) AS "numeric圆整",
  round(value::double precision) AS "doule圆整"
FROM generate_series(-3.5, 3.5, 1) as value;
 value | numeric圆整 | doule圆整
-------+-------------+-----------
  -3.5 |          -4 |        -4
  -2.5 |          -3 |        -2
  -1.5 |          -2 |        -2
  -0.5 |          -1 |        -0
   0.5 |           1 |         0
   1.5 |           2 |         2
   2.5 |           3 |         2
   3.5 |           4 |         4
(8 行记录)


## serial演示，类似于mysql的auto_increment
test=# create table test3 (
id serial primary key,
name varchar not null
);
CREATE TABLE

## 实际上自动为test3表增加了一个序列
test=# \ds
                           关联列表
 架构模式 |             名称              |  类型  |  拥有者
----------+-------------------------------+--------+----------
 public   | test3_id_seq                  | 序列数 | postgres

test=# insert into test3 values (DEFAULT,'t1');
INSERT 0 1
test=# insert into test3 values (DEFAULT,'t2');
INSERT 0 1

test=# select * from test3;
 id | name
----+------
  1 | t1
  2 | t2
(2 行记录)

## 查看当前序列值
test=# select currval('test3_id_seq');
 currval
---------
       2
(1 行记录)

test=# select nextval('test3_id_seq');
 nextval
---------
       3
(1 行记录)

2.3 布尔类型

test=# CREATE TABLE test4 (a boolean, b text);
CREATE TABLE
test=# INSERT INTO test4 VALUES (TRUE, 't1');
INSERT 0 1
test=# INSERT INTO test4 VALUES (FALSE, 't2');
INSERT 0 1
test=# SELECT * FROM test4;
 a | b
---+----
 t | t1
 f | t2
(2 行记录)
test=# SELECT * FROM test4 WHERE a;
 a | b
---+----
 t | t1
(1 行记录)

2.4 时间/日期类型

2.5 枚举类型

## 创建枚举类型
test=# CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE

## 创建表
test=# CREATE TABLE person(
test(#     name varchar(10),
test(#     current_mood mood
test(# );
CREATE TABLE

test=# INSERT INTO person VALUES ('Moe', 'happy');
INSERT 0 1
test=# INSERT INTO person VALUES ('Moe', 'no');
ERROR:  invalid input value for enum mood: "no"
第1行INSERT INTO person VALUES ('Moe', 'no');
                                     
test=# SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
------+--------------
 Moe  | happy
(1 行记录)

2.6 几何类型

## 点
test=# select point '(1,1)';
 point
-------
 (1,1)
(1 行记录)

test=# select '(1,1)'::point;
 point
-------
 (1,1)
(1 行记录)

## 直线
test=# select lseg '(1,2),(3,2)';
     lseg
---------------
 [(1,2),(3,2)]
(1 行记录)

test=# select lseg '1,2,3,2';
     lseg
---------------
 [(1,2),(3,2)]
(1 行记录)

## 矩形
test=# select box '((0,0),(1,1))';
     box
-------------
 (1,1),(0,0)
(1 行记录)

test=# select box '(0,0),(1,1)';
     box
-------------
 (1,1),(0,0)
(1 行记录)


## 路径
test=# select path '(1,1),(2,2),(2,1)';
        path
---------------------
 ((1,1),(2,2),(2,1))
(1 行记录)

test=# select path '[(1,1),(2,2),(2,1)]';
        path
---------------------
 [(1,1),(2,2),(2,1)]
(1 行记录)


## 多边形
test=# select polygon '((1,1),(2,2),(2,1))';
       polygon
---------------------
 ((1,1),(2,2),(2,1))
(1 行记录)

## 圆
test=# select circle '<(0,0),1>';
  circle
-----------
 <(0,0),1>
(1 行记录)

## 平移
test=# select box '((0,0),(1,1))' + point '(2,0)';  -- 结果: (3,1),
  ?column?
-------------
 (3,1),(2,0)
(1 行记录)

2.7 网络地址类型

test=# CREATE TABLE networks (
test(#     id SERIAL PRIMARY KEY,
test(#     network_name VARCHAR(255),
test(#     ip_address INET,
test(#     subnet CIDR
test(# );
CREATE TABLE

## 插入一个网络地址
test=# INSERT INTO networks (network_name, ip_address, subnet) VALUES
test-# ('Local Network', '192.168.1.1', '192.168.1.0/24');
INSERT 0 1

## 插入另一个网络地址
test=# INSERT INTO networks (network_name, ip_address, subnet) VALUES
test-# ('Remote Network', '10.0.0.1', '10.0.0.0/8');
INSERT 0 1

## 插入一个包含多个地址的子网
test=# INSERT INTO networks (network_name, ip_address, subnet) VALUES
test-# ('Data Center', '172.16.0.1', '172.16.0.0/16');
INSERT 0 1

test=# SELECT id, network_name, ip_address, subnet FROM networks;
 id |  network_name  | ip_address  |     subnet
----+----------------+-------------+----------------
  1 | Local Network  | 192.168.1.1 | 192.168.1.0/24
  2 | Remote Network | 10.0.0.1    | 10.0.0.0/8
  3 | Data Center    | 172.16.0.1  | 172.16.0.0/16
(3 行记录)

2.8 位串类型
位串就是一串 1 和 0 的串。它们可以用于存储和可视化位掩码。我们有两种类型的 SQL 位类型：bit(n)和bit varying(n)，其中 n是一个正整数。

## bit类型的数据必须准确匹配长度n; 不允许存储短些或者长一些的位串
## bit varying数据是最长n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于 bit(1)，没有长度的bit varying意味着没有长度限制。
## 显式把位串值转换成bit(n)， 那么它的右边将被截断或者在右边补齐零，直到刚好n位

test=# CREATE TABLE test5 (a BIT(3), b BIT VARYING(5));
CREATE TABLE

test=# INSERT INTO test5 VALUES (B'101', B'00');
INSERT 0 1
test=# INSERT INTO test5 VALUES (B'10', B'101');
ERROR:  bit string length 2 does not match type bit(3)

test=# SELECT * FROM test5;
  a  | b
-----+----
 101 | 00
(1 行记录)

2.9 数组类型

test=# CREATE TABLE sal_emp (
test(#     name            text,
test(#     pay_by_quarter  integer[],
test(#     schedule        text[][]
test(# );
CREATE TABLE

test=# INSERT INTO sal_emp
test-#     VALUES ('Bill',
test(#     '{10000, 10000, 10000, 10000}',
test(#     '{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT 0 1

test=# INSERT INTO sal_emp
test-#     VALUES ('Carol',
test(#     '{20000, 25000, 25000, 25000}',
test(#     '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
INSERT 0 1

test=# SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 行记录)

test=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
 name
-------
 Carol
(1 行记录)

2.10 复合类型

## 创建复合类型
test=# CREATE TYPE inventory_item AS (
test(#      name           text,
test(#      supplier_id    integer,
test(#      price          numeric
test(# );
CREATE TYPE

## 字段声明为组合类型
CREATE TABLE on_hand (
    item    inventory_item,
    count   integer
);

## 插入数据
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

## 创建数据表的同时，会创建同名的type
test=# CREATE TABLE my_complex (name text, value complex);
CREATE TABLE

test=# INSERT INTO my_complex (name, value) VALUES ('one', ROW(1.0, 1.0));
INSERT 0 1

test=# SELECT * FROM my_complex;
 name | value
------+-------
 one  | (1,1)
(1 行记录)

test=# \dt
 public   | my_complex      | 数据表 | postgres

2.11 文本搜索类型
PostgreSQL提供两种数据类型，它们被设计用来支持全文搜索，全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动。tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询。

## tsvector会自动排序去重
test=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
(1 行记录)

## 可以存储用于搜索的词位
test=# SELECT 'fat & rat'::tsquery;
    tsquery
---------------
 'fat' & 'rat'
(1 行记录)

test=# SELECT 'fat & (rat | cat)'::tsquery;
          tsquery
---------------------------
 'fat' & ( 'rat' | 'cat' )
(1 行记录)

2.12 xml类型
xml数据类型可以被用来存储XML数据。它比直接在一个text域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作。

2.13 JSON类型

## 有零个或者更多元素的数组（元素不需要为同一类型）
test=# SELECT '[1, 2, "foo", null]'::json;
        json
---------------------
 [1, 2, "foo", null]
(1 行记录)

## 包含键值对的对象，注意对象键必须总是带引号的字符串
test=# SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
                       json
--------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active": false}
(1 行记录)

## 数组和对象可以被任意嵌套
test=# SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;
                        json
-----------------------------------------------------
 {"foo": [true, "bar"], "tags": {"a": 1, "b": null}}
(1 行记录)
avatar
文章
阅读
粉丝