Create a timestamp with time zone in PostgreSQL from Liquibase XML

Question

I have a Liquibase migration XML file which creates columns with the datetime type.

<createTable tableName="foo">
    <column name="bar" type="datetime"/>
</createTable>

I realized to my consternation today that these are being created without a timezone (timestamp without time zone in PostgreSQL) and there doesn't seem to be any Liquibase type that you can use that will give you a timestamp with time zone. Is there any way to deal with this beyond having an <sql> block that alters the table like so after initially creating the table:

<sql>
alter table foo alter column bar type timestamp with time zone;
</sql>

Thanks.

Answer 1

TL;DR: use timestamp with time zone:

<column name="bar" type="timestamp with time zone"/>

Liquibase datetime will be automatically converted to the target database's timestamp type, but it is not possible to specify a time zone.

Liquibase will accept timestamp with time zone and pass it as a native type (no conversion), but as it is SQL standard type, it will be accepted by any standard database anyway (including PostgreSQL).

timestamptz is PostgreSQL-specific abbreviation for the same data type. It is not portable.

Answer 2

The answer, which was provided in a comment by https://stackoverflow.com/users/330315/a-horse-with-no-name, is to use the timestamptz native PostgreSQL type:

<createTable tableName="foo">
    <column name="bar" type="timestamptz"/>
</createTable>