PostgreSQL插入数据的核心是INSERT INTO命令，支持插入单行、多行、指定列、从查询结果插入，并可通过RETURNING获取插入后生成的值；结合事务、批量操作、预处理语句和ON CONFLICT实现高效安全的UPSERT操作。

在PostgreSQL中插入数据，核心就是使用INSERT INTO SQL命令，它可以将新记录添加到指定的表中。这听起来很简单，但实际操作中，根据具体需求和场景，这个命令能玩出不少花样，远不是字面上那么直接。

解决方案

向PostgreSQL数据库中插入数据，最基本的操作就是使用INSERT INTO语句。它允许你将一行或多行数据添加到指定的表里。

1. 插入完整行数据： 如果你想为表的所有列插入数据，并且知道所有列的顺序，可以省略列名列表。但这通常不推荐，因为表的结构可能会变动。

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);

例如，有一个名为 products 的表，包含 id, name, price 三列：

INSERT INTO products VALUES (1, '笔记本电脑', 1200.00);

2. 插入指定列数据： 这是更常用也更健壮的方式，明确指出要插入哪些列以及对应的值。

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);

例如，只插入产品的名称和价格，让 id 列使用默认值（如果它被定义为自增或有默认值）：

INSERT INTO products (name, price) VALUES ('机械键盘', 150.00);

3. 插入多行数据： 你可以通过在 VALUES 子句中提供多个值列表来一次性插入多行数据，用逗号分隔。

INSERT INTO 表名 (列1, 列2) VALUES     (值A1, 值A2),     (值B1, 值B2),     (值C1, 值C2);

例如：

INSERT INTO products (name, price) VALUES     ('无线鼠标', 35.00),     ('显示器', 300.00),     ('摄像头', 60.00);

4. 从另一个表插入数据： 如果你想将一个查询结果插入到另一个表中，可以使用 INSERT INTO … SELECT 语句。

INSERT INTO 目标表 (列1, 列2, ...) SELECT 源列1, 源列2, ... FROM 源表 WHERE 条件;

例如，将 old_products 表中价格低于100的产品转移到 products 表中：

INSERT INTO products (name, price) SELECT name, price FROM old_products WHERE price < 100.00;

5. 获取插入后返回的值： 在某些场景下，比如插入一个新记录后，你需要获取它自动生成的ID（比如自增ID）。PostgreSQL的 RETURNING 子句就能派上用场。

INSERT INTO 表名 (列1) VALUES (值1) RETURNING id_列名;

例如，插入一个用户并获取其生成的 id：

INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com') RETURNING id, created_at;

这些就是PostgreSQL插入数据的基本操作。理解这些，你就能应对绝大部分的数据插入需求了。

PostgreSQL数据插入操作的最佳实践是什么？

说实话，插入数据看似简单，但要在生产环境中做得既高效又安全，还是有些门道的。在我看来，最佳实践并不仅仅是会写INSERT语句，更重要的是考虑性能、并发和数据完整性。

首先，事务管理是基石。如果你有一系列相关的插入操作，务必将它们包裹在一个事务中。这意味着要么所有操作都成功，数据提交；要么任何一个操作失败，所有操作都回滚。这能有效避免数据不一致。比如：

BEGIN; INSERT INTO orders (user_id, product_id, quantity) VALUES (101, 201, 1); INSERT INTO order_items (order_id, product_id, price) VALUES (LASTVAL(), 201, 150.00); -- 假设LASTVAL()能获取上一个自增ID COMMIT;

接着，对于大量数据插入，避免一条一条地执行INSERT语句。网络延迟和数据库的解析开销会显著降低效率。

• 多行VALUES语法：前面提到的 INSERT INTO … VALUES (…), (…); 就能有效减少语句执行次数。
• COPY命令：这是PostgreSQL处理大批量数据导入的“核武器”。它直接从文件（或标准输入）读取数据并写入表，效率远超INSERT。如果你的数据源是CSV、TSV等文件，COPY是首选。

