PostgreSQL 增删改查

插入

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

INSERT INTO users (name, email, age)
VALUES
  ('Alice', 'alice@example.com', 20),
  ('Bob', 'bob@example.com', 22);

更新

UPDATE users
SET
  age = 19,
  updated_at = now()
WHERE id = 1
RETURNING id, age;

删除

DELETE FROM users
WHERE id = 1
RETURNING id;

查询

SELECT id, name, email
FROM users
WHERE status = 1
ORDER BY id DESC
LIMIT 10 OFFSET 0;

条件、排序和分页

SELECT *
FROM users
WHERE age BETWEEN 18 AND 30
  AND email ILIKE '%@example.com'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

LIKE 区分大小写，ILIKE 不区分大小写。

关联查询

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  amount NUMERIC(10, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id
WHERE u.id = 1;

PostgreSQL 支持 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN。

事务

BEGIN;

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

COMMIT;
-- ROLLBACK;

PostgreSQL 默认隔离级别是 READ COMMITTED。

索引

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;

执行计划

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

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

JSONB

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

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