ContextQMD
Back to Flyway

Aurora PostgreSQL

documentation/Reference/Database Driver Reference/Aurora PostgreSQL.md

latest3.2 KB
Original Source
  • Verified Versions: 9.6, 14.x, 17.4
  • Maintainer: {% include redgate-badge.html %}

Supported Versions and Support Levels

{% include database-boilerplate.html %}

Driver

ItemDetails
URL format<code>jdbc:postgresql://<i>instance</i>.<i>region</i>.rds.amazonaws.com:<i>port</i>/<i>database</i></code>
SSL supportYes - add ?ssl=true
Ships with Flyway Command-lineYes
Maven Central coordinatesorg.postgresql:postgresql
Supported versions9.3-1104-jdbc4 and later
Default Java classorg.postgresql.Driver

SQL Script Syntax

  • Standard SQL syntax with statement delimiter ;
  • Stored procedures (CREATE FUNCTION with $$ escapes, as generated by pg_dump)
  • COPY ... FROM STDIN (as generated by pg_dump)

Compatibility

  • DDL exported by pg_dump can be used unchanged in a Flyway migration.
  • Any PostgreSQL sql script executed by Flyway, can be executed by the PostgreSQL command-line tool and other PostgreSQL-compatible tools (after the placeholders have been replaced).

Example

<pre class="prettyprint">/* Single line comment */ CREATE TABLE test_data ( value VARCHAR(25) NOT NULL PRIMARY KEY ); /* Multi-line comment */ -- Multi-statement PostgreSQL function CREATE FUNCTION AddData() RETURNS INTEGER AS $$ BEGIN INSERT INTO test_data (value) VALUES ('Hello'); RETURN 1; END; $$ LANGUAGE plpgsql; SELECT * INTO TEMP adddata_temp_table FROM AddData() ; -- Single-statement PostgreSQL function CREATE FUNCTION add(integer, integer) RETURNS integer LANGUAGE sql IMMUTABLE STRICT AS $_$select $1 + $2;$_$; -- Placeholder INSERT INTO ${tableName} (name) VALUES ('Mr. T');</pre>

Limitations

  • No support for PSQL meta-commands with no JDBC equivalent like \set
  • Clean does not remove objects created by extensions. It is therefore highly recommended to create your extensions using CREATE EXTENSION IF NOT EXISTS in order to be able to clean and (re-)migrate your schemas at will.
  • No support for cleaning referenced large objects in pg_largeobject (Issue 1934)

Feature support for check command

Support for the check command on PostgreSQL databases is currently ongoing. Basic support for the following object types has been implemented.

  • Aggregates
  • Domains
  • Enumerations
  • Foreign Keys
  • Functions
  • Indexes
  • Materialized Views
  • Procedures
  • Schemas
  • Sequences
  • Tables
  • Triggers
  • Views