Postgresql Insert

PostgreSQL INSERT 语句：向表中添加数据

欢迎来到我们的 PostgreSQL 教程系列！在本课中，我们将重点介绍 PostgreSQL 中的 INSERT 语句，这是向数据库表中添加新数据的关键。有效使用 INSERT 语句可以帮助你为表格填充有意义的数据。

INSERT 语句简介

PostgreSQL 中的 INSERT 语句用于向表中添加新行。你可以向所有列或特定列插入数据，还可以一次插入多行，甚至可以从其他表中插入数据。

基本语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • INSERT INTO：指定要插入数据的表。
  • VALUES：列出要插入列的值。

向 PostgreSQL 表中插入数据

接下来，我们将通过实际示例，使用我们熟悉的图书馆和电影数据库来介绍各种使用 INSERT 语句的方法。

1. 插入单行

示例：在 books 表中添加一本新书

INSERT INTO books (title, author, price, pages)
VALUES ('Brave New World', 'Aldous Huxley', 9.99, 268);

查看模式初始化

解释：

  • 一本名为 《Brave New World》 的新书被添加到 books 表中，指定了该书的作者、价格和页数。

2. 插入多行

你可以在一个语句中插入多行，通过用逗号分隔各个值集。

示例：在 authors 表中添加多个作者

INSERT INTO authors (name)
VALUES ('Aldous Huxley'),
       ('Ray Bradbury'),
       ('Kurt Vonnegut');

解释：

  • 向 authors 表中添加了三个新作者。

3. 向特定列插入数据

如果你没有所有列的值，你可以只向特定列插入数据。未指定的列将接收其默认值或 NULL（如果未设置默认值）。

示例：插入一部没有指定其他细节的电影

由于我们的 movies 表只有一个 title 列，所以我们只能向该列插入数据。

INSERT INTO movies (title)
VALUES ('Blade Runner');

解释：

  • 一部名为 《Blade Runner》 的新电影被添加到 movies 表中。

4. 使用默认值

你可以使用 DEFAULT 关键字显式插入列的默认值，前提是你的表已定义了默认值。

示例：插入一本具有默认价格的书

首先，修改 books 表以设置默认价格（如果尚未设置）：

-- 修改 books 表，设置默认价格
ALTER TABLE books ALTER COLUMN price SET DEFAULT 9.99;
 
-- 使用默认价格插入一本书
INSERT INTO books (title, author, pages)
VALUES ('Fahrenheit 451', 'Ray Bradbury', 194);

解释：

  • 书籍 《Fahrenheit 451》 被以默认价格 $9.99 添加。

5. 从其他表中插入数据

你可以使用 INSERT INTO ... SELECT 语句通过从另一个表中选择数据来插入数据。

示例：向新表中复制作者

-- 创建一个新表 classic_authors 用于经典作家
CREATE TABLE classic_authors (name VARCHAR(255));
 
-- 将 authors 表中的作者插入 classic_authors
INSERT INTO classic_authors (name)
SELECT name
FROM authors
WHERE name IN ('George Orwell', 'Jane Austen', 'Leo Tolstoy');

解释：

  • 创建了一个名为 classic_authors 的新表。
  • 将与指定名字匹配的作者从 authors 表复制到 classic_authors。

6. 返回插入数据

RETURNING 子句允许你从插入的行中返回数据。

示例：插入一本书并返回其标题和价格

INSERT INTO books (title, author, price, pages)
VALUES ('Slaughterhouse-Five', 'Kurt Vonnegut', 8.99, 275)
RETURNING title, price;

解释：

  • 书籍已插入，返回了插入行的 title 和 price。

7. 使用 ON CONFLICT 处理冲突（Upsert）

ON CONFLICT 子句允许你在插入时指定一种替代操作，以防插入违反了唯一约束。

首先，我们需要在相关列上设置一个唯一约束。

示例：插入作者，若已存在则不做任何操作

首先，确保在 name 列上有唯一约束：

ALTER TABLE authors ADD CONSTRAINT unique_name UNIQUE (name);

现在使用 ON CONFLICT：

INSERT INTO authors (name)
VALUES ('George Orwell')
ON CONFLICT (name) DO NOTHING;

解释：

  • 尝试向 authors 表插入 'George Orwell'。
  • 如果 'George Orwell' 已经存在，则跳过此插入。

8. 使用 INSERT 和子查询

你可以使用子查询来生成插入的值。

示例：从尚未在 authors 表中的书作者中插入新的作者

假设我们有一些书籍的作者尚未在 authors 表中。

首先，我们找到处于 books 表，但不在 authors 表中的作者，并将其插入 authors 表。

INSERT INTO authors (name)
SELECT DISTINCT author
FROM books
WHERE author NOT IN (SELECT name FROM authors);

解释：

  • 来自 books 表的作者被插入到 authors 表中，但前提是他们尚未在 authors 表中。
  • 在我们的模式中，所有作者都已存在，因此不会添加新作者，除非有新书连同新作者一起被添加到 books 表中。

9. 插入 JSON 数据

我们当前的模式中没有包含具有 JSON 列的表，但我们可以创建一个。

示例：向表中插入一个 JSON 对象

首先，创建一个具有 JSON 列的表：

CREATE TABLE book_info (
    id SERIAL PRIMARY KEY,
    info JSONB
);

插入 JSON 数据：

INSERT INTO book_info (info)
VALUES ('{"title": "Dune", "author": "Frank Herbert", "price": 10.99, "pages": 412}');

解释：

  • 一个包含书籍详细信息的 JSON 对象被插入到 book_info 表中。

10. 在表之间复制数据

你可以将数据从一个表复制到另一个表，并同时执行转换。

示例：将书籍数据复制到 discounted_books 表，并应用 10% 折扣

创建 discounted_books 表：

CREATE TABLE discounted_books (
    title VARCHAR(255),
    author VARCHAR(255),
    discounted_price NUMERIC(10, 2)
);

插入带有转换值的数据：

INSERT INTO discounted_books (title, author, discounted_price)
SELECT title, author, price * 0.9
FROM books;

解释：

  • 所有书籍按 10% 折扣将数据复制到 discounted_books 表中。

结论

PostgreSQL 中的 INSERT 语句是向表中添加数据的多功能工具。无论你是插入单行、多行还是从其他表中添加数据，掌握 INSERT 语句对于有效的数据库管理至关重要。

关键要点：

  • 使用 INSERT INTO ... VALUES 添加新数据。
  • 提供多个值集以插入多行数据。
  • 指定列以仅向特定列插入数据。
  • 使用 RETURNING 获取插入行的信息。
  • 使用 ON CONFLICT 处理冲突以避免插入错误。
  • 使用 INSERT INTO ... SELECT 从其他表中插入数据。
  • 利用 PostgreSQL 对 JSON 数据类型的支持。

Schema Initialization

To run the examples provided, ensure your PostgreSQL database is set up with the following schema:

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

尽情练习使用此模式插入数据，以增强你对 PostgreSQL INSERT 语句的理解。祝你编程愉快！

Postgresql Group ByPostgresql Join