Aurora PostgreSQL

Supported Versions

• 13.x
• 12.x
• 11.x
• 10.x
• 9.6

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

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)

Redshift ➡️