---
layout: documentation
menu: postgresql
subtitle: PostgreSQL
---
# PostgreSQL

## Supported Versions

- `14`
- `13`
- `12`
- `11`
- `10`
- `9.6` {% include teams.html %}
- `9.5` {% include teams.html %}
- `9.4` {% include teams.html %}
- `9.3` {% include teams.html %}
- `9.2` {% include teams.html %}
- `9.1` {% include enterprise.html %}
- `9.0` {% include enterprise.html %}

## Support Level

<table class="table">
    <tr>
        <th width="25%">Compatible</th>
        <td>✅</td>
    </tr>
    <tr>
        <th width="25%">Certified</th>
        <td>✅</td>
    </tr>
    <tr>
        <th width="25%">Guaranteed</th>
        <td>✅ {% include teams.html %}</td>
    </tr>
</table>

Support Level determines the degree of support available for this database ([learn more](/documentation/learnmore/database-support)). 

## Driver

<table class="table">
<tr>
<th>URL format</th>
<td><code>jdbc:postgresql://<i>host</i>:<i>port</i>/<i>database</i></code></td>
</tr>
<tr>
<th>SSL support</th>
<td>Yes - add <code>?ssl=true</code></td>
</tr>
<tr>
<th>Ships with Flyway Command-line</th>
<td>Yes</td>
</tr>
<tr>
<th>Maven Central coordinates</th>
<td><code>org.postgresql:postgresql:42.2.14</code></td>
</tr>
<tr>
<th>Supported versions</th>
<td><code>9.3-1104-jdbc4</code> and later</td>
</tr>
<tr>
<th>Default Java class</th>
<td><code>org.postgresql.Driver</code></td>
</tr>
</table>

## SQL Script Syntax

- [Standard SQL syntax](/documentation/concepts/migrations#sql-based-migrations#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');

-- COPY ... FROM STDIN
COPY copy_test (c1, c2, c3) FROM stdin;
1	utf8: ümlaute: äüß	NaN
2	\N	123
3	text	123.234444444444449
\.</pre>

## Authentication

Flyway supports the following PostgreSQL authentication methods:

- URL authentication 
- SCRAM
- pgpass

### URL authentication

The user and password can be provided in the JDBC URL, in the form

`jdbc:postgresql://<host>:<port>/<database>?user=myUsername&password=myPassword&<key1>=<value1>&<key2>=<value2>...`

In this case, they do not need to be passed separately in configuration and the Flyway commandline will not prompt for them.

### SCRAM

SCRAM authentication encryption is supported transparently using the current driver (42.2.14) - note that 
`.jre6` and `.jre7` versions of the driver for older JREs do not support it. 

### pgpass
{% include teams.html %}

Authentication can be done with a pgpass file to retrieve the password for a connection, in which case it does not need to be supplied in configuration. If the path to a pgpass file is set in the environment variable `PGPASSFILE`, it will be read from here. If not, then in Windows the file will be read from the location `%APPDATA%\postgresql\pgpass.conf`, otherwise it is read from`~/.pgpass`. You can read more about pgpass files and their structure [here](https://www.postgresql.org/docs/9.6/libpq-pgpass.html).

## Lock Types

By default Flyway uses a transactional lock with PostgreSQL, however this can cause issues with certain SQL statements, most notably `CREATE INDEX CONCURRENTLY`. In this scenario, transactional locks can be replaced with session-level locks by setting `flyway.postgresql.transactional.lock=false` in your configuration.

## 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 schema(s) at will
- No support for cleaning referenced large objects in `pg_largeobject` ([Issue 1934](https://github.com/flyway/flyway/issues/1934))
- No support for the `passfile` or `hostaddr` parameter when using pgpass as there is no JDBC equivalent

<p class="next-steps">
    <a class="btn btn-primary" href="/documentation/database/aurora-postgresql">Aurora PostgreSQL <i class="fa fa-arrow-right"></i></a>
</p>

## 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
- Enums
- Foreign Keys
- Functions
- Indexes
- Materialized Views
- Procedures
- Schemas
- Sequences
- Tables
- Triggers
- Views