PostgreSQL - INSERT

PostgreSQL - INSERT 语句

PostgreSQL 的 INSERT INTO 语句用于向表中添加一行或多行新数据。您可以使用 VALUES 子句插入单行，也可以一次性插入多行。

语法

插入单行的基本语法是：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

要在单个语句中插入多行，您可以在 VALUES 子句中提供多个元组：

INSERT INTO table_name (column1, column2, ...)
VALUES
  (value1_row1, value2_row1, ...),
  (value1_row2, value2_row2, ...),
  (value1_row3, value2_row3, ...);

如果您按照定义的顺序为所有列提供值，则可以省略列名列表。然而，明确列出所有列是一种最佳实践，可以提高清晰度，并避免在表结构更改时出现错误。

示例：现代表和插入操作

让我们创建一个更现代的 employees 表。我们将使用 BIGSERIAL 作为自增主键，VARCHAR 用于可变长度字符串，NUMERIC 用于精确的薪资数字，以及 TIMESTAMPTZ 用于带有时区信息的日期时间戳。

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    age INT NOT NULL CHECK (age > 18),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date TIMESTAMPTZ DEFAULT NOW()
);

现在，让我们向 employees 表中插入几行数据：

-- 单行插入，明确列出所有列
INSERT INTO employees (full_name, age, department, salary)
VALUES ('Alice Wonderland', 32, 'Engineering', 95000.00);

-- 多行插入
INSERT INTO employees (full_name, age, department, salary)
VALUES
    ('Bob Builder', 25, 'Engineering', 80000.00),
    ('Charlie Chaplin', 27, 'Marketing', 85000.00);

-- 插入部门为 NULL 的行
INSERT INTO employees (full_name, age, department, salary)
VALUES ('Diana Prince', 29, NULL, 110000.00);

插入后获取数据：RETURNING 子句

一个非常常见的需求是在插入行后获取该行的 ID 或其他值。您可以使用 RETURNING 子句，而不是运行单独的 SELECT 查询。这种方法既高效又可靠。

INSERT INTO employees (full_name, age, department, salary)
VALUES ('Eve Adamson', 28, 'Sales', 72000.00)
RETURNING id, hire_date;

此语句将返回为 ‘Eve Adamson’ 新创建记录的 id 和 hire_date。

输出消息

成功插入后，PostgreSQL 会返回类似 INSERT 0 1 的消息，其中 0 是一个遗留的 OID 值（默认情况下不再使用），1 是插入的行数。

最佳实践：使用 ON CONFLICT 进行“插入或更新”（Upsert）

如果您想插入一行新数据，但如果具有特定唯一键的行已经存在，您希望改为更新它怎么办？这被称为“插入或更新”（upsert）操作。PostgreSQL 提供了使用 ON CONFLICT 的简洁解决方案。

首先，让我们在 full_name 上添加一个唯一约束。

ALTER TABLE employees ADD CONSTRAINT unique_full_name UNIQUE (full_name);

现在，我们可以使用 ON CONFLICT 来处理重复项。DO NOTHING 将静默忽略插入操作，而 DO UPDATE 将修改现有行。

-- 这将失败，因为 'Bob Builder' 已经存在
-- INSERT INTO employees (full_name, age, department) VALUES ('Bob Builder', 26, 'Maintenance');

-- 如果 'Bob Builder' 已经存在，这将更新他的薪水
INSERT INTO employees (full_name, age, department, salary)
VALUES ('Bob Builder', 26, 'Maintenance', 82000.00)
ON CONFLICT (full_name)
DO UPDATE SET
    department = EXCLUDED.department,
    salary = EXCLUDED.salary,
    age = EXCLUDED.age;