数据类型

PostgreSQL拥有极其丰富的数据类型，本章将详细介绍各种数据类型及其使用方法。

数值类型

整数类型

-- 整数类型

-- SMALLINT：2字节，范围 -32768 到 32767
CREATE TABLE smallint_example (
    id SMALLINT
);
INSERT INTO smallint_example VALUES (100);

-- INTEGER (INT)：4字节，范围 -2147483648 到 2147483647
CREATE TABLE int_example (
    id INTEGER,
    age INT
);
INSERT INTO int_example VALUES (1, 25);

-- BIGINT：8字节，范围 -9223372036854775808 到 9223372036854775807
CREATE TABLE bigint_example (
    id BIGINT
);
INSERT INTO bigint_example VALUES (9223372036854775807);

-- 选择建议：
-- - 一般情况使用INTEGER
-- - 小范围数值使用SMALLINT（如年龄）
-- - 大数值使用BIGINT（如主键ID）

序列类型

-- 序列类型：自动递增

-- SMALLSERIAL：2字节自增
CREATE TABLE smallserial_example (
    id SMALLSERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- SERIAL：4字节自增
CREATE TABLE serial_example (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- BIGSERIAL：8字节自增
CREATE TABLE bigserial_example (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- 插入数据时不需要指定id
INSERT INTO serial_example (name) VALUES ('张三');
INSERT INTO serial_example (name) VALUES ('李四');

-- 查看当前序列值
SELECT currval('serial_example_id_seq');

-- 查看下一个序列值
SELECT nextval('serial_example_id_seq');

浮点类型

-- 浮点类型

-- REAL：4字节单精度浮点数，精度约6位
CREATE TABLE real_example (
    value REAL
);
INSERT INTO real_example VALUES (3.14159);

-- DOUBLE PRECISION：8字节双精度浮点数，精度约15位
CREATE TABLE double_example (
    value DOUBLE PRECISION
);
INSERT INTO double_example VALUES (3.14159265358979);

-- 注意：浮点数有精度问题
SELECT 0.1 + 0.2;  -- 结果可能是 0.30000000000000004

定点类型

-- 定点类型：精确的小数

-- NUMERIC(precision, scale)
-- precision：总位数
-- scale：小数位数

-- NUMERIC：任意精度
CREATE TABLE numeric_example (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),      -- 总共10位，小数2位
    amount NUMERIC(15, 4)      -- 总共15位，小数4位
);

INSERT INTO numeric_example (price, amount) VALUES (12345.67, 123456789.1234);

-- DECIMAL：NUMERIC的同义词
CREATE TABLE decimal_example (
    price DECIMAL(10, 2)
);

-- 定点数没有精度问题
SELECT 0.1::NUMERIC + 0.2::NUMERIC;  -- 结果精确为 0.3

字符串类型

字符类型

-- 字符类型

-- CHAR(n)/CHARACTER(n)：定长字符串，不足补空格
CREATE TABLE char_example (
    code CHAR(10)          -- 固定10个字符
);
INSERT INTO char_example VALUES ('ABC');  -- 存储为 'ABC       '

-- VARCHAR(n)/CHARACTER VARYING(n)：变长字符串，有长度限制
CREATE TABLE varchar_example (
    name VARCHAR(50)       -- 最多50个字符
);
INSERT INTO varchar_example VALUES ('张三');

-- TEXT：变长字符串，无长度限制
CREATE TABLE text_example (
    content TEXT           -- 无长度限制
);
INSERT INTO text_example VALUES ('很长的文本内容...');

-- 选择建议：
-- - 固定长度使用CHAR（如编码、手机号）
-- - 有长度限制使用VARCHAR
-- - 无长度限制使用TEXT

字符串函数

-- 常用字符串函数

-- 字符串连接
SELECT 'Hello' || ' ' || 'World';        -- Hello World
SELECT CONCAT('Hello', ' ', 'World');     -- Hello World

-- 字符串长度
SELECT LENGTH('你好');                    -- 2（字符数）
SELECT OCTET_LENGTH('你好');              -- 6（字节数，UTF-8）

-- 大小写转换
SELECT UPPER('hello');                    -- HELLO
SELECT LOWER('HELLO');                    -- hello
SELECT INITCAP('hello world');            -- Hello World

-- 截取字符串
SELECT SUBSTRING('Hello World', 1, 5);    -- Hello
SELECT SUBSTRING('Hello World', 7);       -- World
SELECT LEFT('Hello', 3);                  -- Hel
SELECT RIGHT('Hello', 3);                 -- llo

-- 替换字符串
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');
SELECT TRANSLATE('Hello', 'el', 'ip');    -- Hippo

-- 去除空格
SELECT TRIM('  Hello  ');                 -- Hello
SELECT LTRIM('  Hello  ');                -- Hello  
SELECT RTRIM('  Hello  ');                --   Hello

-- 填充字符串
SELECT LPAD('Hello', 10, '*');            -- *****Hello
SELECT RPAD('Hello', 10, '*');            -- Hello*****

-- 分割字符串
SELECT SPLIT_PART('a,b,c', ',', 2);       -- b

-- 字符串位置
SELECT POSITION('World' IN 'Hello World'); -- 7
SELECT STRPOS('Hello World', 'World');     -- 7

日期时间类型

日期类型

-- 日期类型

-- DATE：日期（年月日）
CREATE TABLE date_example (
    birth_date DATE
);
INSERT INTO date_example VALUES ('2024-01-15');
INSERT INTO date_example VALUES (CURRENT_DATE);

-- 当前日期
SELECT CURRENT_DATE;
SELECT TODAY;  -- 部分版本支持

-- 日期计算
SELECT CURRENT_DATE + INTERVAL '1 day';     -- 明天
SELECT CURRENT_DATE - INTERVAL '1 day';     -- 昨天
SELECT CURRENT_DATE + INTERVAL '1 month';   -- 下月同日
SELECT CURRENT_DATE + INTERVAL '1 year';    -- 明年同日

时间类型

-- 时间类型

-- TIME：时间（时分秒）
CREATE TABLE time_example (
    start_time TIME
);
INSERT INTO time_example VALUES ('14:30:00');
INSERT INTO time_example VALUES (CURRENT_TIME);

-- TIME WITH TIME ZONE：时间（带时区）
CREATE TABLE timetz_example (
    start_time TIME WITH TIME ZONE
);
INSERT INTO timetz_example VALUES ('14:30:00+08');

-- 当前时间
SELECT CURRENT_TIME;

时间戳类型

-- 时间戳类型

-- TIMESTAMP：时间戳（不带时区）
CREATE TABLE timestamp_example (
    created_at TIMESTAMP
);
INSERT INTO timestamp_example VALUES ('2024-01-15 14:30:00');
INSERT INTO timestamp_example VALUES (CURRENT_TIMESTAMP);
INSERT INTO timestamp_example VALUES (NOW());

-- TIMESTAMP WITH TIME ZONE：时间戳（带时区）
CREATE TABLE timestamptz_example (
    created_at TIMESTAMP WITH TIME ZONE
);
INSERT INTO timestamptz_example VALUES ('2024-01-15 14:30:00+08');

-- 当前时间戳
SELECT CURRENT_TIMESTAMP;
SELECT NOW();
SELECT CLOCK_TIMESTAMP();  -- 实时时间

-- 推荐使用TIMESTAMP WITH TIME ZONE

日期时间函数

-- 日期时间函数

-- 提取日期部分
SELECT EXTRACT(YEAR FROM CURRENT_DATE);        -- 年
SELECT EXTRACT(MONTH FROM CURRENT_DATE);       -- 月
SELECT EXTRACT(DAY FROM CURRENT_DATE);         -- 日
SELECT EXTRACT(DOW FROM CURRENT_DATE);         -- 星期几（0-6）
SELECT EXTRACT(WEEK FROM CURRENT_DATE);        -- 年中第几周
SELECT EXTRACT(QUARTER FROM CURRENT_DATE);     -- 季度

-- 日期截断
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP); -- 本月第一天
SELECT DATE_TRUNC('year', CURRENT_TIMESTAMP);  -- 本年第一天
SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP);  -- 本周一

