PostgreSQL数据类型一览（数值类型）

文章目录

• **一、数值类型**
• • **1. 整数类型**
  • • - **smallint**：2 字节，范围 -32768 到 32767，适合存储小范围整数，节省存储空间。
    • - **integer (int)**：4 字节，范围 -2147483648 到 2147483647，最常用的整数类型。
    • - **bigint**：8 字节，范围 -9223372036854775808 到 9223372036854775807，用于需要超大范围的场景。
  • **2. 浮点类型**
  • • - **real**：4 字节，单精度浮点数，约 6 位有效数字，适合科学计算但存在精度损失。
    • - **double precision**：8 字节，双精度浮点数，约 15 位有效数字，精度更高。
  • **3. 精确数值类型**
  • • - **decimal/numeric**：可变长度，用户指定精度（如 `numeric(10,2)` 表示总共 10 位，小数部分 2 位），完全精确，适用于金融计算。
    • - **serial/bigserial**：自增整数类型（`serial` 对应 `integer`，`bigserial` 对应 `bigint`），底层通过序列实现，常用于主键。
  • **4. 特殊浮点值**
  • • - **Infinity / -Infinity**：表示正负无穷（需用单引号包裹，如 `'Infinity'::float`）。
    • - **NaN**：非数字值（Not a Number），比较时 PostgreSQL 视为相等，且大于所有非 NaN 值。
• **二、字符串类型**
• • **1. 可变长度字符串**
  • • - **text**：无长度限制的字符串，适合存储任意长度的文本。
    • - **varchar(n)**：最多 n 字符的字符串（n 最大 10^6），超出会报错。
    • - **character varying(n)**：与 `varchar(n)` 等价，SQL 标准写法。
  • **2. 固定长度字符串**
  • • - **char(n)**：固定 n 字符长度，不足补空格，适合长度固定的字段（如国家代码）。
    • - **character(n)**：与 `char(n)` 等价。
• **三、日期与时间类型**
• • **1. 日期类型**
  • • - **date**：存储日期（年-月-日），占用 4 字节。
    • - **time [without time zone]**：存储时间（时:分:秒），不带时区，占用 8 字节。
    • - **time with time zone**：带时区的时间，占用 12 字节。
    • - **timestamp [without time zone]**：日期和时间组合，不带时区，占用 8 字节。
    • - **timestamp with time zone (timestamptz)**：带时区的日期时间，自动转换为 UTC 存储，占用 8 字节。
  • **2. 时间间隔**
  • • - **interval [fields] [precision]**：表示时间段（如 `interval '3 days'`），可指定精度（如秒的小数位数）。
• **四、布尔类型**
• • • - **boolean**：1 字节，存储 `true`、`false` 或 `null`，支持逻辑运算（`AND`、`OR`、`NOT`）。
• **五、二进制数据类型**
• • • - **bytea**：存储二进制数据（如文件、图片），支持十六进制或转义格式输入（如 `E'\\xDEADBEEF'`）。
• **六、JSON 类型**
• • **1. JSON**
  • • - **json**：存储 JSON 数据，查询时不验证格式，适合存储原始 JSON。
    • - **jsonb**：以二进制格式存储 JSON，支持索引和高效查询，但会丢失键顺序和空格。
  • **2. JSON 函数**
  • • - 提供丰富的函数（如 `json_extract_path`、`json_agg`）进行数据操作。
• **七、数组类型**
• • • - **array**：支持所有基本类型的数组（如 `int[]`、`text[]`），使用方括号访问元素（如 `arr[1]`）。
    • - 支持多维数组和嵌套数组。
• **八、范围类型（Range Types）**
• • • - **numrange**：数值范围（如 `[1.1, 5.5]`）。
    • - **tsrange**：时间范围（不带时区）。
    • - **tstzrange**：带时区的时间范围。
    • - **int4range/int8range**：整数范围。
    • - **daterange**：日期范围。
• **九、网络地址类型**
• • • - **inet**：IPv4/IPv6 地址和子网（如 `'192.168.1.0/24'`）。
    • - **cidr**：IPv4/IPv6 子网地址。
    • - **macaddr**：MAC 地址（如 `'08:00:2b:01:02:03'`）。
• **十、UUID 类型**
• • • - **uuid**：存储通用唯一标识符（UUID），格式如 `123e4567-e89b-12d3-a456-426614174000`。
• **十一、几何类型**
• • • - **point**：二维点（如 `(x,y)`）。
    • - **line**：直线。
    • - **lseg**：线段。
    • - **box**：矩形框。
    • - **path**：闭合或开放的路径。
    • - **polygon**：多边形。
    • - **circle**：圆（中心点和半径）。
• **十二、文本搜索类型**
• • • - **tsvector**：文本搜索向量（分词后的文本）。
    • - **tsquery**：文本搜索查询条件（如 `'fat & (cat | dog)'`）。
• **十三、XML 类型**
• • • - **xml**：存储 XML 数据，支持验证和 XPath 查询。
• **十四、HStore 类型**
• • • - **hstore**：键值对存储（如 `"color"=>"red", "size"=>"large"`），适合存储稀疏数据。
• **十五、枚举类型**
• • • - **enum**：用户自定义枚举类型（如 `CREATE TYPE color AS ENUM ('red', 'blue')`）。
• **十六、复合类型**
• • • - **composite type**：由多个字段组成的类型（如表的行类型），可通过 `ROW()` 构造。
• **十七、伪类型**
• • • - **void**：表示无返回值。
    • - **anyelement/anyarray/anynonarray**：用于函数泛型（如 `+` 运算符支持任意类型）。
    • - **cstring**：C 风格字符串，用于扩展开发。
• **十八、其他特殊类型**
• • • - **pg_lsn**：日志序列号（Log Sequence Number），用于复制。
    • - **txid_snapshot**：事务快照（用于并发控制）。
    • - **ltree**：扩展类型，支持层级路径（需安装 `ltree` 扩展）。
    • - **tsm_system_rows/tsm_system_time**：用于表采样。
• **使用建议**
• • • - **高精度计算**：优先使用 `numeric`。
    • - **存储 JSON**：优先使用 `jsonb` 以提高查询性能。
    • - **时间处理**：带时区的场景必须使用 `timestamptz`。
    • - **数组与范围**：适合需要批量操作或区间查询的场景。
    • - **网络地址**：使用 `inet` 或 `cidr` 进行子网匹配和地址校验。