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.

You can insert multiple data records at a time by using one of the following four methods:

  • Execute the INSERT INTO ... SELECT statement.

      postgres=# INSERT INTO tbl1 (id, info ,crt_time) SELECT GENERATE_SERIES(1,10000),'test',NOW();    
      INSERT 0 10000    
      postgres=# SELECT COUNT(*) FROM tbl1;    
       count     
      -------    
       10001    
      (1 row)
  • Use the VALUES(),(),...(); function.

    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW()), (2,'test2',NOW()), (3,'test3',NOW());    
    INSERT 0 3
  • Run a BEGIN; ...Multiple INSERT statements...; END; transaction. 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.

    postgres=# BEGIN;    
    BEGIN    
    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW());    
    INSERT 0 1    
    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (2,'test2',NOW());    
    INSERT 0 1    
    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (3,'test3',NOW());    
    INSERT 0 1    
    postgres=# END;    
    COMMIT
  • Use the COPY command. Compared with the INSERT statement, the COPY command is easier-to-use and can insert data at higher efficiency.

      test03=# \d test  
                      Table "public.test"  
        Column  |            Type             | Modifiers   
      ----------+-----------------------------+-----------  
       id       | integer                     | not null  
       info     | text                        |   
       crt_time | timestamp without time zone |   
      Indexes:  
          "test_pkey" PRIMARY KEY, btree (id)  
    
      test03=# COPY test FROM stdin;  
      Enter data to be copied followed by a newline.  
      End with a backslash and a period on a line by itself.  
      >> 8    'test'  '2017-01-01'  
      >> 9    'test9' '2017-02-02'  
      >> \.  
      COPY 2
    Note
    The available COPY functions vary based on the language driver that you use. For more information, see the following documentation:

Update multiple data records at a time

test03=# UPDATE test SET info=tmp.info from (VALUES (1,'new1'),(2,'new2'),(6,'new6')) AS tmp (id,info) WHERE test.id=tmp.id;  
UPDATE 3  
test03=# SELECT * FROM test;  
 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

test03=# DELETE FROM test USING (VALUES (3),(4),(5)) AS tmp(id) WHERE test.id=tmp.id;  
DELETE 3  
test03=# SELECT * FROM test;  
 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

If you want to clear a table, we recommend that you execute the TRUNCATE statement.

test03=# SET lock_timeout = '1s';
SET
test03=# TRUNCATE test;  
TRUNCATE TABLE  
test03=# SELECT * FROM test;  
 id | info | crt_time   
----+------+----------  
(0 rows)