postgresql copy 远程复制 pgsql copy from

转载

蓝月亮 2024-04-28 10:59:13

文章标签 pg copy from pg copy sql postgresql java 文章分类 云原生云计算

postgresql的copy命令用来在文件和表之间进行数据复制，复制效率很高。

官方提供了命令行的copy命令以及Java封装的copy，下文将分别介绍说明。

命令行copy

copy有两个命令：COPY FROM和COPY TO。

COPY TO用于把一个表的内容复制到一个文件；COPY FROM从文件复制数据到表中。

COPY TO中也可以指定查询，将查询结果写入文件。COPY FROM中，文件的字段按照顺序写入到指定列中。

语法

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 [, ...] ) ]

其中 option 可以是下列之一：
    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'

参数说明

table_name：已有表的表名

column_name：要被复制的列列表

query：指定查询语句，将查询结果导出

file：输入或输出的文件名

PROGRAM：要执行的命令。COPY FROM中，输入从该命令的输出获取；COPY TO中，输出写入到该命令的标准输入。

STDIN：指定标准输入流

STDOUT：指定标准输出流

boolean：指定选项打开还是关闭。ture、false

FORMAT：选择要读取或者写入的数据格式：text、csv等

DELIMITER：指定分割每列的字符

NULL：指定表示一个空值的字符串。文本格式中默认是 \N（反斜线-N），CSV格式中默认是一个未加引用的空串。

HEADER：指定文件包含标题行。

ENCODING：指定文件的编码，如果省略，将使用当前客户端编码

WHERE：COPY FROM时指定条件，不满足条件不会写入

注意

复制语句有：copy和\copy两种。

copy是服务器端来进行读取或写入，文件必须位于服务器端，且启动服务器的用户对文件可以读写。

\copy是客户端来进行读取或写入，是取决于客户端，文件位于客户端且启动客户端的用户对文件有访问权限。

示例

导出表tb1的数据到tb1.csv中，带有标题行且列之间的分隔符为|

\copy ( select * from tb1) to '/home/export/tb1.csv' delimiter '|' csv header;

导入数据到表tb1中

\copy tb1 from '/home/export/tb1.csv'
with (FORMAT csv, DELIMITER '|', HEADER true);

更多命令相关信息可查看官方文档

Java封装的copy

官方提供的org.postgresql.copy包下的相关类，可以通过Java的输入输出流将表进行数据导出或写入。

pom依赖

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.16</version>
</dependency>

demo代码

package com.upupfeng.postgres;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class PgConnection {

    private String dbUrl;
    private String user;
    private String password;

    public PgConnection(String dbUrl, String user, String password) {
        this.dbUrl = dbUrl;
        this.user = user;
        this.password = password;
    }

    // 获取数据库连接
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        return DriverManager.getConnection(dbUrl, user, password);
    }

    // 将表数据写入到文件
    public void copy2File(Connection conn, String sql, String path) throws SQLException, IOException {
        CopyManager copyManager = new CopyManager(conn.unwrap(BaseConnection.class));
        try (FileOutputStream fileOutputStream = new FileOutputStream(path)) {
            copyManager.copyOut(String.format("copy (%s) to stdout", sql), fileOutputStream);
        }
    }

    // 将文件数据写入表中
    public void copy2DB(Connection conn, String tableName, String path) throws IOException, SQLException {
        CopyManager copyManager = new CopyManager(conn.unwrap(BaseConnection.class));
        try (FileInputStream fileInputStream = new FileInputStream(path)) {
            copyManager.copyIn(String.format("copy %s from stdin", tableName), fileInputStream);
        }
    }

    public static void main(String[] args) throws Exception {
        String dbUrl = "jdbc:postgresql://192.168.168.200:5432/mwf_db";
        String user = "mwf";
        String password = "123456";

        String sql = "select * from tb1";
        String destPath = "D:\\tb1.csv";

        String srcPath = "D:\\tb1.csv";
        String table = "tb1";

        PgConnection pgConnection = new PgConnection(dbUrl, user, password);
        Connection connection = pgConnection.getConnection();

        pgConnection.copy2File(connection, sql, destPath);

        pgConnection.copy2DB(connection, table, srcPath);
    }
    
}