-- 从文件导入 COPY products FROM '/path/to/your/products.csv' WITH (FORMAT CSV, HEADER true);  -- 从标准输入导入（例如通过命令行管道） psql -c "COPY products FROM STDIN WITH (FORMAT CSV)" < products.csv

另外，预处理语句（Prepared Statements）也是一个好习惯。当你需要重复执行相似的INSERT语句，但只有参数值不同时，预处理语句能减少数据库的解析和规划时间。它还能有效防止SQL注入攻击。在应用开发中，ORM框架通常会替你处理好这一点。

最后，合理设计表结构和索引也非常关键。插入数据时，如果表上有大量索引，每次插入都需要更新这些索引，这会增加开销。虽然你不能完全没有索引，但可以审视是否所有索引都是必需的。同时，NOT NULL、UNIQUE、PRIMARY KEY等约束能在数据库层面保证数据质量，减少应用层的复杂性。

PostgreSQL插入数据时如何处理默认值和自增ID？

处理默认值和自增ID是数据插入时非常常见的需求，PostgreSQL在这方面提供了非常灵活和强大的机制。

1. 默认值（DEFAULT Values）： 当你在创建表时为某一列指定了DEFAULT值，那么在插入数据时，如果你不为该列提供显式的值，或者使用DEFAULT关键字，PostgreSQL就会自动填充这个默认值。

CREATE TABLE users (     id SERIAL PRIMARY KEY,     username VARCHAR(50) NOT NULL,     status VARCHAR(10) DEFAULT 'active', -- 默认值为 'active'     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默认值为当前时间戳 );

插入数据时，你可以：

• 省略该列：

  INSERT INTO users (username) VALUES ('Alice'); -- 此时 status 会是 'active'，created_at 会是当前时间

• 使用DEFAULT关键字：

  INSERT INTO users (username, status, created_at) VALUES ('Bob', DEFAULT, DEFAULT); -- 效果同上

• 显式提供值：

  INSERT INTO users (username, status) VALUES ('Charlie', 'inactive'); -- 此时 status 会是 'inactive'，而不是默认值

2. 自增ID（Auto-incrementing IDs）： PostgreSQL提供了几种方式来实现自增ID，最常见的是SERIAL和BIGSERIAL伪类型，以及SQL标准中引入的GENERATED AS IDENTITY。

• SERIAL / BIGSERIAL： 这是PostgreSQL特有的，它们实际上是创建了一个序列（sequence）对象，并将其绑定到列上，同时为该列设置了NOT NULL约束和默认值，使其从序列中获取下一个值。SERIAL用于较小的整数，BIGSERIAL用于更大的整数。

  CREATE TABLE products (     product_id SERIAL PRIMARY KEY, -- 会自动创建 sequence，并设为默认值     name VARCHAR(100) NOT NULL,     price NUMERIC(10, 2) );

  插入数据时，你通常会省略product_id列，让数据库自动生成：

  INSERT INTO products (name, price) VALUES ('智能手表', 299.99); -- product_id 会自动生成

  或者，如果你真的想显式地插入一个ID（通常不建议，除非有特殊迁移场景），你也可以：

  

  小K直播姬

  全球首款AI视频动捕虚拟直播产品

  34

  查看详情

  INSERT INTO products (product_id, name, price) VALUES (1001, '定制产品A', 500.00);

  但这样做可能会与序列的当前值冲突，所以要小心。

• GENERATED AS IDENTITY： 这是SQL:2003标准引入的，是更现代、更符合标准的方式。它提供了更细粒度的控制。

  CREATE TABLE orders (     order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 默认生成，允许手动插入     -- order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 总是生成，不允许手动插入     order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     customer_id INT NOT NULL );

  对于GENERATED BY DEFAULT AS IDENTITY：

  • 省略列：

    INSERT INTO orders (customer_id) VALUES (123); -- order_id 会自动生成

  • 显式插入：

    INSERT INTO orders (order_id, customer_id) VALUES (5000, 456); -- 允许你插入自定义的 order_id

  对于GENERATED ALWAYS AS IDENTITY：

  • 你不能显式插入值，除非使用OVERRIDING SYSTEM VALUE。

    INSERT INTO orders (customer_id) VALUES (789); -- 正常 -- INSERT INTO orders (order_id, customer_id) VALUES (6000, 999); -- 报错 INSERT INTO orders (order_id, customer_id) OVERRIDING SYSTEM VALUE VALUES (6000, 999); -- 强制插入

