PostgreSQL

Supported Versions

  • 14
  • 13
  • 12
  • 11
  • 10
  • 9.6

Driver

URL format jdbc:postgresql://host:port/database
SSL support Yes - add ?ssl=true
Maven Central coordinates org.postgresql:postgresql:42.2.14
Supported versions 9.3-1104-jdbc4 and later
Default Java class org.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 MigrateDB migration.
  • Any PostgreSQL sql script executed by MigrateDB, can be executed by the PostgreSQL command-line tool and other PostgreSQL-compatible tools (after the placeholders have been replaced).

Example

/* 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
CREATE TABLE copy_test(c1 text, c2 text, c3 text);
COPY copy_test (c1, c2, c3) FROM stdin;
1	utf8: ümlaute: äüß	NaN
2	\N	123
3	text	123.234444444444449
\.

Authentication

MigrateDB 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>...

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

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.

Limitations

  • No support for psql meta-commands with no JDBC equivalent like \set

Aurora PostgreSQL ➡️