edit-icon download-icon

Update, delete, and insert data in batches

Last Updated: Feb 24, 2018

A batch operation helps reduce the interactions between databases and application programs and improve the data processing throughput. This article provides examples to illustrate how to insert, update, and delete data in batches.

Batch insert data

Use any of the following four methods to insert data in batches:

  • Use insert into ... select.

    1. postgres=# insert into tbl1 (id, info ,crt_time) select generate_series(1,10000),'test',now();
    2. INSERT 0 10000
    3. postgres=# select count(*) from tbl1;
    4. count
    5. -------
    6. 10001
    7. (1 row)
  • Use values(),(),...();.

    1. postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now()), (2,'test2',now()), (3,'test3',now());
    2. INSERT 0 3
  • Use BEGIN; ...multiple inserts...; END;. Strictly speaking, it is not a batch operation, but it helps improve performance and reduce the synchronization wait time when transactions are committed.

    1. postgres=# begin;
    2. BEGIN
    3. postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());
    4. INSERT 0 1
    5. postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());
    6. INSERT 0 1
    7. postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());
    8. INSERT 0 1
    9. postgres=# end;
    10. COMMIT
  • Use the Copy protocol. Different from the Insert protocol, the Copy protocol is more simplified and makes insertion more efficient.

    1. test03=# \d test
    2. Table "public.test"
    3. Column | Type | Modifiers
    4. ----------+-----------------------------+-----------
    5. id | integer | not null
    6. info | text |
    7. crt_time | timestamp without time zone |
    8. Indexes:
    9. "test_pkey" PRIMARY KEY, btree (id)
    10. test03=# copy test from stdin;
    11. Enter data to be copied followed by a newline.
    12. End with a backslash and a period on a line by itself.
    13. >> 8 'test' '2017-01-01'
    14. >> 9 'test9' '2017-02-02'
    15. >> \.
    16. COPY 2

    Note: Drivers based on different languages have different COPY interfaces. For more information, see the following documents.

Batch update data

  1. test03=# update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id;
  2. UPDATE 3
  3. test03=# select * from test;
  4. id | info | crt_time
  5. ----+--------------+----------------------------
  6. 3 | hello | 2017-04-24 15:31:49.14291
  7. 4 | digoal0123 | 2017-04-24 15:42:50.912887
  8. 5 | hello digoal | 2017-04-24 15:57:29.622045
  9. 1 | new1 | 2017-04-24 15:58:55.610072
  10. 2 | new2 | 2017-04-24 15:28:20.37392
  11. 6 | new6 | 2017-04-24 15:59:12.265915
  12. (6 rows)

Batch delete data

  1. test03=# delete from test using (values (3),(4),(5)) as tmp(id) where test.id=tmp.id;
  2. DELETE 3
  3. test03=# select * from test;
  4. id | info | crt_time
  5. ----+---------+----------------------------
  6. 1 | new1 | 2017-04-24 15:58:55.610072
  7. 2 | new2 | 2017-04-24 15:28:20.37392
  8. 6 | new6 | 2017-04-24 15:59:12.265915

If you want to clear the entire table, we recommend that you use TRUNCATE.

  1. test03=# set lock_timeout = '1s';
  2. SET
  3. test03=# truncate test;
  4. TRUNCATE TABLE
  5. test03=# select * from test;
  6. id | info | crt_time
  7. ----+------+----------
  8. (0 rows)
Thank you! We've received your feedback.