How to execute Postgresql copy command with column information on Java


I feel that it is too pinpoint as a subject and weak, but I wrote it.

The PostgreSQL copy command is You can process at high speed when storing a file in a table. However, although CSV can be registered as it is, If you want to complete the value

  1. Read the CSV file.
  2. Create a CSV file with columns.
  3. Execute the copy command with CopyManager to load the CSV.

So, if you are processing in order, Since writing to the storage runs twice (file and DB), I think it is better to unify it and implement it.

To run the copy command naive in Java

It looks like this in simple writing,

  public static long copy(Connection conn, String filePath, String tableName) throws Exception {
    CopyManager copyManager = new CopyManager((BaseConnection) conn);
    Reader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF8"));
    String sql = "copy " + tableName + " FROM STDIN WITH DELIMITER ','";
    long result = copyManager.copyIn(sql, reader);
    return result;    

Pass the connection to CopyManager Create an instance of the CSV Reader class interface and Pass it to copyIn and pour it. At this time, it is assumed that CSV has the column structure of the table as it is.

Where to give it?

Create your own Reader and I decided to add a system column at the timing of reading, Create a Reader class that implements the following Read method.

public class CsvFileWithSysColReader extends Reader {
  /**Queue for storing character strings with columns*/
  private final Queue<Character> csvBuffer;
  /**I will set it with the argument in the Reader constructor of CSV*/
  private final BufferedReader reader = new ArrayDeque<>();
//~~ Abbreviation ~~
   * @param reader Buffered Reader to wrap
   * @throws IOException
  public CsvFileWithSysColReader(final BufferedReader reader)
      throws IOException {
    //It may be good to have a Boolean such as skipPeader as an argument to have a header skip function.
    this.reader = reader;

  public int read(final char[] cbuf, final int off, final int len) throws IOException {
    int readCount = len;
    //If the CSV read buffer size is less than the number of digits read by the Copy command, read from the file.
    while (this.csvBuffer.size() < len) {
      //If it is the last of the file, it exits and returns only the current number of digits.
      if (loadLine() == 0) {
        readCount = this.csvBuffer.size();
    //If there is a read, the number of digits is returned.
    if (readCount != 0) {
      for (int i = off; i < readCount; i++) {
        cbuf[i] = this.csvBuffer.poll();
      return readCount;
    } else {
      //If not, at the end-Return 1.
      return -1;

   *Row load
   * @return Number of load result characters
   * @throws IOException
  private int loadLine() throws IOException {
    final String line = this.reader.readLine();
    if (line == null) {
      //If there is no acquisition line, 0 is returned and the process ends.
      return 0;
    } else {
      //addSysCol: A function that adds system columns, edit as you like.
      //It is also possible to add an error column by checking around here
      final String lineWithSysCol= addSysCol(line);
      for (int i = 0; i < lineWithSysCol.length(); i++) {
      return lineWithSysCol.length();
//~~ Abbreviation ~~

And execute like this.

  public static long copy(final Connection conn, final String filePath, final String tableName) throws Exception {
    final CopyManager copyManager = new CopyManager((BaseConnection) conn);
    final Reader reader = new CsvFileWithSysColReader(new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF8")));
    final String sql = "copy " + tableName + " FROM STDIN WITH DELIMITER ','";
    final long result = copyManager.copyIn(sql, reader);
    return result;    

The implementation of CsvFileWithSysColReader.addSysCol (String line) above expands the range as follows.

  1. Give information that is not in CSV by passing it from the outside.
  2. Validate and set the result in the table column to load
  3. Cut the overflow of digits according to the number of digits in the table column
  4. Number serial numbers
  5. etc...

in conclusion

In application development I / O is often fixed, so If you create a wrap class with Interface and absorb the processing there There are many things that can be done.

By the way Actually, I implemented it with various functions, I dropped the function from there and wrote only the main points, I'm sorry if there is any omission. .. ..

Recommended Posts

How to execute Postgresql copy command with column information on Java
[Java] How to execute tasks on a regular basis
[Java] How to update Java on Windows
How to switch Java version with direnv in terminal on Mac
How to call with One Touch (without confirmation) on Android (Java)
How to check Java installed on Mac
How to compile Java with VsCode & Ant
[Java] How to compare with equals method
How to switch Java versions on Mac
[Java] How to test for null with JUnit
How to use Java framework with AWS Lambda! ??
How to use Java API with lambda expression
[Java] Memo on how to write the source
How to call functions in bulk with Java reflection
How to run javafx with Raspberry Pi Posted on 2020/07/12
[Java 11] I tried to execute Java without compiling with javac
[Java] How to omit spring constructor injection with Lombok
How to deploy Java to AWS Lambda with Serverless Framework
How to use java non-standard library on IntelliJ IDEA
[Ruby on Rails] How to change the column name
[Java] How to encrypt with AES encryption with standard library
Run PostgreSQL on Java
As of April 2018 How to get Java 8 on Mac
Connecting Java application and PostgreSQL with JDBC without using eclipse (execute at command prompt)
How to build Java development environment with VS Code
How to run Java EE Tutial on github on Eclipse
Introduction to java command
How to execute WebCamCapture sample of NyARToolkit for Java
[Java] How to start a new line with StringBuilder
Notes on how to use regular expressions in Java
What to do if you cannot execute with the command "Java package name / class name"
How to develop an app with Jersey Java RESTful API on Alibaba Cloud ECS instance
How to reduce the load on the program even a little when combining characters with JAVA
How to decompile apk file to java source code with MAC
How to use trained model of tensorflow2.0 with Kotlin / Java
How to handle exceptions coolly with Java 8 Stream or Optional
How to deploy a simple Java Servlet app on Heroku
How to deploy a kotlin (java) app on AWS fargate
How to install java9 on elementaryOS Freya or Ubuntu 14.04 LTS
How to switch Java in the OpenJDK era on Mac
How to get started with JDBC using PostgresSQL on MacOS
Investigated how to call services with Watson SDK for Java
How to lower java version
[Java] How to use Map
How to uninstall Java 8 (Mac)
Java to play with Function
Java --How to make JTable
How to use java Optional
How to deploy on heroku
How to minimize Java images
Execute external command with swift
How to write java comments
How to use java class
[Java] How to use Optional ②
Connect from Java to PostgreSQL
[Java] How to use removeAll ()
[Java] How to display Wingdings
[Java] How to use string.format
How to number (number) with html.erb
How to use Java Map
How to update with activerecord-import