New! – Try automated migration testing Learn more

Flyway Documentation

Poll

Redshift

Support Level

Compatible
Certified
Guaranteed Flyway Teams

Support Level determines the degree of support available for this database (learn more).

Driver

URL format jdbc:redshift://host:port/database
Ships with Flyway Command-line No
Download Follow the instructions at docs.aws.amazon.com
Supported versions 1.2.10.1009 and later
2.0.0.5 and later are partially supported (see below)
Default Java class com.amazon.redshift.jdbc42.Driver

SQL Script Syntax

  • Standard SQL syntax with statement delimiter ;
  • Stored procedures (CREATE FUNCTION with $$ escapes)

Compatibility

  • DDL exported by pg_dump can be used unchanged in a Flyway migration. Please note that Redshift does not support exporting data using pg_dump, so you must export only the schema, using pg_dump -s.
  • Any Redshift SQL script executed by Flyway, can be executed by the PostgreSQL command-line tool and other PostgreSQL-compatible tools, such as SQL Workbench/J (after the placeholders have been replaced).

Example

/* Single line comment */
CREATE TABLE test_data (
  test_id INT IDENTITY NOT NULL PRIMARY KEY,
  value VARCHAR(25) NOT NULL
);

/*
Multi-line
comment
*/
INSERT INTO test_data (value) VALUES ('Hello');

CREATE VIEW value_only AS SELECT value FROM test_data;

CREATE TABLE another_table AS SELECT 'some-data' as name;

CREATE FUNCTION add(integer, integer) RETURNS integer
     IMMUTABLE
    AS $$
    select $1 + $2;
$$ LANGUAGE sql;

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');

Limitations

Due to Redshift limitations ALTER TABLE and DROP TABLE for external tables cannot run within a transaction, yet Flyway doesn’t autodetect this. You can work around this limitation and successfully execute such a statement by including a VACUUM statement in the same SQL file as this will force Flyway to run the entire migration without a transaction.

The v2 driver is only supported from v2.0.0.5, and only then by Flyway setting the connection property enableFetchRingBuffer=false. We recommend using the latest v1 driver for the time being. See here for more details.

CockroachDB