Insert, update, and delete multiple data records at a time-ApsaraDB RDS(RDS)-阿里云帮助中心

Batch operations let you insert, update, or delete multiple rows in a single statement or transaction, reducing round trips between your application and ApsaraDB RDS for PostgreSQL. This improves throughput and reduces database load.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance
Access to psql or another PostgreSQL client

Set up the test table

All examples in this topic use a table named tbl1. Run the following statement to create it:

CREATE TABLE tbl1 (
    id SERIAL PRIMARY KEY,
    info TEXT,
    crt_time TIMESTAMP
);

Insert multiple rows at a time

Four methods are available. Choose based on your data source and volume:

Method	Best for
`INSERT INTO ... SELECT`	Generating rows from a series or another query
`VALUES (...), (...)`	A small, known set of rows specified inline
`BEGIN ... END` transaction	Multiple independent INSERT statements that must succeed or fail together
`COPY`	Large volumes of data from a file or stream (fastest option)

For large data loads, COPY is almost always faster than INSERT, even when INSERT statements are batched in a single transaction.

Method 1: INSERT INTO ... SELECT

Use GENERATE_SERIES or any SELECT query to generate rows in bulk.

-- Insert 10,000 rows into tbl1.
INSERT INTO tbl1 (id, info, crt_time) SELECT GENERATE_SERIES(1,10000), 'test', NOW();

-- Verify the row count.
SELECT COUNT(*) FROM tbl1;

Expected output:

 count
-------
 10000
(1 row)

Method 2: VALUES with multiple row lists

Specify all rows inline using VALUES (...), (...), (...).

-- Insert three rows into tbl1.
INSERT INTO tbl1 (id, info, crt_time) VALUES (1,'test',NOW()), (2,'test2',NOW()), (3,'test3',NOW());

-- Verify the row count.
SELECT COUNT(*) FROM tbl1;

Expected output:

 count
-------
     3
(1 row)

Method 3: Transaction block (BEGIN ... END)

Wrap multiple INSERT statements in a transaction. All statements commit together, which reduces transaction commit overhead. If any INSERT fails, the entire transaction rolls back — leaving the table unchanged and preventing partially loaded data.

-- Insert three rows in a single transaction.
BEGIN;
INSERT INTO tbl1 (id, info, crt_time) VALUES (1,'test',NOW());
INSERT INTO tbl1 (id, info, crt_time) VALUES (2,'test2',NOW());
INSERT INTO tbl1 (id, info, crt_time) VALUES (3,'test3',NOW());
END;

-- Verify the row count.
SELECT COUNT(*) FROM tbl1;

Expected output:

 count
-------
     3
(1 row)

END is an alias for COMMIT in PostgreSQL.

Method 4: COPY

COPY loads data directly into a table from stdin or a file with less overhead than INSERT, making it the best option for large data sets.

Load from stdin (when data is not in a file):

-- Load rows from stdin, using | as the column delimiter.
COPY tbl1 FROM stdin WITH (DELIMITER '|');
1|'test'|'2023-01-01'
2|'test1'|'2023-02-02'
\.

-- Verify the row count.
SELECT COUNT(*) FROM tbl1;

Expected output:

 count
-------
     2
(1 row)

Available COPY functions vary by database driver. For details, see the PostgreSQL JDBC Driver API reference and Functions associated with the COPY command.

Update multiple rows at a time

Use UPDATE ... FROM (VALUES ...) to update multiple rows in a single statement. The following example updates the info column for rows with id values of 1, 2, and 6.

-- Update info for three rows.
UPDATE tbl1 SET info = tmp.info
FROM (VALUES (1,'new1'), (2,'new2'), (6,'new6')) AS tmp (id, info)
WHERE tbl1.id = tmp.id;

-- Verify the result.
SELECT * FROM tbl1;

Expected output:

 id |     info     |          crt_time
----+--------------+----------------------------
  3 | hello        | 2017-04-24 15:31:49.14291
  4 | digoal0123   | 2017-04-24 15:42:50.912887
  5 | hello digoal | 2017-04-24 15:57:29.622045
  1 | new1         | 2017-04-24 15:58:55.610072
  2 | new2         | 2017-04-24 15:28:20.37392
  6 | new6         | 2017-04-24 15:59:12.265915
(6 rows)

To confirm which rows were modified, add a RETURNING clause:

UPDATE tbl1 SET info = tmp.info
FROM (VALUES (1,'new1'), (2,'new2'), (6,'new6')) AS tmp (id, info)
WHERE tbl1.id = tmp.id
RETURNING tbl1.id, tbl1.info;

Delete multiple rows at a time

Delete specific rows

Use DELETE FROM ... USING (VALUES ...) to delete rows that match a set of values.

-- Delete rows with id 3, 4, and 5.
DELETE FROM tbl1 USING (VALUES (3),(4),(5)) AS tmp(id) WHERE tbl1.id = tmp.id;

-- Verify the result.
SELECT * FROM tbl1;

Expected output:

 id | info |          crt_time
----+------+----------------------------
  1 | new1 | 2017-04-24 15:58:55.610072
  2 | new2 | 2017-04-24 15:28:20.37392
  6 | new6 | 2017-04-24 15:59:12.265915

To confirm which rows were deleted, add a RETURNING clause:

DELETE FROM tbl1 USING (VALUES (3),(4),(5)) AS tmp(id)
WHERE tbl1.id = tmp.id
RETURNING tbl1.id;

Delete all rows from a table

To remove all rows from a table, use TRUNCATE instead of DELETE FROM. TRUNCATE is faster because it does not scan rows individually.

-- Set a lock timeout to avoid blocking other sessions.
SET lock_timeout = '1s';

-- Remove all rows from tbl1.
TRUNCATE tbl1;

-- Verify the result.
SELECT * FROM tbl1;

Expected output:

 id | info | crt_time
----+------+----------
(0 rows)