PostgreSQL 数据类型

PostgreSQL 提供了丰富的内置数据类型用于定义业务表结构,常用的比如数值类型、字符类型、日期时间类型、布尔类型、二进制类型、json类型等等。此外,用户还可以使用 CREATE TYPE 命令创建自定义数据类型。

本文主要介绍 PostgreSQL 数据库内置数据类型,自定义数据类型将在后面单独介绍。

1. 数值类型

  • smallint,别名 int2,2 字节有符号整数
  • integer,别名 int,int4,4 字节有符号整数
  • bigint,别名 int8,8 字节有符号整数
  • smallserial,别名 serial2,2 字节自增整数
  • serial,别名 serial4,4 字节自增整数
  • bigserial,别名 serial8,8 字节自增整数
  • real,别名 float4,单精度浮点数
  • double precision,float8,双精度浮点数
  • numeric [ (p, s) ],别名 decimal [ (p, s) ],可选精度的精确数字类型

2. 字符类型

  • character [ (n) ],别名 char [ (n) ],固定长度的字符串类型
  • character varying [ (n) ],别名 varchar [ (n) ],可变长度的字符串类型
  • text,可变长度的字符串类型

3. 时间日期类型

  • date,日期类型
  • time [ (p) ] [ without time zone ],不带时区的时间类型
  • time [ (p) ] with time zone,别名 timetz,带时区的时间类型
  • timestamp [ (p) ] [ without time zone ],不带时区的日期时间类型
  • timestamp [ (p) ] with time zone,别名 timestamptz,带时区的日期时间类型
  • interval [ fields ] [ (p) ],时间跨度类型

4. 布尔类型

  • boolean,别名 bool,逻辑布尔类型

5. 二进制类型

  • bit [ (n) ],固定长度的比特串类型
  • bit varying [ (n) ],别名 varbit [ (n) ],可变长度的比特串类型
  • bytea,二进制数据类型

6. 枚举类型

枚举类型需要通过 create type 方式预先定义,如下所示:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

7. JSON类型

  • json,文本 json 数据类型
  • jsonb,二进制 json 数据类型

8. XML类型

  • xml,XML 数据类型,编译数据库时需要加上 --with-libxml 编译选项

9. 几何类型

  • point,平面几何点类型
  • line,平面几何线类型
  • lseg,平面几何线段类型
  • path,平面几何路径类型
  • circle,平面几何圆类型
  • box,平面几何矩形类型
  • polygon,平面几何多边形类型

10. 网络地址类型

  • macaddr,网卡 MAC 地址类型,6 字节
  • macaddr8,网卡 MAC 地址类型,8 字节
  • inet,IPv4 或者 IPv6 主机地址类型
  • cidr,IPv4 或者 IPv6 网络地址类型

11. 货币类型

  • money,货币金额类型

12. UUID 类型

  • uuid,通用唯一标识符类型

13. 范围类型

  • int4range,int4 范围类型
  • int8range ,int8 范围类型
  • numrange,numeric 范围类型
  • tsrange,不带时区的时间范围类型
  • tstzrange,带时区的时间范围类型
  • daterange,日期范围类型

范围类型示例:

postgres=# create table t(c1 int4range, c2 tsrange);
CREATE TABLE
postgres=# insert into t values ('[10,20]', '[2022-01-01 14:30:00, 2022-12-01 14:30:00]');
INSERT 0 1
postgres=# select * from t;
   c1    |                      c2
---------+-----------------------------------------------
 [10,21) | ["2022-01-01 14:30:00","2022-12-01 14:30:00"]
(1 row)

14. 数组类型

在基础类型后面加上 [] 即可定义数组类型,比如 integer[],两个 [] 则表示二维数组类型,多个 [] 则表示多维数组类型,比如 text[][][]。

数组类型的使用示例如下:

create table t(c1 int[], c2 text[][]);
insert into t values('{1,2,3,4,5}',
                     '{{''a'',''b'',''c''},{''d'',''e'',''f''}}');

15. 全文检索相关的数据类型

  • tsquery,全文检索查询类型
  • tsvector,全文检索文档类型

16. 其他数据类型

  • pg_lsn,PostgreSQL LSN 类型
  • pg_snapshot,用户级别的事务 ID 快照类型
  • txid_snapshot,用户级别的事务 ID 快照类型(已废弃,建议使用 pg_snapshot)

文章评论

0条评论