How to Copy Data from File to Postgresql Using Jdbc

how to copy data from file to PostgreSQL using JDBC?

This works...

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

public class PgSqlJdbcCopyStreamsExample {

    public static void main(String[] args) throws Exception {

        if(args.length!=4) {
            System.out.println("Please specify database URL, user, password and file on the command line.");
            System.out.println("Like this: jdbc:postgresql://localhost:5432/test test password file");
        } else {

            System.err.println("Loading driver");
            Class.forName("org.postgresql.Driver");

            System.err.println("Connecting to " + args[0]);
            Connection con = DriverManager.getConnection(args[0],args[1],args[2]);

            System.err.println("Copying text data rows from stdin");

            CopyManager copyManager = new CopyManager((BaseConnection) con);

            FileReader fileReader = new FileReader(args[3]);
            copyManager.copyIn("COPY t FROM STDIN", fileReader );

            System.err.println("Done.");
        }
    }
}

CSV copy to Postgres with array of custom type using JDBC

See https://git.mikael.io/mikaelhg/pg-object-csv-copy-poc/ for a project with a JUnit test that does what you want.

Basically, you want to be able to use commas for two things: to separate array items, and to separate type fields, but you DON'T want the CSV parsing to interpret commas as field delineators.

So

1. you want to tell the CSV parser to consider the whole row to be one string, one field, which you can do by enclosing it in single quotes and telling the CSV parser about this, and
2. you want the PG field parser to consider each array item type instance to be enclosed in a double quote.

Code:

copyManager.copyIn("COPY my_table (addresses) FROM STDIN WITH CSV QUOTE ''''", reader);

DML example 1:

COPY my_table (addresses) FROM STDIN WITH CSV QUOTE ''''

CSV example 1:

'{"(10.0.0.1,1)","(10.0.0.2,2)"}'
'{"(10.10.10.1,80)","(10.10.10.2,443)"}'
'{"(10.10.10.3,8080)","(10.10.10.4,4040)"}'

DML example 2, escaping the double quotes:

COPY my_table (addresses) FROM STDIN WITH CSV

CSV example 2, escaping the double quotes:

"{""(10.0.0.1,1)"",""(10.0.0.2,2)""}"
"{""(10.10.10.1,80)"",""(10.10.10.2,443)""}"
"{""(10.10.10.3,8080)"",""(10.10.10.4,4040)""}"

Full JUnit test class:

package io.mikael.poc;

import com.google.common.io.CharStreams;
import org.junit.*;
import org.postgresql.PGConnection;
import org.postgresql.copy.CopyManager;
import org.testcontainers.containers.PostgreSQLContainer;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import static java.nio.charset.StandardCharsets.UTF_8;

public class CopyTest {

    private Reader reader;

    private Connection connection;

    private CopyManager copyManager;

    private static final String CREATE_TYPE = "CREATE TYPE address AS (ip inet, port int)";

    private static final String CREATE_TABLE = "CREATE TABLE my_table (addresses  address[] NULL)";

    private String loadCsvFromFile(final String fileName) throws IOException {
        try (InputStream is = getClass().getResourceAsStream(fileName)) {
            return CharStreams.toString(new InputStreamReader(is, UTF_8));
        }
    }

    @ClassRule
    public static PostgreSQLContainer db = new PostgreSQLContainer("postgres:10-alpine");

    @BeforeClass
    public static void beforeClass() throws Exception {
        Class.forName("org.postgresql.Driver");
    }

    @Before
    public void before() throws Exception {
        String input = loadCsvFromFile("/data_01.csv");
        reader = new StringReader(input);

        connection = DriverManager.getConnection(db.getJdbcUrl(), db.getUsername(), db.getPassword());
        copyManager = connection.unwrap(PGConnection.class).getCopyAPI();

        connection.setAutoCommit(false);
        connection.beginRequest();

        connection.prepareCall(CREATE_TYPE).execute();
        connection.prepareCall(CREATE_TABLE).execute();
    }

    @After
    public void after() throws Exception {
        connection.rollback();
    }

    @Test
    public void copyTest01() throws Exception {
        copyManager.copyIn("COPY my_table (addresses) FROM STDIN WITH CSV QUOTE ''''", reader);

        final StringWriter writer = new StringWriter();
        copyManager.copyOut("COPY my_table TO STDOUT WITH CSV", writer);
        System.out.printf("roundtrip:%n%s%n", writer.toString());

        final ResultSet rs = connection.prepareStatement(
                "SELECT array_to_json(array_agg(t)) FROM (SELECT addresses FROM my_table) t")
                .executeQuery();
        rs.next();
        System.out.printf("json:%n%s%n", rs.getString(1));
    }

}

Test output:

roundtrip:
"{""(10.0.0.1,1)"",""(10.0.0.2,2)""}"
"{""(10.10.10.1,80)"",""(10.10.10.2,443)""}"
"{""(10.10.10.3,8080)"",""(10.10.10.4,4040)""}"

json:
[{"addresses":[{"ip":"10.0.0.1","port":1},{"ip":"10.0.0.2","port":2}]},{"addresses":[{"ip":"10.10.10.1","port":80},{"ip":"10.10.10.2","port":443}]},{"addresses":[{"ip":"10.10.10.3","port":8080},{"ip":"10.10.10.4","port":4040}]}]

Can PostgreSQL COPY read CSV from a remote location?

If you use JDBC, the best solution for you is to use the PostgreSQL COPY API
http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html

Otherwise (as already noted by others) you can use \copy from psql which allows accessing the local files on the client machine

how to export data from postgresql to .csv file using jdbc?

The error shows ERROR: must be superuser to COPY to or from a file

It seems that the current user is not having enough permissions to write the file. Provide permissions to the write location for the user or execute the program from a user account which has sufficient privileges to write to the file.

PostgreSQL COPY FROM file vs STDIN

Use PipedInputStream and PipedOutputStream together, so you could read from source CSV file directly and then write to the inputStream which will be used in copyIn() Function.
Here is an example:

PipedInputStream is = new PipedInputStream();
PipedOutputStream os = new PipedOutputStream(is);
BufferedReader br = new BufferedReader(new InputStreamReader(is));
BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(os));
BufferedReader fr = new BufferedReader(new FileReader("path/to/file"));

Than read from fr and write to bw. I think you could write rest part of the codes. :)

---

Related Topics