PostgreSQL INSERT 语句

最后修改时间：2025年3月1日

PostgreSQL 的 INSERT 语句用于向表中添加新行。它是最基本的 SQL 操作之一，对于填充数据表至关重要。本教程将通过实际示例介绍如何使用 INSERT 语句。

INSERT 语句可用于插入单行、多行或从另一个表插入数据。它还可以处理默认值和条件插入。

基本 INSERT 语句

此示例演示了如何将单行插入 books 表

basic_insert.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
VALUES (131, 'The Hidden Truth', 'Alice Brown', 'Mystery', 12.99, 2023);

INSERT INTO 语句指定表和列，VALUES 子句提供新行的数据。

插入多行

此示例演示了如何在单个查询中将多行插入 books 表

insert_multiple_rows.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
VALUES 
    (132, 'The Lost City', 'John Green', 'Adventure', 14.99, 2022),
    (133, 'The Final Chapter', 'Sarah Black', 'Thriller', 16.50, 2021),
    (134, 'The Forgotten Path', 'Emily White', 'Fantasy', 18.75, 2020);

可以通过用逗号分隔每组值来插入多行。

插入带默认值的行

此示例演示了如何使用某些列的默认值插入行

insert_with_defaults.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL DEFAULT 0.00 CHECK (price >= 0),
--     publication_year INTEGER NOT NULL DEFAULT 2023 CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre)
VALUES (135, 'The Silent River', 'Michael Blue', 'Mystery');

如果列定义了默认值，则在未提供值时将使用该默认值。

从另一个表插入

此示例演示了如何从另一个表插入数据

insert_from_table.sql

-- CREATE TABLE old_books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
SELECT book_id, title, author, genre, price, publication_year
FROM old_books
WHERE publication_year > 2010;

SELECT 语句从 old_books 表检索数据并将其插入 books 表。

INSERT 语句带 RETURNING 子句

此示例演示了如何插入一行并返回插入的数据

insert_returning.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
VALUES (136, 'The Golden Key', 'Laura Green', 'Fantasy', 19.99, 2024)
RETURNING *;

RETURNING 子句返回插入的行，这对于验证或进一步处理很有用。

INSERT 语句带 ON CONFLICT

此示例演示了如何处理插入数据时的冲突

insert_on_conflict.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
VALUES (101, 'The Silent Forest', 'Jane Doe', 'Mystery', 12.99, 2020)
ON CONFLICT (book_id) DO NOTHING;

如果已存在具有相同 book_id 的行，ON CONFLICT 子句可确保不执行任何操作。

INSERT 语句带 ON CONFLICT UPDATE

此示例演示了如何在发生冲突时更新现有行

insert_on_conflict_update.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
VALUES (101, 'The Silent Forest', 'Jane Doe', 'Mystery', 14.99, 2020)
ON CONFLICT (book_id) DO UPDATE
SET price = EXCLUDED.price;

如果已存在具有相同 book_id 的行，ON CONFLICT ... DO UPDATE 子句将更新 price 列。

INSERT 语句带子查询

此示例演示了如何使用子查询插入数据

insert_with_subquery.sql

-- CREATE TABLE old_books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025)
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year)
SELECT book_id, title, author, genre, price, publication_year
FROM old_books
WHERE genre = 'Sci-Fi';

子查询从 old_books 表检索数据并将其插入 books 表。

插入 JSON 数据

此示例演示了如何将 JSON 数据插入表中

insert_json.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025),
--     metadata JSONB
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year, metadata)
VALUES (137, 'The Digital World', 'Chris Tech', 'Sci-Fi', 21.99, 2025, '{"pages": 320, "publisher": "TechPress"}');

metadata 列存储 JSON 数据，可以使用 PostgreSQL 的 JSON 函数对其进行查询和操作。

INSERT 语句带 CURRENT_TIMESTAMP

此示例演示了如何将当前时间戳插入表中

insert_timestamp.sql

-- CREATE TABLE books (
--     book_id INTEGER PRIMARY KEY,
--     title VARCHAR(100) NOT NULL,
--     author VARCHAR(100) NOT NULL,
--     genre VARCHAR(50) NOT NULL,
--     price NUMERIC(5,2) NOT NULL CHECK (price >= 0),
--     publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1900 AND 2025),
--     added_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- );

INSERT INTO books (book_id, title, author, genre, price, publication_year, added_on)
VALUES (138, 'The Time Machine', 'H.G. Wells', 'Sci-Fi', 9.99, 1895, CURRENT_TIMESTAMP);

CURRENT_TIMESTAMP 函数将当前日期和时间插入 added_on 列。

使用 INSERT 的最佳实践

使用明确的列名：始终指定列名以避免错误并提高可读性。
处理冲突：使用 ON CONFLICT 来优雅地处理重复键错误。
验证数据：在插入之前确保数据符合表约束。
使用事务：将多个 INSERT 语句包装在事务中以实现原子性。

来源

PostgreSQL 文档

在本文中，我们通过实际示例和最佳实践，探讨了如何使用 PostgreSQL 的 INSERT 语句向表中添加数据。

作者

我叫 Jan Bodnar，我是一名热情的程序员，拥有丰富的编程经验。自 2007 年以来，我一直在撰写编程文章。至今，我已撰写了 1400 多篇文章和 8 本电子书。我在教授编程方面拥有十多年的经验。