Shulou(Shulou.com)05/31 Report--

This article focuses on "how to understand the characteristics of PostgreSQL12 COPY and bulkloading". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to understand the characteristics of PostgreSQL12 COPY and bulkloading".

Does the business rely on COPY commands to load data? PostgreSQL12 provides a new feature that greatly speeds up loading.

COPY:Loading and unloading data as fast as possible

A closer look at PostgreSQL12's COPY syntax shows that there are two changes:

1)\ h there will be a link to the manual document

2) COPY supports WHERE conditions

Here is the complete syntax:

Db12=#\ h COPYCommand: COPYDescription: copy data between a file and a tableSyntax:COPY table_name [(column_name [,...])] FROM {'filename' | PROGRAM' command' | STDIN} [[WITH] (option [,...])] [WHERE condition] COPY {table_name [(column_name [ ])] | (query)} TO {'filename' | PROGRAM' command' | STDOUT} [[WITH] (option [,...])] where option can be one of: FORMAT format_name FREEZE [boolean] DELIMITER 'delimiter_character' NULL' null_string' HEADER [boolean] QUOTE 'quote_character' ESCAPE' escape_character' FORCE_QUOTE {(column_name [ )) | *} FORCE_NOT_NULL (column_name [,...]) FORCE_NULL (column_name [,...]) ENCODING 'encoding_name'URL: https://www.postgresql.org/docs/12/sql-copy.html

Although it is convenient to add document links, WHERE filtering is more useful. What is the purpose of this function? Currently, COPY can only import the entire file. But there are problems in some scenarios: in many scenarios, people only want to load a subset of the data and have to write a lot of code to filter before exporting.

COPY...WHERE: filtering on import

PostgreSQL can be easily filtered through this condition. Here is a simple example:

Db12=# CREATE TABLE t_demo AS SELECT * FROM generate_series (1, 1000) AS id;SELECT 1000

First insert 1000 rows of data, and then export to a file:

Db12=# COPY t_demo TO'/ tmp/file.txt';COPY 1000

Finally, re-import the data:

Db12=# CREATE TABLE t_import (x int); CREATE TABLEdb12=# COPY t_import FROM'/ tmp/file.txt' WHERE x

< 5;COPY 4db12=# SELECT * FROM t_import; x--- 1 2 3 4(4 rows) 如上说是，过滤数据非常简单直接。需要注意，导出列是"id"，导入列是"x"。文件文件不知道导入表的表结构---需要确保过滤导入表的列名。 其他特性 COPY可以将数据发送到UNIX pipe，还可以从pipe中读取数据。如下所示： db12=# COPY t_demo TO PROGRAM 'gzip -c >

/ tmp/file.txt.gz';COPY 1000db12=# COPY t_import FROM PROGRAM 'gunzip-c / tmp/file.txt.gz' WHERE x BETWEEN 100 AND 103 copy 4db12=# SELECT * FROM t_import WHERE x > = 100; x-100 101 102 103 (4 rows)

In some scenarios, you don't just want to export the data. In the above scenario, we want to compress the data when we export it, and extract it and filter it when we import it. As shown above, this approach is very simple.

At this point, I believe you have a deeper understanding of "how to understand the characteristics of PostgreSQL12 COPY and bulkloading". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.