-- 日期格式化
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY年MM月DD日');

-- 字符串转日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD');
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- 计算年龄
SELECT AGE(CURRENT_DATE, '1990-01-15');
SELECT AGE('1990-01-15');  -- 相对于当前时间

-- 日期差值
SELECT CURRENT_DATE - '2024-01-01'::DATE;  -- 天数差

布尔类型

-- 布尔类型

-- BOOLEAN：布尔值
CREATE TABLE bool_example (
    is_active BOOLEAN
);

-- 插入布尔值
INSERT INTO bool_example VALUES (TRUE);
INSERT INTO bool_example VALUES (FALSE);
INSERT INTO bool_example VALUES ('t');
INSERT INTO bool_example VALUES ('f');
INSERT INTO bool_example VALUES ('true');
INSERT INTO bool_example VALUES ('false');
INSERT INTO bool_example VALUES ('yes');
INSERT INTO bool_example VALUES ('no');
INSERT INTO bool_example VALUES ('1');
INSERT INTO bool_example VALUES ('0');

-- 布尔运算
SELECT TRUE AND FALSE;   -- FALSE
SELECT TRUE OR FALSE;    -- TRUE
SELECT NOT TRUE;         -- FALSE

-- 查询布尔值
SELECT * FROM bool_example WHERE is_active = TRUE;
SELECT * FROM bool_example WHERE is_active;        -- 等同于 is_active = TRUE
SELECT * FROM bool_example WHERE NOT is_active;    -- 等同于 is_active = FALSE

