PostgreSQL 使用文档

Gin2026-07-084 分钟

1. 基础查询

SQL

-- 查询 PostgreSQL 版本
SELECT version();

2. PostgreSQL 建表示例

SQL

CREATE TABLE girlfriend_configs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    nickname VARCHAR(50),
    personality TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SERIAL 是 PostgreSQL 常见的自增写法。新版本中也可以使用标准 SQL 的 identity 写法：

SQL

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 数据类型和默认值补充

SQL

SERIAL              -- 自增整数，常用于主键
BIGSERIAL           -- 自增大整数
INTEGER             -- 整数
TEXT                -- 大文本
TIMESTAMP           -- 日期和时间
DATE                -- 日期
UUID                -- UUID
CURRENT_TIMESTAMP   -- 当前时间
now()               -- 当前事务开始时间
gen_random_uuid()   -- 生成随机 UUID

使用 gen_random_uuid() 前，通常需要启用扩展：

SQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

4. CRUD 语法差异

PostgreSQL 支持 RETURNING，可以在插入、更新、删除后返回受影响的数据。

SQL

INSERT INTO girlfriend_configs (user_id, nickname, personality)
VALUES (1, '小爱', '温柔体贴，喜欢读书和音乐')
RETURNING *;
 
UPDATE girlfriend_configs
SET nickname = '小爱同学'
WHERE user_id = 1
RETURNING *;
 
DELETE FROM girlfriend_configs
WHERE user_id = 1
RETURNING *;

大小写不敏感的模糊匹配可以使用 ILIKE：

SQL

SELECT *
FROM girlfriend_configs
WHERE nickname ILIKE '%小%';

5. 索引补充

PostgreSQL CREATE INDEX 默认创建 B-tree 索引，也支持更多索引类型。

SQL

-- 默认 B-tree 索引
CREATE INDEX idx_conversation_user_id
ON conversation_history (user_id, created_at DESC);
 
-- 指定索引类型示例
CREATE INDEX idx_docs_content_gin
ON documents USING GIN (content);

多列索引仍然需要关注列顺序。通常把最常用的查询条件放在前面，例如 (user_id, created_at) 更适合：

SQL

WHERE user_id = 123;
 
WHERE user_id = 123
  AND created_at >= '2024-11-01';

6. 约束操作差异

PostgreSQL 删除外键或普通约束时使用约束名：

SQL

ALTER TABLE employees DROP CONSTRAINT fk_employees_department;

如果忘记约束名，可以在 psql 中使用：

SQL

\d employees

7. pgvector

向量可以理解为将文本、图片等内容转化后的数字坐标，常用于相似度检索。

常见向量维度：

开源模型，如 BERT / HuggingFace：通常是 768。
OpenAI text-embedding-3-small：通常是 1536。

启用 vector 扩展：

SQL

CREATE EXTENSION IF NOT EXISTS vector;