PostgreSQL 教程

PostgreSQL 插入数据

向数据表中插入数据是数据库管理中最基础的操作之一，它能让你用实际信息填充你的数据表。在 PostgreSQL 中，INSERT 语句是完成这项任务的主要工具。

本章将全面讲解在 PostgreSQL 中插入数据时的语法格式和各种实用选项。

1. 基础 INSERT 语句

INSERT 语句最简单的形式就是向表中插入一整行新数据。你需要指定表名，并为每一列提供对应的值。

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  • table_name: 你想要插入数据的目标数据表名称。
  • (column1, column2, ...): （可选）列名列表。用于指定你想插入数据的具体列及其顺序。如果省略此列表，你必须按照创建表时定义的列顺序，为表中的所有列提供值。
  • VALUES (value1, value2, ...): 要插入到对应列中的具体数值列表。

示例：
假设我们按照如下方式创建了一个名为 employees（员工）的表：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

要向 employees 表中插入一名新员工，我们可以使用以下 INSERT 语句：

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('约翰', '杜', 'john.doe@example.com', '2023-01-15', 60000.00);

在这个例子中，我们明确指定了要插入数据的列。由于 employee_id 列是 SERIAL 类型（会自动生成唯一的自增数字），所以我们不需要为它提供值，PostgreSQL 会在后台自动处理。

1.1 省略列名列表

如果你想按照表定义的顺序为所有列插入值，可以省略列名列表：

INSERT INTO employees
VALUES (DEFAULT, '简', '史密斯', 'jane.smith@example.com', '2023-02-20', 65000.00);

在这里，我们对 employee_id 列使用了 DEFAULT 关键字，指示 PostgreSQL 去生成下一个序列值。极其重要的一点是：VALUES 子句中值的顺序，必须与表中定义的列顺序完全一致。

2. 插入指定列的数据

你可以只向表中的一部分（子集）列插入数据。任何没有被明确指定的列，要么会被赋予默认值（如果定义了的话），要么会被设置为 NULL（如果没有默认值且该列允许为空）。

示例：
让我们插入一名新员工，这次只提供名字、姓氏和邮箱：

INSERT INTO employees (first_name, last_name, email)
VALUES ('迈克', '布朗', 'mike.brown@example.com');

在这种情况下，hire_date (入职日期) 和 salary (薪水) 列将被设置为 NULL（因为在建表时没有为它们设定默认值），而 employee_id 依然会自动生成。

3. 一次性插入多行数据

PostgreSQL 允许你使用一条单独的 INSERT 语句同时插入多行数据。这能显著提升执行效率，特别是在处理大型数据集时。

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1_1, value1_2, value1_3, ...),
       (value2_1, value2_2, value2_3, ...),
       (value3_1, value3_2, value3_3, ...);

示例：
让我们一次性插入三名新员工：

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('爱丽丝', '约翰逊', 'alice.johnson@example.com', '2023-03-01', 70000.00),
       ('鲍勃', '威廉姆斯', 'bob.williams@example.com', '2023-03-15', 72000.00),
       ('查理', '戴维斯', 'charlie.davis@example.com', '2023-04-01', 75000.00);

这比执行三条独立的 INSERT 语句要快得多。

4. 使用 SELECT 语句插入数据

你可以通过 SELECT 语句从另一个表（甚至是同一个表）中提取数据，并将其直接插入到目标表中。这对于复制数据或在插入前转换数据非常有用。

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column_a, column_b, column_c
FROM another_table
WHERE condition;
  • table_name: 你要插入数据的目标表。
  • (column1, ...): 目标表中将要被填充的列。
  • SELECT ... FROM ... WHERE: 该语句从 another_table (另一个表) 中检索数据。选出的列数量和数据类型必须与 INSERT 语句中指定的列数量和类型相匹配。WHERE 子句是可选的，用于过滤要插入的数据。

示例：
假设我们有一个名为 old_employees 的表，结构类似，但包含了过时的信息。我们想把 2020 年之前入职的员工迁移到现有的 employees 表中：

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
SELECT first_name, last_name, email, hire_date, salary
FROM old_employees
WHERE hire_date < '2020-01-01';

5. 处理 SERIAL 列与默认值

正如前面示例所示，SERIAL 列会自动生成唯一的自增整数序列。在插入数据时，处理这类列有三种方式：

-- 方式 1：直接在列列表中省略它
INSERT INTO employees (first_name, last_name)
VALUES ('伊芙', '泰勒');

-- 方式 2：使用 DEFAULT 关键字
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (DEFAULT, '弗兰克', '米勒');

如果某列定义了默认值，而你在插入时不想提供特定值，同样可以使用 DEFAULT 关键字：

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) DEFAULT 0.00
);

INSERT INTO products (product_name)
VALUES ('超级小工具');
-- 或者明确写出：VALUES ('超级小工具', DEFAULT);

在这个例子中，新插入产品的 price (价格) 将被自动设置为默认值 0.00。

6. 数据类型注意事项

插入数据时，确保提供的值的数据类型与表中对应列的数据类型相匹配是极其关键的。虽然 PostgreSQL 会尝试进行隐式类型转换，但为了避免意外行为或报错，最好采用显式的、规范的写法。

  • 字符串 (Strings): 必须使用单引号将字符串括起来（例如：'约翰'）。
  • 数字 (Numbers): 直接书写数字字面量，不要加引号（例如：60000.00）。
  • 日期 (Dates): 使用标准的日期格式字符串（例如：'2023-01-15'）。对于复杂的日期格式，你可能需要使用 TO_DATE() 函数。
  • 布尔值 (Booleans): 直接使用 TRUE 或 FALSE（不需要引号）。

显式日期转换示例：
如果你的日期格式是 MM/DD/YYYY（月/日/年），你可以使用 TO_DATE() 函数进行转换：

INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('格蕾丝', '安德森', TO_DATE('05/20/2023', 'MM/DD/YYYY'));

7. 常见错误与排查

  • 数据类型不匹配 (Data type mismatch): 确保插入值的类型与对应列的类型一致。
  • 值的数量不对 (Incorrect number of values): VALUES 子句中值的数量必须与列名列表中的列数完全匹配（如果省略了列名列表，则必须与表中的总列数匹配）。
  • 违反约束 (Violation of constraints): 如果列上有 NOT NULL、UNIQUE 或 FOREIGN KEY (外键) 等约束，务必确保插入的数据满足这些条件。例如，向带有 UNIQUE 约束的列插入重复值会导致报错。
  • 语法错误 (Syntax errors): 仔细检查 SQL 语句中是否有拼写错误、漏掉括号或逗号。