Insert, update, and delete multiple data records at a time

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.

• 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.

• 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)