JSON类型

JSON和JSONB

-- JSON类型

-- JSON：存储JSON文本，每次查询时解析
CREATE TABLE json_example (
    data JSON
);

-- JSONB：存储二进制JSON，解析一次，支持索引（推荐）
CREATE TABLE jsonb_example (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 插入JSON数据
INSERT INTO jsonb_example (data) VALUES (
    '{
        "name": "张三",
        "age": 25,
        "email": "zhangsan@example.com",
        "hobbies": ["阅读", "编程"],
        "address": {
            "city": "北京",
            "street": "朝阳路"
        }
    }'
);

-- JSONB的优势：
-- 1. 解析一次，查询更快
-- 2. 支持索引
-- 3. 支持更多操作符
-- 4. 自动去除空格和键的顺序

JSON操作符

-- JSON操作符

-- 提取值
SELECT data->'name' FROM jsonb_example;           -- 返回JSON值 "张三"
SELECT data->>'name' FROM jsonb_example;          -- 返回文本 张三
SELECT data->'hobbies'->0 FROM jsonb_example;     -- 返回 "阅读"
SELECT data#>'{address,city}' FROM jsonb_example; -- 返回JSON值
SELECT data#>>'{address,city}' FROM jsonb_example;-- 返回文本

-- 包含检查
SELECT * FROM jsonb_example WHERE data @> '{"name": "张三"}';

-- 键存在检查
SELECT * FROM jsonb_example WHERE data ? 'name';

-- 多键存在检查
SELECT * FROM jsonb_example WHERE data ?| ARRAY['name', 'age'];

-- 所有键存在检查
SELECT * FROM jsonb_example WHERE data ?& ARRAY['name', 'age'];

JSON函数

-- JSON函数

-- 创建JSON
SELECT to_json('Hello'::TEXT);                    -- "Hello"
SELECT to_jsonb(ROW('张三', 25));                  -- {"f1": "张三", "f2": 25}
SELECT json_build_object('name', '张三', 'age', 25);
SELECT jsonb_build_object('name', '张三', 'age', 25);

-- 数组转JSON
SELECT json_agg(name) FROM (VALUES ('张三'), ('李四')) AS t(name);
-- ["张三", "李四"]

-- 对象数组
SELECT jsonb_object_agg(key, value) 
FROM (VALUES ('name', '张三'), ('age', '25')) AS t(key, value);
-- {"name": "张三", "age": "25"}

