SQL exception in COPY statement when the DELIMITER is set to a semicolon

[jensberke]

Hi,

when using the following COPY statement in PostgreSQL 9.1:

COPY CSV_FILES FROM '/path/to/filename.csv' DELIMITER ';' CSV HEADER;

Flyway 3.2.1 throws the following exception:

Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V2015_04_27_11_00__COPY.sql failed
SQL State : 42601
Error Code : 0
Message : ERROR: unterminated quoted string at or near "'"
Position: 55
Location : XXXXX/migrations/postgresql/V2015_04_27_11_00__COPY.sql (/home/XXXXX/postgresql/V2015_04_27_11_00__COPY.sql)
Line : 2
Statement : COPY CSV_FILES FROM '/path/to/filename.csv' DELIMITER ';' CSV HEADER;
at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:284)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:282)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:282)
at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006)
at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
at org.flywaydb.core.Flyway.migrate(Flyway.java:1006)
... 57 more
Caused by: org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "'"
Position: 55
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:966)
at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:728)
at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:52)
at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:161)
at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:146)
at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.executePgCopy(PostgreSQLDbSupport.java:112)
at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:112)
... 80 more

Switching to another DELIMITER like ',' works. It looks like the script parser thinks that the ';' after the DELIMITER keyword is the end of the statement. Since DELIMITER and a semicolon are both valid parts of a COPY statement, the script should check for the DELIMITER keyword and interpret the delimiter value correctly.

BTW: at least in Flyway 2.2.1 there was no problem with this COPY statement.

Regards
Jens

[axelfontaine]

Hmmm did/does this work at all? The only support we have is actually for COPY FROM STDIN...

[jensberke]

Yes, it definitely worked with 2.2.1, I just double-checked again. A quick debug session with 2.2.1 shows that the whole COPY statement was treated as one single line by the script parser and therefore executed without problem.

I think "PostgreSQLDbSupport.executePgCopy()" should try to distinguish between a COPY statement that uses STDIN (with special handling of the DATA block which comes after the semicolon) and other COPY statements (which should be treated like normal statements, I guess). If I find some time the forthcoming days I can try to come up with a solution.

[axelfontaine]

Good to know! Thanks. Yes, if that used to work, then yes, we should reenable support.