PostgreSQL 数据类型

数值类型

整数

类型名	存储大小	范围
SMALLINT	2 字节	-32768 ~ 32767
INTEGER	4 字节	-2147483648 ~ 2147483647
BIGINT	8 字节	-9223372036854775808 ~ 9223372036854775807
SERIAL	4 字节	1 ~ 2147483647（自增）
BIGSERIAL	8 字节	1 ~ 9223372036854775807（自增）

浮点数

类型名	说明
REAL	4 字节，单精度
DOUBLE PRECISION	8 字节，双精度
DECIMAL(p,s)	精确小数，p=总位数，s=小数位
NUMERIC(p,s)	同 DECIMAL

CREATE TABLE test_numbers (
    id SERIAL PRIMARY KEY,
    age SMALLINT,
    salary DECIMAL(10,2),
    rate DOUBLE PRECISION
);

字符串类型

类型名	说明
CHAR(n)	固定长度，不足补空格
VARCHAR(n)	可变长度，最大 n
TEXT	可变长度，无限

CREATE TABLE test_strings (
    code CHAR(6),        -- 'ABC  '
    name VARCHAR(100),  -- '张三'
    content TEXT        -- 任意长度文本
);

日期时间类型

类型名	说明
DATE	日期（年-月-日）
TIME	时间（时:分:秒）
TIMESTAMP	日期时间
TIMESTAMPTZ	带时区的日期时间
INTERVAL	时间间隔

CREATE TABLE test_dates (
    created_date DATE,
    created_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    duration INTERVAL '30 days'
);

-- 示例
INSERT INTO test_dates (created_date, duration) 
VALUES ('2024-01-15', '2 hours 30 minutes');

SELECT created_at + interval '1 week';

布尔类型

CREATE TABLE test_boolean (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT true,
    is_deleted BOOLEAN DEFAULT false
);

INSERT INTO test_boolean (is_active) VALUES (TRUE), (FALSE), ('yes'), ('no'), ('1'), ('0');

数组类型

CREATE TABLE test_arrays (
    id SERIAL PRIMARY KEY,
    tags TEXT[],
    scores INTEGER[]
);

INSERT INTO test_arrays (tags, scores) 
VALUES 
    ('{"Python", "Go", "Rust"}', '{90, 85, 88}'),
    (ARRAY['JavaScript', 'TypeScript'], ARRAY[95, 92]);

-- 访问数组元素（从1开始）
SELECT tags[1] FROM test_arrays;

-- 包含检查
SELECT * FROM test_arrays WHERE 'Python' = ANY(tags);

JSON 类型

CREATE TABLE test_json (
    id SERIAL PRIMARY KEY,
    data JSON,
    datab JSONB  -- JSONB 是二进制格式，查询更快
);

INSERT INTO test_json (data, datab) VALUES 
    ('{"name": "张三", "age": 30}', '{"name": "李四", "age": 25}'),
    ('[1, 2, 3]', '[4, 5, 6]');

-- 提取 JSON 字段
SELECT data->>'name' FROM test_json;  -- 返回文本
SELECT data->'name' FROM test_json;  -- 返回 JSON

-- JSONB 包含检查
SELECT * FROM test_json WHERE datab @> '{"name": "李四"}';

UUID 类型

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE test_uuid (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100)
);

INSERT INTO test_uuid (name) VALUES ('测试数据');

网络地址类型

CREATE TABLE test_network (
    id SERIAL PRIMARY KEY,
    ip INET,
    mac MACADDR
);

INSERT INTO test_network (ip, mac) VALUES 
    ('192.168.1.1', '08:00:27:00:00:01'),
    ('10.0.0.1/24', NULL);

范围类型

CREATE TABLE test_ranges (
    id SERIAL PRIMARY KEY,
    age_range INT4RANGE,
    time_range TSRANGE
);

INSERT INTO test_ranges (age_range) VALUES 
    ('[18, 30)'),    -- 18 <= age < 30
    ('[0, 18)');     -- 0 <= age < 18

-- 包含检查
SELECT * FROM test_ranges WHERE age_range @> 25;

枚举类型

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE test_enum (
    id SERIAL PRIMARY KEY,
    current_mood mood
);

INSERT INTO test_enum (current_mood) VALUES ('happy'), ('sad');

类型转换

-- 显式转换
SELECT '123'::INTEGER;
SELECT CAST('2024-01-01' AS DATE);

-- 使用函数转换
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_DATE('20240101', 'YYYYMMDD');
SELECT NOW()::DATE;