使用
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接口不同，请参见如下文档：
PostgreSQL JDBC Driver - JDBC 4.2 9.4.1209 API
PostgreSQL 9.6.2 Documentation — Functions Associated with the COPY Command
批量更新数据
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';
test03=# TRUNCATE test;
TRUNCATE TABLE
test03=# SELECT * FROM test;
id | info | crt_time
----+------+----------
(0 rows)
