PostgreSQL Data Source

Live API Creator supports a subset of the full range of PostgreSQL data types.

For data type documentation, see PostgreSQL documentation.

Install the PostgreSQL JDBC Driver

Install the license file and the postgresql-9.xxx.xxx-jdbc41.jar driver into the following directory:

  • (The self-contained, single-user version of Live API Creator based on Jettycaliveapicreator/lib/ext directory.
Note: This package already includes the PostgreSQL JDBC driver.
  • (Tomcat) The Tomcat Home/lib directory.

For more information about the supported versions of the JDBC drivers, see Supported Platforms.

Connect to a PostgreSQL Database

Prerequisites: You know your JDBC connection information.

Connect to your database by creating your API.

For more information about creating APIs, see Create your API Project.

  1. In the connection wizard, select PostgreSQL.
  2. Complete the Schema, Database, host, port, and password fields, including the following fields, and then click Continue:

Database

This database value accepts double quotesUse double quotes to accept the value of mixed-case (non-standard) database value.

Schema

This database value accepts double quotesUse double quotes to accept the value of mixed-case (non-standard) schema name.

For example: "MySchema".

Username

This database value accepts double quotesUse double quotes to accept the value of mixed-case (non-standard) username.

You are connected to the database.

PostgreSQL Supported Data Types

Live API Creator supports read and write of the following PostgreSQL data types:

Data typeModeled as... Notes
smallintnumber 
integernumber 
bigintnumber 
decimalnumber 
numericnumber in JSON plus NaN value 
realnumber 
double precisionnumber 
smallserialnumber 
serialnumber 
bigserialnumber 
moneynumber in JSON (BigDecimal) 
character varying
Alias: varchar
string 
character
Alias: char
string 
textstring 
jsonstring 
jsonbstring 
xmlstring 
uuidstring 
byteabytes 
timestampSimpleTimestamp 
timestamp with time zoneSimpleTimestampOffset
If you are using a timestamp with timezone or time with timezone, set a prepared statement threshold for connections created from this URL by including the following change to the JDBC URL connection (Data Source):
jdbc:postgresql://server:5432/database?prepareThreshold=0

For more information about connecting to the database, including information about prepareThreshold, see the PostgreSQL JDBC driver documentation.
dateSimpleDate 
timeSimpleTime 
time with time zoneSimpleTimeOffset
Note 1: include in url: prepareThreshold=0
 
intervalinterval 
booleanboolean in JSON 
bitBitMask 
bit varyingBitMask