您可以通过如下四种方法进行批量插入数据：
- 使用
INSERT INTO ... SELECT的方法。
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)
- 使用
VALUES(),(),...();的方法。
postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW()), (2,'test2',NOW()), (3,'test3',NOW()); INSERT 0 3
- 使用
BEGIN; ...多条INSERT...; END;的方法。严格来说，这不属于批量，但可以减少事务提交时的同步等待，同样可以提升性能。
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
- 使用COPY协议。COPY协议与INSERT协议不一样，更加精简，插入效率高。
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
- 说明不同的语言驱动，对应的COPY接口不同，请参见如下文档：
批量更新数据
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)
批量删除数据
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
如果要清除全表，建议您使用TRUNCATE。
test03=# SET lock_timeout = '1s'; SET test03=# TRUNCATE test; TRUNCATE TABLE test03=# SELECT * FROM test; id | info | crt_time ----+------+---------- (0 rows)
