Insert, update, and delete multiple data records at a time - ApsaraDB RDS

This topic describes how to insert, update, and delete multiple data records of an ApsaraDB RDS for PostgreSQL instance at a time. These operations can reduce the number of interactions between your RDS instance and your application and increase the data processing capability of your RDS instance.

Insert multiple data records at a time

You can use one of the following methods to insert multiple data records at a time:

In this example, the following statement is used to create a test table named tbl1:

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

Method 1: Execute the INSERT INTO ... SELECT statement.

Sample statements

-- Insert multiple data records into the tbl1 table at a time.
INSERT INTO tbl1 (id, info ,crt_time) SELECT GENERATE_SERIES(1,10000),'test',NOW(); 

-- Query the data volume.
SELECT COUNT(*) FROM tbl1;

Output

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

Method 2: Use the VALUES(),(),...(); function.

Sample statements

-- Insert multiple data records into the tbl1 table at a time.
INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW()), (2,'test2',NOW()), (3,'test3',NOW());

-- Query the data volume.
SELECT COUNT(*) FROM tbl1;

Output

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

Method 3: Run a BEGIN; ...Multiple INSERT statements...; END; transaction.

Note

This method allows you to include multiple INSERT statements in one transaction. This reduces the wait time on transaction commit, which in turn improves the performance of your RDS instance.

Sample statements

-- Insert multiple data records into the tbl1 table at a time.
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; 

-- Query the data volume.
SELECT COUNT(*) FROM tbl1;

Output

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

Method 4: Use the COPY command.

Note

Compared with the INSERT statement, the COPY command is easy to use and can insert data at higher efficiency.

Sample statements

-- Read data from the standard input stream and copy the data to the tbl1 table. 
COPY tbl1 FROM stdin;

-- Enter the first data record.
1    'test'  '2023-01-01'
-- Enter the second data record.
2    'test1' '2023-02-02'
-- End
\.

-- Query the data volume.
SELECT COUNT(*) FROM tbl1;

Output
```
   count     
  -------    
       2    
  (1 row)
```
Note
The available COPY functions vary based on the language driver that you use. For more information, see the following documentation:
- PostgreSQL JDBC Driver - JDBC 4.2 9.4.1209 API
- PostgreSQL 9.6.2 Documentation - Functions Associated with the COPY Command

Update multiple data records at a time

Sample statements

-- Update multiple data records at a time.
UPDATE tbl1 SET info=tmp.info from (VALUES (1,'new1'),(2,'new2'),(6,'new6')) AS tmp (id,info) WHERE tbl1.id=tmp.id; 

-- Query table data.
SELECT * FROM tbl1;

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)

Delete multiple data records at a time

Delete partial table data at a time

Sample statements

-- Delete multiple data records at a time.
DELETE FROM tbl1 USING (VALUES (3),(4),(5)) AS tmp(id) WHERE tbl1.id=tmp.id; 

-- Query table data.
SELECT * FROM tbl1;

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

Delete all table data. We recommend that you use the TRUNCATE statement.

Sample statements

-- Configure a lock timeout period.
SET lock_timeout = '1s';

-- Delete all data in the tbl1 table.
TRUNCATE tbl1;

-- Query table data.
SELECT * FROM tbl1;

Output

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