generateChangeLog fails using postgres when any column name is "default"

General Discussion
1

Hi everybody.

Theese are my first steps with liquibase and java at all. Trying to use liquibase in my project i’m unable to generate initial changelog - the problem is probably name of one of columns which is “default”. Table belongs to 3rd party app (django notification), and let’s assume it can not be changed. 


I use:

  • Liquibase Version: 2.0.1

  • postgresql-9.1

  • postgresql-9.1-901.jdbc3.jar postgresql-jdbc3-8.2.jar - no matter, problem remains

I’m getting following errors:

  1. Liquibase Update Failed: ERROR: syntax error at or near "default"
    SEVERE 05.03.12 12:44:liquibase: ERROR: syntax error at or near "default"
    liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "default"
    at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.createSnapshot(JdbcDatabaseSnapshotGenerator.java:251)
    at liquibase.snapshot.DatabaseSnapshotGeneratorFactory.createSnapshot(DatabaseSnapshotGeneratorFactory.java:69)
    at liquibase.diff.Diff.compare(Diff.java:63)
    at liquibase.integration.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:145)
    at liquibase.integration.commandline.Main.doMigration(Main.java:624)
    at liquibase.integration.commandline.Main.main(Main.java:116)
    Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "default"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)
    at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.isColumnAutoIncrement(JdbcDatabaseSnapshotGenerator.java:842)
    at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.readColumns(JdbcDatabaseSnapshotGenerator.java:369)
      at liquibase.snapshot.jvm.JdbcDatabaseSnapshotGenerator.createSnapshot(JdbcDatabaseSnapshotGenerator.java:244)... 5 more

Examining pg log i got:

  1. Mar 5 12:24:21 xxx postgres[13621]: [1044-1] ERROR: syntax error at or near “default” at character 8

  2. Mar 5 12:24:21 xxx postgres[13621]: [1044-2] STATEMENT: SELECT default FROM notification_noticetype WHERE 1 = 0

Query with double quoted column name passes OK returning 0 rows as expected.


Error semms to conform with http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html


Any ideas?

Thanx in advance ;).

2

Some related stuff:

3

Default should be quoted but is not being. We had issues earlier when we automatically quoted everything in postgres which then makes everything case sensitive, then went to only quoting keywords but were too broad on our keyword list and still made some tables unexpectedly case sensitive. The current list is probably too likely to run into problems like you are seeing, however.


One option is to quote the table name in your changelog file ()


Otherewise, you can override the PostgresDatabase class’s isReservedWord() method using the extension system (liquibase.org/extensions).


I’ll add default to the list of reserved postgres words, but that won’t go out until liquibase 3.0 which may be a month or more.


Nathan