List all liquibase sql types
This is a comprehensive list of all liquibase datatypes and how they are converted for different databases:
booleanMySQLDatabase: BIT(1)SQLiteDatabase: BOOLEANH2Database: BOOLEANPostgresDatabase: BOOLEANUnsupportedDatabase: BOOLEANDB2Database: SMALLINTMSSQLDatabase: [bit]OracleDatabase: NUMBER(1)HsqlDatabase: BOOLEANFirebirdDatabase: SMALLINTDerbyDatabase: SMALLINTInformixDatabase: BOOLEANSybaseDatabase: BITSybaseASADatabase: BITtinyintMySQLDatabase: TINYINTSQLiteDatabase: TINYINTH2Database: TINYINTPostgresDatabase: SMALLINTUnsupportedDatabase: TINYINTDB2Database: SMALLINTMSSQLDatabase: [tinyint]OracleDatabase: NUMBER(3)HsqlDatabase: TINYINTFirebirdDatabase: SMALLINTDerbyDatabase: SMALLINTInformixDatabase: TINYINTSybaseDatabase: TINYINTSybaseASADatabase: TINYINTintMySQLDatabase: INTSQLiteDatabase: INTEGERH2Database: INTPostgresDatabase: INTUnsupportedDatabase: INTDB2Database: INTEGERMSSQLDatabase: [int]OracleDatabase: INTEGERHsqlDatabase: INTFirebirdDatabase: INTDerbyDatabase: INTEGERInformixDatabase: INTSybaseDatabase: INTSybaseASADatabase: INTmediumintMySQLDatabase: MEDIUMINTSQLiteDatabase: MEDIUMINTH2Database: MEDIUMINTPostgresDatabase: MEDIUMINTUnsupportedDatabase: MEDIUMINTDB2Database: MEDIUMINTMSSQLDatabase: [int]OracleDatabase: MEDIUMINTHsqlDatabase: MEDIUMINTFirebirdDatabase: MEDIUMINTDerbyDatabase: MEDIUMINTInformixDatabase: MEDIUMINTSybaseDatabase: MEDIUMINTSybaseASADatabase: MEDIUMINTbigintMySQLDatabase: BIGINTSQLiteDatabase: BIGINTH2Database: BIGINTPostgresDatabase: BIGINTUnsupportedDatabase: BIGINTDB2Database: BIGINTMSSQLDatabase: [bigint]OracleDatabase: NUMBER(38, 0)HsqlDatabase: BIGINTFirebirdDatabase: BIGINTDerbyDatabase: BIGINTInformixDatabase: INT8SybaseDatabase: BIGINTSybaseASADatabase: BIGINTfloatMySQLDatabase: FLOATSQLiteDatabase: FLOATH2Database: FLOATPostgresDatabase: FLOATUnsupportedDatabase: FLOATDB2Database: FLOATMSSQLDatabase: [float](53)OracleDatabase: FLOATHsqlDatabase: FLOATFirebirdDatabase: FLOATDerbyDatabase: FLOATInformixDatabase: FLOATSybaseDatabase: FLOATSybaseASADatabase: FLOATdoubleMySQLDatabase: DOUBLESQLiteDatabase: DOUBLEH2Database: DOUBLEPostgresDatabase: DOUBLE PRECISIONUnsupportedDatabase: DOUBLEDB2Database: DOUBLEMSSQLDatabase: [float](53)OracleDatabase: FLOAT(24)HsqlDatabase: DOUBLEFirebirdDatabase: DOUBLE PRECISIONDerbyDatabase: DOUBLEInformixDatabase: DOUBLE PRECISIONSybaseDatabase: DOUBLESybaseASADatabase: DOUBLEdecimalMySQLDatabase: DECIMALSQLiteDatabase: DECIMALH2Database: DECIMALPostgresDatabase: DECIMALUnsupportedDatabase: DECIMALDB2Database: DECIMALMSSQLDatabase: [decimal](18, 0)OracleDatabase: DECIMALHsqlDatabase: DECIMALFirebirdDatabase: DECIMALDerbyDatabase: DECIMALInformixDatabase: DECIMALSybaseDatabase: DECIMALSybaseASADatabase: DECIMALnumberMySQLDatabase: numericSQLiteDatabase: NUMBERH2Database: NUMBERPostgresDatabase: numericUnsupportedDatabase: NUMBERDB2Database: numericMSSQLDatabase: [numeric](18, 0)OracleDatabase: NUMBERHsqlDatabase: numericFirebirdDatabase: numericDerbyDatabase: numericInformixDatabase: numericSybaseDatabase: numericSybaseASADatabase: numericblobMySQLDatabase: LONGBLOBSQLiteDatabase: BLOBH2Database: BLOBPostgresDatabase: BYTEAUnsupportedDatabase: BLOBDB2Database: BLOBMSSQLDatabase: [varbinary](MAX)OracleDatabase: BLOBHsqlDatabase: BLOBFirebirdDatabase: BLOBDerbyDatabase: BLOBInformixDatabase: BLOBSybaseDatabase: IMAGESybaseASADatabase: LONG BINARYfunctionMySQLDatabase: FUNCTIONSQLiteDatabase: FUNCTIONH2Database: FUNCTIONPostgresDatabase: FUNCTIONUnsupportedDatabase: FUNCTIONDB2Database: FUNCTIONMSSQLDatabase: [function]OracleDatabase: FUNCTIONHsqlDatabase: FUNCTIONFirebirdDatabase: FUNCTIONDerbyDatabase: FUNCTIONInformixDatabase: FUNCTIONSybaseDatabase: FUNCTIONSybaseASADatabase: FUNCTIONUNKNOWNMySQLDatabase: UNKNOWNSQLiteDatabase: UNKNOWNH2Database: UNKNOWNPostgresDatabase: UNKNOWNUnsupportedDatabase: UNKNOWNDB2Database: UNKNOWNMSSQLDatabase: [UNKNOWN]OracleDatabase: UNKNOWNHsqlDatabase: UNKNOWNFirebirdDatabase: UNKNOWNDerbyDatabase: UNKNOWNInformixDatabase: UNKNOWNSybaseDatabase: UNKNOWNSybaseASADatabase: UNKNOWNdatetimeMySQLDatabase: datetimeSQLiteDatabase: TEXTH2Database: TIMESTAMPPostgresDatabase: TIMESTAMP WITHOUT TIME ZONEUnsupportedDatabase: datetimeDB2Database: TIMESTAMPMSSQLDatabase: [datetime]OracleDatabase: TIMESTAMPHsqlDatabase: TIMESTAMPFirebirdDatabase: TIMESTAMPDerbyDatabase: TIMESTAMPInformixDatabase: DATETIME YEAR TO FRACTION(5)SybaseDatabase: datetimeSybaseASADatabase: datetimetimeMySQLDatabase: timeSQLiteDatabase: timeH2Database: timePostgresDatabase: TIME WITHOUT TIME ZONEUnsupportedDatabase: timeDB2Database: timeMSSQLDatabase: [time](7)OracleDatabase: DATEHsqlDatabase: timeFirebirdDatabase: timeDerbyDatabase: timeInformixDatabase: INTERVAL HOUR TO FRACTION(5)SybaseDatabase: timeSybaseASADatabase: timetimestampMySQLDatabase: timestampSQLiteDatabase: TEXTH2Database: TIMESTAMPPostgresDatabase: TIMESTAMP WITHOUT TIME ZONEUnsupportedDatabase: timestampDB2Database: timestampMSSQLDatabase: [datetime]OracleDatabase: TIMESTAMPHsqlDatabase: TIMESTAMPFirebirdDatabase: TIMESTAMPDerbyDatabase: TIMESTAMPInformixDatabase: DATETIME YEAR TO FRACTION(5)SybaseDatabase: datetimeSybaseASADatabase: timestampdateMySQLDatabase: dateSQLiteDatabase: dateH2Database: datePostgresDatabase: dateUnsupportedDatabase: dateDB2Database: dateMSSQLDatabase: [date]OracleDatabase: dateHsqlDatabase: dateFirebirdDatabase: dateDerbyDatabase: dateInformixDatabase: dateSybaseDatabase: dateSybaseASADatabase: datecharMySQLDatabase: CHARSQLiteDatabase: CHARH2Database: CHARPostgresDatabase: CHARUnsupportedDatabase: CHARDB2Database: CHARMSSQLDatabase: [char](1)OracleDatabase: CHARHsqlDatabase: CHARFirebirdDatabase: CHARDerbyDatabase: CHARInformixDatabase: CHARSybaseDatabase: CHARSybaseASADatabase: CHARvarcharMySQLDatabase: VARCHARSQLiteDatabase: VARCHARH2Database: VARCHARPostgresDatabase: VARCHARUnsupportedDatabase: VARCHARDB2Database: VARCHARMSSQLDatabase: [varchar](1)OracleDatabase: VARCHAR2HsqlDatabase: VARCHARFirebirdDatabase: VARCHARDerbyDatabase: VARCHARInformixDatabase: VARCHARSybaseDatabase: VARCHARSybaseASADatabase: VARCHARncharMySQLDatabase: NCHARSQLiteDatabase: NCHARH2Database: NCHARPostgresDatabase: NCHARUnsupportedDatabase: NCHARDB2Database: NCHARMSSQLDatabase: [nchar](1)OracleDatabase: NCHARHsqlDatabase: CHARFirebirdDatabase: NCHARDerbyDatabase: NCHARInformixDatabase: NCHARSybaseDatabase: NCHARSybaseASADatabase: NCHARnvarcharMySQLDatabase: NVARCHARSQLiteDatabase: NVARCHARH2Database: NVARCHARPostgresDatabase: VARCHARUnsupportedDatabase: NVARCHARDB2Database: NVARCHARMSSQLDatabase: [nvarchar](1)OracleDatabase: NVARCHAR2HsqlDatabase: VARCHARFirebirdDatabase: NVARCHARDerbyDatabase: VARCHARInformixDatabase: NVARCHARSybaseDatabase: NVARCHARSybaseASADatabase: NVARCHARclobMySQLDatabase: LONGTEXTSQLiteDatabase: TEXTH2Database: CLOBPostgresDatabase: TEXTUnsupportedDatabase: CLOBDB2Database: CLOBMSSQLDatabase: [varchar](MAX)OracleDatabase: CLOBHsqlDatabase: CLOBFirebirdDatabase: BLOB SUB_TYPE TEXTDerbyDatabase: CLOBInformixDatabase: CLOBSybaseDatabase: TEXTSybaseASADatabase: LONG VARCHARcurrencyMySQLDatabase: DECIMALSQLiteDatabase: REALH2Database: DECIMALPostgresDatabase: DECIMALUnsupportedDatabase: DECIMALDB2Database: DECIMAL(19, 4)MSSQLDatabase: [money]OracleDatabase: NUMBER(15, 2)HsqlDatabase: DECIMALFirebirdDatabase: DECIMAL(18, 4)DerbyDatabase: DECIMALInformixDatabase: MONEYSybaseDatabase: MONEYSybaseASADatabase: MONEYuuidMySQLDatabase: char(36)SQLiteDatabase: TEXTH2Database: UUIDPostgresDatabase: UUIDUnsupportedDatabase: char(36)DB2Database: char(36)MSSQLDatabase: [uniqueidentifier]OracleDatabase: RAW(16)HsqlDatabase: char(36)FirebirdDatabase: char(36)DerbyDatabase: char(36)InformixDatabase: char(36)SybaseDatabase: UNIQUEIDENTIFIERSybaseASADatabase: UNIQUEIDENTIFIERFor reference, this is the groovy script I've used to generate this output:
@Grab('org.liquibase:liquibase-core:3.5.1')import liquibase.database.core.*import liquibase.datatype.core.*def datatypes = [BooleanType,TinyIntType,IntType,MediumIntType,BigIntType,FloatType,DoubleType,DecimalType,NumberType,BlobType,DatabaseFunctionType,UnknownType,DateTimeType,TimeType,TimestampType,DateType,CharType,VarcharType,NCharType,NVarcharType,ClobType,CurrencyType,UUIDType]def databases = [MySQLDatabase, SQLiteDatabase, H2Database, PostgresDatabase, UnsupportedDatabase, DB2Database, MSSQLDatabase, OracleDatabase, HsqlDatabase, FirebirdDatabase, DerbyDatabase, InformixDatabase, SybaseDatabase, SybaseASADatabase]datatypes.each { def datatype = it.newInstance() datatype.finishInitialization("") println datatype.name databases.each { println "$it.simpleName: ${datatype.toDatabaseDataType(it.newInstance())}"} println ''}
I've found the liquibase.database.typeconversion.core.AbstractTypeConverter class.It lists all types that can be used:
protected DataType getDataType(String columnTypeString, Boolean autoIncrement, String dataTypeName, String precision, String additionalInformation) { // Translate type to database-specific type, if possible DataType returnTypeName = null; if (dataTypeName.equalsIgnoreCase("BIGINT")) { returnTypeName = getBigIntType(); } else if (dataTypeName.equalsIgnoreCase("NUMBER") || dataTypeName.equalsIgnoreCase("NUMERIC")) { returnTypeName = getNumberType(); } else if (dataTypeName.equalsIgnoreCase("BLOB")) { returnTypeName = getBlobType(); } else if (dataTypeName.equalsIgnoreCase("BOOLEAN")) { returnTypeName = getBooleanType(); } else if (dataTypeName.equalsIgnoreCase("CHAR")) { returnTypeName = getCharType(); } else if (dataTypeName.equalsIgnoreCase("CLOB")) { returnTypeName = getClobType(); } else if (dataTypeName.equalsIgnoreCase("CURRENCY")) { returnTypeName = getCurrencyType(); } else if (dataTypeName.equalsIgnoreCase("DATE") || dataTypeName.equalsIgnoreCase(getDateType().getDataTypeName())) { returnTypeName = getDateType(); } else if (dataTypeName.equalsIgnoreCase("DATETIME") || dataTypeName.equalsIgnoreCase(getDateTimeType().getDataTypeName())) { returnTypeName = getDateTimeType(); } else if (dataTypeName.equalsIgnoreCase("DOUBLE")) { returnTypeName = getDoubleType(); } else if (dataTypeName.equalsIgnoreCase("FLOAT")) { returnTypeName = getFloatType(); } else if (dataTypeName.equalsIgnoreCase("INT")) { returnTypeName = getIntType(); } else if (dataTypeName.equalsIgnoreCase("INTEGER")) { returnTypeName = getIntType(); } else if (dataTypeName.equalsIgnoreCase("LONGBLOB")) { returnTypeName = getLongBlobType(); } else if (dataTypeName.equalsIgnoreCase("LONGVARBINARY")) { returnTypeName = getBlobType(); } else if (dataTypeName.equalsIgnoreCase("LONGVARCHAR")) { returnTypeName = getClobType(); } else if (dataTypeName.equalsIgnoreCase("SMALLINT")) { returnTypeName = getSmallIntType(); } else if (dataTypeName.equalsIgnoreCase("TEXT")) { returnTypeName = getClobType(); } else if (dataTypeName.equalsIgnoreCase("TIME") || dataTypeName.equalsIgnoreCase(getTimeType().getDataTypeName())) { returnTypeName = getTimeType(); } else if (dataTypeName.toUpperCase().contains("TIMESTAMP")) { returnTypeName = getDateTimeType(); } else if (dataTypeName.equalsIgnoreCase("TINYINT")) { returnTypeName = getTinyIntType(); } else if (dataTypeName.equalsIgnoreCase("UUID")) { returnTypeName = getUUIDType(); } else if (dataTypeName.equalsIgnoreCase("VARCHAR")) { returnTypeName = getVarcharType(); } else if (dataTypeName.equalsIgnoreCase("NVARCHAR")) { returnTypeName = getNVarcharType(); } else { return new CustomType(columnTypeString,0,2); }
Well, since liquibase is open source there's always the source code which you could check.
Some of the data type classes seem to have a method toDatabaseDataType() which should give you information about what type works (is used) on a specific data base.