PostgreSQL-数据类型（data type）

运维小路

发布于 2026-01-26 12:29:33

1470

文章被收录于专栏：运维小路运维小路

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL

PostgreSQL（本章节）

MongoDB

Redis

Etcd

我们前面介绍过MySQL的数据类型，PostgreSQL的数据类型和MySQL数据类型有一定的相似的地方也有很多不一样的地方，我们还是从数据的不同类型来介绍。

1. 数值类型

整数类型

-- 整数类型
smallint      -- 2字节，-32768 到 +32767
integer       -- 4字节，-2147483648 到 +2147483647
bigint        -- 8字节，-9223372036854775808 到 +9223372036854775807

-- 自增整数（实际是特殊的整数类型）
smallserial   -- 2字节自增
serial        -- 4字节自增  
bigserial     -- 8字节自增

精确小数

-- 精确小数
numeric(precision, scale)  -- 例如：numeric(10,2) 总共10位，小数2位
decimal(precision, scale)  -- 与 numeric 相同

浮点数

-- 浮点数
real         -- 4字节，6位十进制精度
double precision -- 8字节，15位十进制精度

2. 字符类型

-- 固定长度
char(n)          -- 定长，不足补空格
character(n)     -- char(n) 的别名

-- 可变长度
varchar(n)       -- 变长，最大n字符
character varying(n) -- varchar(n) 的别名
text            -- 无限长度变长字符串

-- 示例
CREATE TABLE text_example (
    fixed_char CHAR(10),        -- 总是占用10字符
    variable_char VARCHAR(50),  -- 最大50字符
    unlimited_text TEXT         -- 无长度限制
);

3. 日期/时间类型

-- 日期时间类型
timestamp [without time zone]  -- 日期和时间（无时区）
timestamp with time zone       -- 日期和时间（带时区）
date                          -- 只包含日期
time [without time zone]      -- 只包含时间（无时区）  
time with time zone           -- 只包含时间（带时区）
interval                      -- 时间间隔

-- 示例
CREATE TABLE time_example (
    event_time TIMESTAMP,              -- 2024-01-15 14:30:00
    event_time_tz TIMESTAMPTZ,         -- 2024-01-15 14:30:00+08
    event_date DATE,                   -- 2024-01-15
    duration INTERVAL                  -- 2 hours 30 minutes
);

4. 布尔类型

-- 布尔类型
boolean
-- 可接受的值：TRUE, FALSE, NULL
-- 缩写：t, f, yes, no, 1, 0, on, off

CREATE TABLE bool_example (
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE
);

5. 二进制数据类型

-- 二进制数据
bytea  -- 变长二进制字符串

-- 示例
CREATE TABLE binary_example (
    image_data BYTEA,
    file_content BYTEA
);

6. 几何类型

-- 几何类型
point     -- 平面上的点 (x,y)
line      -- 无限长的线
lseg      -- 线段
box       -- 矩形
path      -- 几何路径
polygon   -- 多边形
circle    -- 圆

-- 示例
CREATE TABLE geometry_example (
    location POINT,
    area POLYGON,
    radius CIRCLE
);

7. 网络地址类型

-- 网络地址类型
cidr      -- IPv4 或 IPv6 网络
inet      -- IPv4 或 IPv6 主机地址
macaddr   -- MAC 地址

-- 示例
CREATE TABLE network_example (
    ip_address INET,
    network CIDR,
    mac_address MACADDR
);

8. 数组类型

-- 数组类型（任何数据类型都可以创建数组）
integer[]        -- 整数数组
text[]           -- 文本数组
varchar(10)[]    -- 可变字符串数组

-- 示例
CREATE TABLE array_example (
    user_ids INTEGER[],                    -- {1,2,3,4,5}
    tags TEXT[],                           -- {'sql','postgres','database'}
    scores DOUBLE PRECISION[]              -- {98.5,87.2,92.0}
);

-- 数组操作
INSERT INTO array_example VALUES 
    (ARRAY[1,2,3], ARRAY['a','b','c'], ARRAY[1.1,2.2,3.3]);

SELECT user_ids[1] FROM array_example;     -- 访问第一个元素

9. JSON 类型

-- JSON 类型
json      -- 存储原始 JSON 数据
jsonb     -- 以二进制格式存储 JSON（推荐，支持索引）

-- 示例
CREATE TABLE json_example (
    metadata JSON,
    config JSONB
);

INSERT INTO json_example VALUES (
    '{"name": "John", "age": 30, "hobbies": ["reading", "swimming"]}',
    '{"settings": {"theme": "dark", "notifications": true}}'
);

-- JSON 查询
SELECT metadata->>'name' as name FROM json_example;
SELECT config @> '{"settings": {"theme": "dark"}}' as has_dark_theme;

10. 范围类型

-- 范围类型
int4range     -- integer 范围
int8range     -- bigint 范围
numrange      -- numeric 范围
tsrange       -- 无时区时间戳范围
tstzrange     -- 有时区时间戳范围
daterange     -- 日期范围

-- 示例
CREATE TABLE range_example (
    age_range INT4RANGE,           -- [18,60)
    price_range NUMRANGE,          -- [100.00,500.00]
    reservation_daterange DATERANGE -- [2024-01-01,2024-01-07]
);

INSERT INTO range_example VALUES 
    ('[18,60)', '[100.00,500.00]', '[2024-01-01,2024-01-07]');

11. 特殊类型

-- 特殊类型
uuid          -- 通用唯一标识符
xml           -- XML 数据
tsvector      -- 文本搜索向量
tsquery       -- 文本搜索查询
bit(n)        -- 定长位串
bit varying(n) -- 变长位串

-- 示例
CREATE TABLE special_example (
    user_uuid UUID DEFAULT gen_random_uuid(),
    document XML,
    search_index TSVECTOR
);

12. 枚举类型

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

-- 使用枚举类型
CREATE TABLE person (
    name TEXT,
    current_mood mood
);

INSERT INTO person VALUES ('John', 'happy');

13. 复合类型

-- 创建复合类型
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip_code VARCHAR(10)
);

-- 使用复合类型
CREATE TABLE customer (
    name TEXT,
    home_address address
);

INSERT INTO customer VALUES ('Alice', ROW('123 Main St', 'New York', '10001'));

14. 域类型（Domain）

-- 创建域类型（带约束的基础类型）
CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0);

-- 使用域类型
CREATE TABLE domain_example (
    id positive_int,
    name TEXT
);