PostgreSQL插入数据怎么操作_PostgreSQL插入数据详细步骤-SQL-PHP中文网

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

postgresql插入数据怎么操作_postgresql插入数据详细步骤

在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（通常不建议，除非有特殊迁移场景），你也可以：

阿里云-虚拟数字人
阿里云-虚拟数字人是什么？ ...

2

查看详情
```
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非常实用的一个特性。

以上就是PostgreSQL插入数据怎么操作_PostgreSQL插入数据详细步骤的详细内容，更多请关注php中文网其它相关文章！