PostgreSQL Data Source

PostgreSQL Data Source
lac51
This article includes information about how to install the PostgreSQL JDBC driver, how to connect to a PostgreSQL database, and a list of the PostgreSQL data types that 
Layer7 Live API Creator
 supports. With the JDBC driver installed, the 
PostgreSQL
 data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
3
 
 
Install the PostgreSQL JDBC Driver
The self-contained, single-user version of 
Layer7 Live API Creator
 that is based on Jetty includes the PostgreSQL JDBC driver. The JDBC driver is located in the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory. If you have installed this version of 
Layer7 Live API Creator
, no additional steps are required to install the JDBC driver.
If you have installed 
Layer7 Live API Creator
 on another Java container, such as Apache Tomcat, copy the license file and the 
postgresql-9.xxx.xxx-jdbc41.jar
 JAR file (the JDBC driver) into the directory that is based on the Java container on which you have installed 
Layer7 Live API Creator
:
Apache Tomcat
Copy the JDBC driver into the 
%{CATALINA_HOME}/lib
 directory
Oracle WebLogic
Copy the JDBC driver to the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver to your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation.
For more information about the JDBC driver versions that 
Layer7 Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to a PostgreSQL Database
You can connect to your PostgreSQL database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to a PostgreSQL data source.
    For more information about how to add a connection to a data source, see Database Connectivity
The following procedure details how to connect to your PostgreSQL database when creating your API.
Prerequisites: 
You know your JDBC connection information.
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select 
    PostgreSQL
     as the data source.
  4. Complete the 
    Host
    Port
    , and 
    Password
     fields, including the following fields, and then click
    Continue
    :
    Database
     
    This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) database value.
    Schema
     
    This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) schema name.
    Example:
     "MySchema".
    Username
     
    This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) username.
You are now connected to the PostgreSQL database.
PostgreSQL Supported Data Types
When you are setting up a connection to an external data source, 
Layer7 Live API Creator
 must map the data types in the data source to data types in 
Layer7 Live API Creator
. The following table lists the different data type mappings applicable when working with a PostgreSQL database.
Layer7 Live API Creator
 supports a subset of the full range of PostgreSQL data types.
Layer7 Live API Creator
 supports read and write of arrays of built-in data types, except for the 
bit varying
date
double precision
timestamp
, and 
time
 array types.
For more information about the PostgreSQL data types, see the PostgreSQL documentation.
Layer7 Live API Creator
 supports read and write of the following PostgreSQL data types:
Data type
Modeled as...
Notes
bigint
number
The data type supports the identity column constraint.
bigserial
number
decimal
number
double precision
number
integer
number
The data type supports the identity column constraint.
smallserial
number
serial
number
smallint
number
The data type supports the identity column constraint.
real
number
money
number in JSON (BigDecimal)
PostgreSQL databases handle this data type according to its default monetary format.
For more information about how 
Layer7 Live API Creator
 handles the 
money
 data type, see the "Handling of the money Data Type" section.
numeric
number in JSON plus NaN value
character
Alias:
 char
string
character varying
Alias:
 varchar
string
enum
string
xml
string
json
string
text
string
uuid
string
bit
BitMask
bit varying
BitMask
boolean
boolean in JSON
bytea
bytes
interval
IntervalYearsMonthsDaysSecondsType
Stores a span of time in a contiguous set of units of 
days
hours
minutes
seconds
, and 
fractions
 
of a second
.
"interval": {
"positive": true,
"days": 99,
"hours": 1,
"minutes": 10,
"seconds": 30,
"picos": 990000000000
}
jsonb
JsonType
This data type returns a validated JSON object.
regproc
PostgreSQLOIDType
Example:
 
"a_regproc": {
"oidType": "regproc",
"oidString": "-"
}
regprocedure
PostgreSQLOIDType
regoper
PostgreSQLOIDType
regclass
PostgreSQLOIDType
regtype
PostgreSQLOIDType
regconfig
PostgreSQLOIDType
regdictionary
PostgreSQLOIDType
xid, oid, cid, tid
PostgreSQLOIDType
This following code block represents a signed interval of days to the nano-second precision:
{"oidName": "xid", "oidString": "99999"}
date
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
time
SimpleTime
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
time with time zone
SimpleTimeOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
If you are using a time with time zone, set a prepared statement threshold for connections created from this URL by including the following parameter to the JDBC URL connection (data source):
jdbc:postgresql://server:5432/database?
prepareThreshold=0
timestamp
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestamp with time zone
SimpleTimestampOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
If you are using a timestamp with time zone, set a prepared statement threshold for connections created from this URL by including the following parameter 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.
Use Sequences in PostgreSQL Data Sources
PostgreSQL typically handles auto-generated primary keys using sequences, which are database objects that the sequence generator in the database uses to generate sequential values. When you connect to an PostgreSQL data source, 
Layer7 Live API Creator
 detects the sequences that are defined in your schema.
For more information about the approaches to generating sequential values, see Associate Entities to Sequences.
Filter Complex Data Types
You can filter complex data types in 
Layer7 Live API Creator
 using user filter (
userfilter
). User filter provide you the flexibility to define the filter and specify additional operators using the PostgreSQL SQL syntax.
For more information about how to define a user filter, see Structured Filters.
Debug the errors as you define user filters for complex data types and analyze the SQL that 
Layer7 Live API Creator
 passes in to the PostgreSQL database by viewing the logs.
Filter Enum Types
You can filter enum types by creating a regular filter, a system filter ( 
sysfilter
 ), or a user filter ( 
userfilter
 ). When defining a user filter, convert, or cast, the 
enum
 type parameter in your user filter using the PostgreSQL SQL syntax.
Example:
 
The following example shows the definition of a user filter that contains the 
myArg
 filter and the data type for the 
colname
 column is enum type within the 
mySchemaOwner
 schema/owner of the PostgreSQL data source:
"colname" = {myArg}::"mySchemaOwner"."colname"
Filter Array Types
You can filter arrays of the built-in data types that 
Layer7 Live API Creator
 supports using one of the following:
  • User filters ( 
    userfilter
     )
    The following example shows the definition of a user filter that looks for the 
    myArg
     filter value within the 
    colname
     column that is an 
    array
     data type column:  
    {myArg}= ANY("colname")
  • Free SQL resources
     
    The following example Free SQL resource shows the definition of a Free SQL resource that contains the 
    myArg
     filter and the data type for the 
    bigint
     column is array of 
    bigint
     data type:  
    "bigint[]" = {myArg}::bigint[]
In the Free SQL resource or user filter, convert, or cast, the 
array
 type argument using PostgreSQL SQL syntax. Define the 
WHERE
 clause for filtering arrays of different built-in data types using PostgreSQL SQL syntax.
For more information:
PostgreSQL Data Source Limitations
The following are known limitations in PostgreSQL data sources.
PostgreSQL JDBC Driver Limitations
The PostgreSQL JDBC driver has limitations that prevent the following:
  • Layer7 Live API Creator
     from exposing PostgreSQL functions as REST endpoints. This PostgreSQL JDBC driver limitation exposes inadequate information on the parameter datatypes for a PostgreSQL function to 
    Layer7 Live API Creator
    .
  • Layer7 Live API Creator
     from supporting the 
    Bit
     data type.
  • Layer7 Live API Creator
     from supporting arrays of the 
    Enum 
    and 
    char
     built-in data types. You can call GET requests but you cannot POST/PUT requests.
Display of Complex Data Types in Data Explorer
Data Explorer might not display complex data types properly, such as arrays of the 
Enum
 built-in data type.