Force Liquibase to map Blob to BYTEA on PostgreSQL

Question

How to tell Liquibase to map BLOB datatype to BYTEA on PostgreSQL?

It seems that Hibernate people has taken over and adapted the tool to their needs: https://liquibase.jira.com/browse/CORE-1863 , however, EclipseLink don't support oid's and the bug seems to be still open: https://bugs.eclipse.org/bugs/show_bug.cgi?id=337467

I need to use EclipseLink, and I need to use blobs with PostgreSQL. I'd like to use Liquibase, is it possible to make those things work together?

If you only use your changelog for Postgres, simply use `bytea` as the column type instead of `blob` — , Feb 22 '17 at 11:06
@a_horse_with_no_name I was already thinking of putting block but your solution works nice. I thought only 'supported' types are supported by liquibase, their documentation isn't especially intuitive. — Danubian Sailor, Feb 22 '17 at 11:17

score 23 · Accepted Answer · 2017-02-22T11:39:22.250

23

You have two options.

If you only need this for Postgres and don't plan to support other DBMS, simply use bytea as the column type.

Any data type that is not listed as one of the "generic" types in the description of the column tag will be passed "as-is" to the database, e.g.

<createTable tableName="foo">
  <column name="id" type="integer"/> 
  <column name="picture" type="bytea"/>
</createTable>

If you want to support different DBMS, you can define a property depending on the DBMS:

<property name="blob_type" value="bytea" dbms="postgresql"/>
<property name="blob_type" value="blob" dbms="oracle"/>

then later

<createTable tableName="foo">
  <column name="id" type="integer"/> 
  <column name="picture" type="${blob_type}"/>
</createTable>

edited Feb 22 '17 at 11:39

answered Feb 22 '17 at 11:30

1

For liquibase 3.4, I didn't have to to do this. I just put the column type as BLOB and then liquibase would generate column as BLOB for oracle and BYTEA for PostgreSQL. We're generating schema in both the postgres and oracle, so liquibase takes care if BLOB is used as column type. – Mital Pritmani Jan 28 '19 at 06:38
3

yes, prior liquibase version 3.5.2 it changed default blob column type to oid instead bytea https://liquibase.jira.com/browse/CORE-1863 – jtomaszk Apr 16 '19 at 11:01
1

Just came to this point, nasty breaking change going from liquibase 3.5.1 to 4.5.0 ! – consultantleon Nov 04 '21 at 12:42

1 Answers1