Cannot create column with type "TIMESTAMP WITHOUT TIME ZONE" in PostgreSQL

6
votes

when i tried to create a column with the data type "TIMESTAMP WITHOUT TIME ZONE" in postgresql it's always created in the database as "TIMESTAMP WITH TIME ZONE" , so is there's any workarounds or solutions for this problem ?

<addColumn tableName="myTable">
            <column name="date_added" type="TIMESTAMP WITHOUT TIME ZONE">
            <constraints nullable="false" />
            </column>
 </addColumn>

btw, this issue is on jira: http://liquibase.jira.com/browse/CORE-877

5
postgresqlliquibase
Keep in mind Liquibase is interpreting XML tags into PostgreSQL SQL commands. To be sure what SQL is being executed run Liquibase with the updateSQL option to see what the actual SQL being generated is. - Kuberchaun
Can't you fix this bug in Liquibase? That's where the real problem is, Liquibase creates the wrong SQL. - Frank Heikens

5 Answers

12
votes

Instead of using the tag and switch completely from XML to sql, you could modify the resulting generated SQL using the tag which is valid across the whole changeset: http://www.liquibase.org/documentation/modify_sql.html

for example in your case you would have this:

<modifySql dbms="postgresql">
    <replace replace="WITH" with="WITHOUT"/>
</modifySql>
2
votes

Read this page http://www.liquibase.org/documentation/sql_format.html. just manually type in the needed SQL exactly how you want it if you use the SQL format with Liquibase.

1
votes

You could use the <sql> tag to create the exact SQL you are wanting if liquibase is generating the wrong SQL for you.

0
votes

In addition to @magomarcelo you can also write modifysql as a YAML. The following example ignores unsigned in postgresql:

- modifySql:
    dbms: postgresql
    replace:
      replace: unsigned
      with: default 0
0
votes

After a long time since this query, liquibase team has already corrected this problem and now you can add following column type:

<column name="created_at" type="TIMESTAMP WITHOUT TIME ZONE">
    <constraints nullable="true"/>
</column>