PostgreSQL 库表

数据库

CREATE DATABASE app_db
  WITH ENCODING 'UTF8'
  LC_COLLATE 'C'
  LC_CTYPE 'C'
  TEMPLATE template0;

DROP DATABASE IF EXISTS app_db;

删除数据库前不能有其他会话连接该数据库。

Schema

PostgreSQL 的 schema 是数据库内部的命名空间。默认使用 public。

CREATE SCHEMA app;
SET search_path TO app, public;
DROP SCHEMA IF EXISTS app CASCADE;

常用数据类型

类型	说明
TEXT	文本，长度不限
VARCHAR(n)	限长文本
INTEGER	普通整数
BIGINT	大整数
NUMERIC(p, s)	精确小数
DOUBLE PRECISION	双精度浮点数
BOOLEAN	布尔值
DATE	日期，不包含时间
TIMESTAMP	不带时区的日期时间
TIMESTAMPTZ	带时区语义的时间戳
JSONB	二进制 JSON，支持索引和查询
UUID	UUID
BYTEA	二进制数据
TEXT[]	文本数组
INTEGER[]	整数数组

创建表

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  age INTEGER DEFAULT 0 CHECK (age >= 0),
  status SMALLINT NOT NULL DEFAULT 1,
  profile JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

查看表

\dt
\d users

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

修改表

ALTER TABLE users ADD COLUMN avatar_url TEXT;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);
ALTER TABLE users RENAME COLUMN avatar_url TO avatar;
ALTER TABLE users DROP COLUMN avatar;
ALTER TABLE users RENAME TO app_users;

删除表

DROP TABLE IF EXISTS users;
TRUNCATE TABLE users RESTART IDENTITY;

TRUNCATE 会清空表数据，RESTART IDENTITY 会重置自增序列。

返回写入结果

INSERT INTO users (name, email, age)
VALUES ('Tom', 'tom@example.com', 18)
RETURNING id;

UPDATE users
SET age = 19
WHERE id = 1
RETURNING id, age;

DELETE FROM users
WHERE id = 1
RETURNING id;

索引

CREATE INDEX idx_users_status_created_at ON users (status, created_at);
CREATE UNIQUE INDEX uk_users_email ON users (email);
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);
DROP INDEX idx_users_status_created_at;

事务

BEGIN;

UPDATE users SET age = age + 1 WHERE id = 1;
INSERT INTO orders (user_id, amount) VALUES (1, 99.00);

COMMIT;
-- ROLLBACK;

默认隔离级别是 READ COMMITTED。

执行计划

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'tom@example.com';

EXPLAIN ANALYZE 会实际执行 SQL。生产环境排查写操作时要谨慎。

匹配和连接

LIKE 区分大小写，ILIKE 不区分大小写。PostgreSQL 支持 FULL OUTER JOIN。

SELECT *
FROM users
WHERE email ILIKE '%@example.com';

JSONB

INSERT INTO users (name, email, profile)
VALUES ('Json User', 'json@example.com', '{"city": "Shanghai"}');

SELECT *
FROM users
WHERE profile ->> 'city' = 'Shanghai';