Making case insensitive table with liquibase in postgres

Question

Does anybady know how to make case insensitive table with liquibase. I'm using the newest postgres. For example liquibase creates table in that way:

create table "Users" (
  "userId" integer unique not null,
  "userFirstName" varchar(50) not null,
  "userLastName" varchar(50) not null
);

but how to make liquibase to create table in that way:

create table Users (
  userId integer unique not null,
  userFirstName varchar(50) not null,
  userLastName varchar(50) not null
);

Answer 1

You can always use the tag to specify the exact SQL you want executed if liquibase is not generating what you want.

The default postgres support uses quotes around table and column names all the time so there will not be problems with using reserved words as object names. You can override this by creating your own liquibase.database.core.PostgresDatabase subclass and having liquibase use your class instead. There is an escapeDatabaseObject(String) method that is passed in the original string and returns the quoted value. You would just need to override this method to return the original string untouched.

How you use your database depends on your version of liquibase. If you are using a snapshot of the upcoming 2.0 release (http://liquibase.org/ci/latest), you simply need to keep your class in a liquibase.database.ext package. If you are using 1.9, there should be a databaseClassName parameter you can use to tell liquibase about your class.

Answer 2

Since Liquibase 3.0 there is an attribute objectQuotingStrategy for the databaseChangeLog. The default is LEGACY but it can be changed to QUOTE_ONLY_RESERVED_WORDS which mostly turns a changelog to become case insensitive for PostgreSQL:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd" 
        objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">

The attribute is also available for a particular changeSet, but I am not sure as it may change the checksum of the change set. You can also use it with YAML config of course.

The PostgreSQL behaviour is explained in About PostgreSQL Case Sensitivity.