PostgreSQL学习总结（3）—— PostgreSQL 数据类型

阅读量：795 次

发布时间：2023-03-03

本文共 4054 字，大约阅读时间需要 13 分钟。

PostgreSQL 数据类型全面解析

PostgreSQL 是一款强大的开源数据库管理系统，其数据类型种类丰富，涵盖了从基础到高级的各种类型。以下将详细介绍 PostgreSQL 的各个数据类型及其应用场景。

数值类型

数值类型是 PostgreSQL 最基础的数据类型，主要用于存储整数和浮点数。根据存储空间可以分为 2 字节、4 字节和 8 字节的整数类型，以及 4 字节和 8 字节的浮点数类型。

数据类型	存储空间	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	精确小数	小数点前 131072 位；小数点后 16383 位
numeric	可变长	精确小数	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度浮点数	6 位十进制精度
double precision	8 字节	可变精度浮点数	15 位十进制精度
smallserial	2 字节	自增小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增大范围整数	1 到 9223372036854775807

货币类型

money 数据类型专门用于存储货币金额，支持固定小数精度。money 类型可以与 int、bigint 和 numeric 类型互相转换，但不建议使用浮点数来处理货币数据。

数据类型	存储空间	描述	范围
money	8 字节	货币金额	-92233720368547758.08 到 +92233720368547758.07

字符类型

字符类型用于存储文本数据，根据长度和是否有固定大小可以分为定长和变长类型。

数据类型	描述	长度限制
character varying(n) 或 varchar(n)	变长，具有长度限制	n
character(n) 或 char(n)	定长，不足时用空白填充	n
text	变长，无长度限制	无

日期/时间类型

日期和时间类型用于存储日期、时间或两者的组合。PostgreSQL 提供多种时间类型，支持不同的精度和时区。

数据类型	存储空间	描述	最低值	高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间（无时区）	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间（有时区）	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	00:00:00	24:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

布尔类型

布尔类型用于存储布尔值，支持 true、false 和 unknown（用 NULL 表示）。

数据类型	存储空间	描述
boolean	1 字节	`true` 或 `false`

枚举类型

枚举类型允许创建一个有序的值集合，适用于需要离散值的场景。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

枚举类型可以用于表字段定义，如：

CREATE TABLE person (    name text,    current_mood mood);INSERT INTO person VALUES ('Moe', 'happy');SELECT * FROM person WHERE current_mood = 'happy';

几何类型

几何类型用于存储平面物体的坐标，常用于空间分析。

数据类型	存储空间	描述	表现形式
point	16 字节	平面中的点	(x, y)
line	32 字节	(无穷)直线	((x1, y1), (x2, y2))
lseg	32 字节	有限线段	((x1, y1), (x2, y2))
box	32 字节	矩形	((x1, y1), (x2, y2))
path	16+16n 字节	闭合路径	((x1, y1), ...)
path	16+16n 字节	开发路径	[(x1, y1), ...]
polygon	40+16n 字节	多边形	((x1, y1), ...)
circle	24 字节	圆	(x, y), r

网络地址类型

用于存储 IPv4、IPv6 和 MAC 地址的数据类型，提供了输入错误检查和其他特殊功能。

数据类型	存储空间	描述
cidr	7 或 19 字节	IPv4 或 IPv6 网络
inet	7 或 19 字节	IPv4 或 IPv6 主机和网络
macaddr	6 字节	MAC 地址

IPv4 地址在排序时总是排在 IPv6 地址前面，包括封装或映射的 IPv6 地址。

位串类型

位串类型用于存储一串 1 和 0 的字符串，常用于表示掩码或二进制数据。

数据类型	描述
bit(n)	必须准确匹配长度 n
bit varying(n)	最多存储 n 位，最长的串会被拒绝

文本搜索类型

文本搜索类型用于支持全文检索，PostgreSQL 提供 tsvector 和 tsquery 数据类型。

数据类型	描述
tsvector	存储无重复词汇的标准化版本列表
tsquery	用于布尔检索，支持 AND、OR 和 NOT 操作

UUID 类型

UUID（通用唯一标识符）用于存储 RFC 4122 标准定义的 128 位唯一标识符。

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML 类型

XML 类型用于存储结构化的 XML 数据，支持类型安全的值验证。

XMLPARSE (DOCUMENT '
   
   
   
   
   
   
    
    
    Manual
    
    
    
     
     ...
     
    
    
    
   
   ')

JSON 类型

JSON 类型用于存储 JSON 数据，支持相关函数进行操作。

array_to_json('{ 
   
      
   {1,5},{99,100}}'::int[])

数组类型

PostgreSQL 支持多维数组，数组类型可以是任何基本类型或用户定义类型。

声明数组

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

插入值

INSERT INTO sal_empVALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}');

访问数组

SELECT name FROM sal_emp WHERE pay_by_quarter[1] > pay_by_quarter[2];

复合类型

复合类型用于定义记录的结构，类似于数据库表中的字段。

CREATE TYPE complex AS (r double precision, i double precision);CREATE TYPE inventory_item AS (name text, supplier_id integer, price numeric);CREATE TABLE on_hand (    item inventory_item,    count integer);INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

范围类型

范围类型用于存储某一元素类型在一定范围内的值，PostgreSQL 提供多种内置范围类型。

示例

CREATE TABLE reservation (    room int,    during tsrange);INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');SELECT int4range(10, 20) @> 3;