How to escape numeric column names in create table

General Discussion
1

I have a table with numeric column names. Hibernate was able to escape them and produce proper sql.
Liquibase fails when trying to perform the db change and creating the table with the names.

The changeset:

                                                                                           

being executed via ant:

    BUILD FAILED liquibase.exception.MigrationFailedException: Migration failed for change set /home/phurrelmann/svn/trunk/logic/hibernate-changelog.xml::test0815::phurrelmann:     Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE test (test_id bigserial NOT NULL, 1_columnx VARCHAR(255), 2_columny VARCHAR(255), CONSTRAINT "testPK" PRIMARY KEY (test_id)):          Caused By: Error executing SQL CREATE TABLE test (test_id bigserial NOT NULL, 1_columnx VARCHAR(255), 2_columny VARCHAR(255), CONSTRAINT "testPK" PRIMARY KEY (test_id)):          Caused By: ERROR: syntax error at or near "1"  Position: 48 at liquibase.changelog.ChangeSet.execute(ChangeSet.java:281) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:39) at liquibase.Liquibase.update(Liquibase.java:112) at liquibase.integration.ant.DatabaseUpdateTask.execute(DatabaseUpdateTask.java:45) at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106) at org.apache.tools.ant.Task.perform(Task.java:348) at org.apache.tools.ant.Target.execute(Target.java:357) at org.apache.tools.ant.Target.performTasks(Target.java:385) at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337) at org.apache.tools.ant.Project.executeTarget(Project.java:1306) at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41) at org.apache.tools.ant.Project.executeTargets(Project.java:1189) at org.apache.tools.ant.Main.runBuild(Main.java:758) at org.apache.tools.ant.Main.startAnt(Main.java:217) at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257) at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104) Caused by: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE test (test_id bigserial NOT NULL, 1_columnx VARCHAR(255), 2_columny VARCHAR(255), CONSTRAINT "testPK" PRIMARY KEY (test_id)) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:61) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:92) at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:960) at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:947) at liquibase.changelog.ChangeSet.execute(ChangeSet.java:252) ... 21 more Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "1"  Position: 48 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345) at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:83) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:54) ... 25 more

    Total time: 4 seconds

How can the column names be escaped/quoted? This is on postgreSQL 8.4, btw.

2

Ok, I digged some more and created a little patch for the postgresql integration that fixes the issue.
Mysql worked fine with the changeset. Postgresql migration works fine after patching and building from svn, too.

Maybe this is helpful and finds it’s way upstream? :slight_smile:

3

Thanks for the patch.  It will be in the RC3 release that I am trying to release right now.

Nathan

4

Hi Nathan,

I am working with liquibase-2.0-rc5 and I am having a similar issue with Oracle:
A column name is “start”, which is an identifier in Oracle. When I quote the column name in a manual CREATE TABLE statement, it all works.

Is there a possibility  to use phurrelmann’s patch for Oracle as well?

Regards & thanks a bunch for this Liquibase

Jens

5

(sorry for the slow response, catching up)

I committed a fix for oracle for RC6 that will surround all oracle objects with "s which should solve your problem.  Oracle doesn’t have the case sensitivity issues that other databases have, so escaping all should be just fine.

Nathan

6

Nathan,

thanks a lot, and there is no need to excuse yourself!

Jens

7
Originally posted by: Nathan
I committed a fix for oracle for RC6 that will surround all oracle objects with "s which should solve your problem.  Oracle doesn't have the case sensitivity issues that other databases have, so escaping all should be just fine.

Hi

This statement isn’t strictly true - if you quote an identifier in Oracle - then you must always refer to that identifier quoted. So any existing database objects that have been created prior to this change will fail. But more importantly, all standard application code, PL/SQL code/triggers/you_name_it will need changing.

I’ve created an issue to look at resolving this:

http://liquibase.jira.com/browse/CORE-740

The patch could be extended so that all Oracle reserved words are also auto-quoted to resolve Jen’s issue - but even this could cause problems for users’ applications which are still using unquoted reserved words in their code.

Perhaps a better solution would be to support a new parameter on the column tag which instructed Liquibase to quote that particular column.

Quoting in Oracle should be the exception rather than the norm.

Ben