-- 提取键值
SELECT jsonb_object_keys(data) FROM jsonb_example;  -- 获取所有键

-- 展开JSON
SELECT * FROM jsonb_each('{"a": 1, "b": 2}'::JSONB);  -- 展开为行

-- 设置值
SELECT jsonb_set('{"a": 1}'::JSONB, '{a}', '2'::JSONB);
-- {"a": 2}

-- 删除键
SELECT data - 'name' FROM jsonb_example;

-- 合并JSON
SELECT '{"a": 1}'::JSONB || '{"b": 2}'::JSONB;
-- {"a": 1, "b": 2}

数组类型

数组定义

-- PostgreSQL支持数组类型

-- 定义数组列
CREATE TABLE array_example (
    id SERIAL PRIMARY KEY,
    numbers INTEGER[],              -- 整数数组
    names TEXT[],                   -- 文本数组
    prices NUMERIC(10,2)[]          -- 定点数数组
);

-- 插入数组数据
INSERT INTO array_example (numbers, names, prices) 
VALUES (
    ARRAY[1, 2, 3, 4, 5],
    ARRAY['张三', '李四', '王五'],
    ARRAY[10.5, 20.3, 30.1]
);

-- 使用字符串语法插入
INSERT INTO array_example (numbers, names) 
VALUES ('{1,2,3}', '{"张三","李四"}');

-- 使用ARRAY构造器
INSERT INTO array_example (numbers) 
VALUES (ARRAY[1, 2, 3]);

数组操作

-- 数组操作

-- 访问数组元素（索引从1开始）
SELECT numbers[1] FROM array_example;        -- 第一个元素
SELECT numbers[3] FROM array_example;        -- 第三个元素

-- 数组切片
SELECT numbers[1:3] FROM array_example;      -- 第1到第3个元素

-- 数组长度
SELECT array_length(numbers, 1) FROM array_example;  -- 数组长度

-- 查询包含某元素的记录
SELECT * FROM array_example WHERE 3 = ANY(numbers);
SELECT * FROM array_example WHERE numbers @> ARRAY[3];

-- 查询包含所有元素的记录
SELECT * FROM array_example WHERE numbers @> ARRAY[1, 2];

-- 数组连接
SELECT ARRAY[1, 2] || ARRAY[3, 4];           -- {1,2,3,4}
SELECT ARRAY[1, 2] || 3;                     -- {1,2,3}

-- 追加元素
SELECT array_append(numbers, 6) FROM array_example;

-- 删除元素
SELECT array_remove(numbers, 3) FROM array_example;

-- 数组转字符串
SELECT array_to_string(numbers, ',') FROM array_example;

-- 字符串转数组
SELECT string_to_array('a,b,c', ',');

枚举类型

-- 枚举类型

-- 创建枚举类型
CREATE TYPE week_day AS ENUM (
    'Monday', 'Tuesday', 'Wednesday', 
    'Thursday', 'Friday', 'Saturday', 'Sunday'
);

-- 使用枚举类型
CREATE TABLE schedule (
    id SERIAL PRIMARY KEY,
    day week_day,
    task TEXT
);

INSERT INTO schedule (day, task) VALUES ('Monday', '开会');
INSERT INTO schedule (day, task) VALUES ('Friday', '汇报');

-- 查询枚举值
SELECT * FROM schedule WHERE day = 'Monday';

-- 查看枚举类型的所有值
SELECT enum_range(NULL::week_day);

-- 添加枚举值
ALTER TYPE week_day ADD VALUE 'Holiday' AFTER 'Sunday';

-- 注意：枚举值不能删除或修改

本章小结

本章介绍了PostgreSQL丰富的数据类型：

1. 数值类型：整数、序列、浮点、定点类型
2. 字符串类型：CHAR、VARCHAR、TEXT
3. 日期时间类型：DATE、TIME、TIMESTAMP
4. 布尔类型：BOOLEAN
5. JSON类型：JSON、JSONB及其操作
6. 数组类型：数组的定义和操作
7. 枚举类型：自定义枚举类型

下一章，我们将学习表操作，了解如何创建和管理数据表。