PostgreSQL 数据操作与事务详解

阿麦

发布时间：2026-10-16 | 3人浏览过

原创

掌握 PostgreSQL 核心数据操作是数据库开发的基础。本文梳理了 INSERT、DELETE、UPDATE 语句的标准写法与高级技巧，如隐式默认值处理和多表复制，并深入解析事务（Transaction）的 ACID 特性及提交回滚机制，帮助开发者确保数据操作的准确与安全。

INSERT：数据插入操作

INSERT 语句用于向表中添加新数据行。执行前需确保表已创建，例如创建一个包含产品信息的 ProductIns 表：

CREATE TABLE ProductIns
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER      DEFAULT 0,
 purchase_price  INTEGER      ,
 regist_date     DATE         ,
 PRIMARY KEY (product_id));

第1步：基础单行插入

指定列名进行插入时，列清单与值清单的数量必须一致。

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES ('0001', 'T恤', '衣服', 1000, 500, '2222-09-20');

若按表定义顺序插入所有列的值，可省略列清单：

INSERT INTO ProductIns VALUES ('0005', '铁锅', '厨房用具', 6800, 5000, '2222-01-15');

第2步：处理 NULL 与默认值

对于非 NOT NULL 约束的列，可直接插入 NULL：

INSERT INTO ProductIns VALUES ('0006', '勺子', '厨房用具', 500, NULL, '2222-09-20');

插入默认值有两种方式：

显式设置：使用关键字 DEFAULT。由于 sale_price 定义了默认值 0，以下语句将该列设为默认值：

INSERT INTO ProductIns VALUES ('0007', '筷子', '厨房用具', DEFAULT, 790, '2222-04-28');

隐式设置：在列清单中省略带有默认值的列。未指定的列若允许为 NULL 则自动填入 NULL，若设置了 NOT NULL 约束则会报错：

INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date)
VALUES ('0007', '筷子', '厨房用具', 790, '2222-04-28');

第3步：从其他表复制数据

可以通过 SELECT 语句将查询结果插入到新表中，支持 WHERE、GROUP BY 等子句过滤或聚合。

首先创建目标表结构：

CREATE TABLE ProductCopy (
product_id      CHAR(4) NOT NULL,
product_name    VARCHAR(100) NOT NULL,
product_type    VARCHAR(32) NOT NULL,
sale_price      INTEGER,
purchase_price  INTEGER,
regist_date     DATE,
PRIMARY KEY (product_id));

将 Product 表数据完整复制到 ProductCopy：

INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;

结合聚合函数，可按商品类型统计总价：

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type;

DELETE：数据删除操作

DELETE 语句作用于行级数据，无法单独删除某列的值。

第1步：删除全部数据

清除表中所有记录，但保留表结构：

DELETE FROM Product;

第2步：条件删除

配合 WHERE 子句仅删除符合条件的行：

DELETE FROM Product
WHERE sale_price >= 2000;

UPDATE：数据更新操作

UPDATE 用于修改现有记录的数据。

第1步：全表更新与条件更新

若不指定 WHERE 条件，将更新指定列的所有行。例如将所有商品的登记日期统一修改：

UPDATE Product
SET regist_date = '2222-02-02';

通过 WHERE 限定范围，例如将“厨房用具”类商品售价提高十倍：

UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';

第2步：复杂更新场景

更新为 NULL：需确保该列无 NOT NULL 或主键约束。

UPDATE Product
SET regist_date = NULL
WHERE product_id = '0008';

同时更新多列：推荐写法是使用逗号分隔各列赋值，兼容性最好。

UPDATE Product
SET sale_price = sale_price * 10,
    purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';

部分 DBMS 支持元组形式的赋值（列表形式），但在 PostgreSQL 中通常推荐逗号分隔法以保证通用性。

事务：保障数据一致性

事务（Transaction）是将一组 DML 操作视为一个不可分割的逻辑单元。当需要执行关联的多步操作时（如转账），事务能确保要么全部成功，要么全部失败。

第1步：事务的基本流程

开始事务：BEGIN TRANSACTION;
执行 DML：执行一系列插入、更新或删除操作。
结束事务：选择 COMMIT（提交）或 ROLLBACK（回滚）。

COMMIT：永久保存变更，之后无法恢复。 ROLLBACK：撤销自事务开始以来的所有更改，恢复到初始状态。

以下示例展示了如何将 T恤价格增加 1000，裤子价格减少 1000，确保金额平衡：

BEGIN TRANSACTION;
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤';
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '裤子';
COMMIT;