作者:为什么不嫩注册 | 来源:互联网 | 2023-09-24 05:01
--批量插入1.insertintoselectINSERTINTOTABLE_NAMESELECT*FROMSOURCE_TABLE_NAME;2.insertint
--批量插入
1.insert into ... select ...
INSERT INTO TABLE_NAME SELECT * FROM SOURCE_TABLE_NAME;
2.insert into values(),(),()
一条sql插入多行数据,相比一条插入能减少与数据库交互,减少数据库wal日志生成,提升插入效率
3.COPY或者\copy元命令
测试copy命令效率,测试机:2核2g内存
postgres=# create table tbl_batch4(id int4 ,info text,create_time timestamp(6) with time zone default clock_timestamp());
CREATE TABLE
postgres=# insert into tbl_batch4 (id,info) select n,n||'_batch4' from generate_series(1,10000000) n;
2021-10-14 09:28:01.062 EDT [1718] LOG: checkpoints are occurring too frequently (16 seconds apart)
2021-10-14 09:28:01.062 EDT [1718] HINT: Consider increasing the configuration parameter "max_wal_size".
INSERT 0 10000000
Time: 36805.325 ms (00:36.805)
postgres=# select count(1) from tbl_batch4;
count
----------
10000000
(1 row)
Time: 2660.098 ms (00:02.660)
postgres=# \dt+ tbl_batch4
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------+-------+----------+--------+-------------
public | tbl_batch4 | table | postgres | 575 MB |
(1 row)
postgres=# COPY public.tbl_batch4 to '/home/postgres/tbl_batch4.sql';
COPY 10000000
Time: 10605.317 ms (00:10.605)
postgres=# truncate tbl_batch4 ;
TRUNCATE TABLE
Time: 151.531 ms
postgres=# COPY public.tbl_batch4 from '/home/postgres/tbl_batch4.sql';
COPY 10000000
Time: 22942.585 ms (00:22.943)
RETURNING返回修改的数据
使用方法(会返回操作的值可以接*表示返回所有字段,可接单独的字段)
postgres=# insert into test_2 values (3) returning *;
id
----
3
(1 row)
INSERT 0 1
Time: 0.652 ms