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

Question

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

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
– Kuberchaun, Commented Mar 24, 2011 at 14:10
Can't you fix this bug in Liquibase? That's where the real problem is, Liquibase creates the wrong SQL. — Frank Heikens
– Frank Heikens, Commented Mar 24, 2011 at 18:34

message · Accepted Answer · 2014-01-11 16:08:27Z

12

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>

edited Jan 11, 2014 at 16:08

message

4,6133 gold badges31 silver badges40 bronze badges

answered May 15, 2011 at 19:01

magomarcelo

3702 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Humber Over a year ago

Thanks! It works great. One tip: The modifySql element goes inside the changeSet element and works across the whole changeSet. However, it was valid only if I place the modifySql element inside but at the end of the changeSet. And the current link is liquibase.org/documentation/modify_sql.html

Kuberchaun · Accepted Answer · 2013-05-27 12:48:56Z

3

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.

edited May 27, 2013 at 12:48

answered Mar 25, 2011 at 14:58

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Comments

F. Müller · Accepted Answer · 2020-11-11 10:41:33Z

2

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>

edited Nov 11, 2020 at 10:41

F. Müller

4,0629 gold badges44 silver badges53 bronze badges

answered Nov 11, 2020 at 10:20

Oskarro

4133 silver badges10 bronze badges

Comments

Nathan Voxland · Accepted Answer · 2011-03-25 21:00:24Z

1

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

edited Mar 25, 2011 at 21:00

answered Mar 24, 2011 at 21:50

Nathan Voxland

15.9k2 gold badges51 silver badges66 bronze badges

2 Comments

Kuberchaun Over a year ago

@sword101 I think this is what you need to look at liquibase.org/manual/custom_sql.

Nathan Voxland Over a year ago

@StarShip3000 Yes, I forgot to escape the angle brackets around the sql tag and so it didn't show. Sorry about that

Mistriel · Accepted Answer · 2019-02-05 23:45:30Z

0

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

answered Feb 5, 2019 at 23:45

Mistriel

5395 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related