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.

Answer 1

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

Answer 2

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.