How to escape numeric column names in create table

phurrelmann · July 16, 2010, 10:10am

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:

Total time: 4 seconds

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

phurrelmann · July 16, 2010, 10:10am

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?

nvoxland · July 16, 2010, 10:10am

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

Nathan

jens_neuhalfen · July 16, 2010, 10:10am

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

nvoxland · July 16, 2010, 10:10am

(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

jens_neuhalfen · July 16, 2010, 10:10am

Nathan,

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

Jens

ben_lb · July 16, 2010, 10:10am

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

Topic		Replies	Views
Bug in renameColumn in 1.9.4 General Discussion	5	621	August 4, 2009
Using changeLog.xml from generateChangeLog causes java.sql.SQLSyntaxErrorException: type not found or user lacks privilege: NUMBER General Discussion	0	524	October 23, 2012
A few MSSQL Issues? General Discussion	10	565	May 14, 2009
generateChangeLog fails when a column name in the database contains a parenthesis General Discussion	0	336	October 17, 2012
H2 database regression 1.9.5 -> 2.0-rc5 General Discussion	2	637	August 19, 2010

How to escape numeric column names in create table

Related topics