了解这些机制，能让你更灵活地控制数据的生成和填充，确保数据插入的正确性和便捷性。

PostgreSQL的INSERT ON CONFLICT语句怎么用？

在处理数据插入时，我们经常会遇到一个问题：如果我要插入的数据，在表中已经存在了（根据某个唯一约束判断），我该怎么办？是报错？是忽略？还是更新已有的记录？PostgreSQL的INSERT … ON CONFLICT语句，也常被称为“UPSERT”（Update or Insert），就是为了优雅地解决这类问题而设计的。它是在PostgreSQL 9.5版本引入的，极大地方便了开发。

这个语句的核心在于，当INSERT操作遇到唯一约束或主键冲突时，它会执行一个备用动作，而不是直接失败。

基本语法是：

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...) ON CONFLICT (冲突列或索引) DO 动作;

这里的“冲突列或索引”通常是你的主键或者任何一个UNIQUE约束的列或列组合。

1. ON CONFLICT DO NOTHING： 当冲突发生时，什么也不做，简单地忽略这条插入操作。这对于“如果数据存在就不要动它”的场景非常有用。

假设我们有一个 users 表，email 列是唯一的：

CREATE TABLE users (     id SERIAL PRIMARY KEY,     username VARCHAR(50) NOT NULL,     email VARCHAR(100) UNIQUE NOT NULL );

现在，我们想插入一个用户，如果邮箱已经存在，就什么也不做：

INSERT INTO users (username, email) VALUES ('alice_new', 'alice@example.com') ON CONFLICT (email) DO NOTHING;

如果 alice@example.com 已经存在，这条语句不会报错，也不会插入新数据，而是静默地完成。如果没有冲突，则正常插入。

2. ON CONFLICT DO UPDATE SET： 当冲突发生时，更新已有的那条记录。这是最常用的“UPSERT”模式，比如你想更新用户的登录时间、分数等。

INSERT INTO users (username, email) VALUES ('bob_updated', 'bob@example.com') ON CONFLICT (email) DO UPDATE SET     username = EXCLUDED.username, -- 使用 EXCLUDED 关键字引用尝试插入的新值     updated_at = NOW();           -- 也可以更新其他列，比如时间戳

这里需要注意一个特殊的关键字 EXCLUDED。它代表了尝试插入但因为冲突而被“排除”的行。也就是说，EXCLUDED.username 就是你尝试插入的 bob_updated。

一个更实际的例子：统计网站访问量 假设你有一个 page_views 表，记录每个页面的访问次数，page_path 是唯一的。每次访问，你都希望增加计数。

CREATE TABLE page_views (     page_path VARCHAR(255) PRIMARY KEY,     view_count INT DEFAULT 0,     last_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

现在，每次有页面访问，你可以这样做：

INSERT INTO page_views (page_path, view_count) VALUES ('/home', 1) ON CONFLICT (page_path) DO UPDATE SET     view_count = page_views.view_count + 1, -- 增加现有计数     last_viewed_at = NOW();                 -- 更新最后访问时间

这条语句的逻辑非常清晰：如果 /home 页面是第一次被访问，就插入一条新记录，view_count 为1。如果不是第一次，就找到 /home 的记录，把 view_count 加1，并更新 last_viewed_at。这比先SELECT再UPDATE或INSERT的逻辑要简洁高效得多，而且能更好地处理并发冲突。

ON CONFLICT语句极大地简化了数据库操作中“存在则更新，不存在则插入”的复杂逻辑，是PostgreSQL非常实用的一个特性。