Want to deploy your JVM, Node.js and Go apps effortlessly to AWS? Try our service Boxfuse

PostgreSQL

Versions

  • 9.0 and later

Default Driver

  • 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 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

/* 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
  • No support for ALTER TYPE ... ADD VALUE ... as this statement is an odd beast that can't run inside a transaction due to PostgreSQL limitations. There are workarounds available.
  • Multiline RULE declarations are not parsed correctly (Issue 183)

Redshift