PostgreSQL 数据类型详解

PostgreSQL 是一种功能强大的开源关系型数据库管理系统，它支持丰富的数据类型，可满足各种不同的应用场景需求。以下是对 PostgreSQL 常见数据类型的详细介绍：

1. 数值类型

整数类型

• smallint：占用 2 个字节，存储范围为 -32768 到 32767。适用于存储较小的整数值，例如表示月份（1 - 12）、年龄等，可节省存储空间。

CREATE TABLE example (
    id smallint
);

• integer：占用 4 个字节，存储范围为 -2147483648 到 2147483647。是最常用的整数类型，可用于存储大多数常见的整数值，如用户 ID、商品数量等。

CREATE TABLE products (
    product_id integer,
    quantity integer
);

• bigint：占用 8 个字节，存储范围为 -9223372036854775808 到 9223372036854775807。用于存储非常大的整数值，例如大型网站的用户访问量统计等。

CREATE TABLE website_stats (
    visit_count bigint
);

浮点类型

• real：单精度浮点数，占用 4 个字节，精度约为 6 位小数。适用于对精度要求不是特别高的浮点数计算，如一些简单的科学计算或统计数据。

CREATE TABLE measurements (
    temperature real
);

• double precision：双精度浮点数，占用 8 个字节，精度约为 15 位小数。用于需要更高精度的浮点数计算，如金融领域的货币计算、科学研究中的精确测量等。

CREATE TABLE financial_data (
    exchange_rate double precision
);

定点数类型

• numeric(precision, scale)：可存储任意精度的小数，precision 表示总位数，scale 表示小数位数。适用于对精度要求极高且需要精确计算的场景，如金融交易中的货币金额计算。

CREATE TABLE transactions (
    amount numeric(10, 2)
);

2. 字符类型

• char(n)：固定长度的字符类型，最多存储 n 个字符。如果存储的字符串长度小于 n，会用空格填充。适用于存储长度固定的字符串，如身份证号码、邮政编码等。

CREATE TABLE users (
    postal_code char(6)
);

• varchar(n)：可变长度的字符类型，最多存储 n 个字符。只存储实际输入的字符串长度，不会进行填充。常用于存储长度不固定但有最大长度限制的字符串，如用户名、商品名称等。

CREATE TABLE products (
    product_name varchar(100)
);

• text：可变长度的字符类型，没有长度限制。适用于存储任意长度的文本内容，如文章正文、产品描述等。

CREATE TABLE articles (
    content text
);

3. 日期和时间类型

• date：存储日期，格式为 YYYY-MM-DD。用于记录与日期相关的信息，如出生日期、订单日期等。

CREATE TABLE customers (
    birth_date date
);

• time：存储时间，格式为 HH:MM:SS。可用于记录事件发生的具体时间，如会议开始时间、上班打卡时间等。

CREATE TABLE meetings (
    start_time time
);

• timestamp：存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS。常用于记录事件发生的具体时刻，如用户注册时间、数据更新时间等。

CREATE TABLE user_logs (
    register_time timestamp
);

• interval：存储时间间隔，可用于计算时间差或进行时间偏移操作。例如，计算两个日期之间的天数差、给某个时间加上一定的时间间隔等。

SELECT '2024-01-01'::date + INTERVAL '10 days';

4. 布尔类型

• boolean：只有两个取值：true 和 false，也可以用 1 和 0 分别表示。常用于存储逻辑判断结果，如用户是否激活、商品是否上架等。

CREATE TABLE users (
    is_active boolean
);

5. 枚举类型

• 自定义枚举类型：可以创建自定义的枚举类型，限制字段的取值范围。例如，定义一个表示订单状态的枚举类型。

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'completed');
CREATE TABLE orders (
    status order_status
);

6. 数组类型

• 一维数组：可以存储相同数据类型的多个值。例如，存储用户的多个爱好。

CREATE TABLE users (
    hobbies text[]
);
INSERT INTO users (hobbies) VALUES (ARRAY['reading', 'swimming', 'painting']);

• 多维数组：支持多维数组，如二维数组可用于存储矩阵数据。

CREATE TABLE matrices (
    data integer[][]
);

7. JSON 和 JSONB 类型

• json：存储 JSON 数据，以文本形式保存，在存储和读取时不会对数据进行解析，只是简单的字符串处理。

CREATE TABLE products (
    metadata json
);
INSERT INTO products (metadata) VALUES ('{"color": "red", "size": "large"}');

• jsonb：也是存储 JSON 数据，但会对数据进行二进制解析和存储，支持索引和高效的查询操作。

CREATE TABLE products (
    metadata jsonb
);
INSERT INTO products (metadata) VALUES ('{"color": "red", "size": "large"}');

8. 几何类型

• point：表示二维平面上的一个点，由 (x, y) 坐标组成。可用于地理信息系统（GIS）中表示地理位置等。

CREATE TABLE locations (
    coord point
);
INSERT INTO locations (coord) VALUES (POINT(1.0, 2.0));

• line：表示一条无限长的直线，由三个系数 (A, B, C) 定义，满足方程 Ax + By + C = 0。

CREATE TABLE lines (
    line_info line
);

• polygon：表示一个多边形，由一系列的点按顺序连接而成。常用于表示地理区域、图形等。

CREATE TABLE areas (
    polygon_info polygon
);

 

PostgreSQL 的丰富数据类型为开发者提供了强大的工具，能够满足各种复杂的应用需求。在实际使用中，应根据具体的业务场景和数据特点选择合适的数据类型，以提高数据库的性能和数据的完整性。