Does flyway migrations support PostgreSQL's COPY?

Question

Having performed a pg_dump of an existing posgresql schema, I have an sql file containing a number of table population statements using the copy.

COPY test_table (id, itm, factor, created_timestamp, updated_timestamp, updated_by_user, version) FROM stdin;
1   600 0.000   2012-07-17 18:12:42.360828  2012-07-17 18:12:42.360828  system  0
2   700 0.000   2012-07-17 18:12:42.360828  2012-07-17 18:12:42.360828  system  0
\.

Though not standard this is part of PostgreSQL's PLSQL implementation.

Performing a flyway migration (via the maven plugin) I get:

[ERROR] Caused by org.postgresql.util.PSQLException: ERROR: unexpected message type 0x50 during COPY from stein

Am I doing something wrong, or is this just not supported?

Thanks.

Axel Fontaine · Accepted Answer · 2015-04-24T07:34:39.303

7

The short answer is no.

The one definite problem is that the parser is currently not able to deal with this special construct.

The other question is jdbc driver support. Could you try and see if this syntax generally supported by the jdbc driver with a single createStatement call?

If it is, please file an issue in the issue tracker and I'll extend the parser.

Update: This is now supported

edited Apr 24 '15 at 07:34

answered Jul 18 '12 at 09:15

Axel Fontaine

34,542
16
106
137

As far as I know, the JDBC driver does not support `COPY .. FROM stdin`. Only COPY from a file. – Jul 18 '12 at 09:38
The JDBC driver does seem to support the `COPY ... FROM stdin` construct, looking at the classes in the `org.postgresql.copy` package. – araqnid Jul 18 '12 at 09:46
Thanks - the issues I was having made me re-evaluate my approach. However, now I have had a chance to dig a bit further into this, it seems it does not have "simple" JDBC support, being via a CopyManager [answer showing example](http://stackoverflow.com/questions/6958965/how-to-copy-a-data-from-file-to-postgresql-using-jdbc) – englishteeth Jul 18 '12 at 20:05
By the way - the short answer was exactly what I was after ;) thanks – englishteeth Jul 18 '12 at 20:08
4

The current version seems to support it somehow.. see http://flywaydb.org/documentation/database/postgresql.html – Tarnschaf Apr 24 '15 at 07:17

score 2 · Answer 2 · answered Jun 19 '15 at 15:51

I have accomplished this for Postgres using

public abstract class SeedData implements JdbcMigration {

    protected static final String CSV_COPY_STRING = "COPY %s(%s) FROM STDIN HEADER DELIMITER ',' CSV ENCODING 'UTF-8'";

    protected CopyManager copyManager;

    @Override
    public void migrate(Connection connection) throws Exception {
        log.info(String.format("[%s] Populating database with seed data", getClass().getName()));
        copyManager = new CopyManager((BaseConnection) connection);

        Resource[] resources = scanForResources();

        List<Resource> res = Arrays.asList(resources);
        for (Resource resource : res) {
            load(resource);
        }
    }

    private void load(Resource resource) throws SQLException, IOException {
        String location = resource.getLocation();

        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(location);
        if (inputStream == null) {
            throw new FlywayException("Failure to load seed data. Unable to load from location: " + location);
        }
        if (!inputStream.markSupported()) {
            // Sanity check. We have to be able to mark the stream.
            throw new FlywayException(
                    "Failure to load seed data as mark is not supported. Unable to load from location: " + location);
        }
        // set our mark to something big
        inputStream.mark(1 << 32);

        String filename = resource.getFilename();
        // Strip the prefix (e.g. 01_) and the file extension (e.g. .csv)
        String table = filename.substring(3, filename.length() - 4);
        String columns = loadCsvHeader(location, inputStream);

        // reset to the mark
        inputStream.reset();

        // Use Postgres COPY command to bring it in
        long result = copyManager.copyIn(String.format(CSV_COPY_STRING, table, columns), inputStream);
        log.info(format("   %s - Inserted %d rows", location, result));
    }

    private String loadCsvHeader(String location, InputStream inputStream) {
        try {
            return new BufferedReader(new InputStreamReader(inputStream)).readLine();
        } catch (IOException e) {
            throw new FlywayException("Failure to load seed data. Unable to load from location: " + location, e);
        }
    }

    private Resource[] scanForResources() throws IOException {
        return new ClassPathScanner(getClass().getClassLoader()).scanForResources(getSeedDataLocation(), "", ".csv");
    }

    protected String getSeedDataLocation() {
        return getClass().getPackage().getName().replace('.', '/');
    }

}

To use implement the class with the appropriate classpath

package db.devSeedData.dev;

public class v0_90__seed extends db.devSeedData.v0_90__seed {
}

All that is needed then is to have CSV files in your classpath under db/devSeedData that follow the format 01_tablename.csv. Columns are extracted from the header line of the CSV.

Does flyway migrations support PostgreSQL's COPY?

2 Answers2