TIBCO Scribe® Online Connector for PostgreSQL

The TIBCO Scribe® Online Connector for PostgreSQL simplifies integrating your PostgreSQL databases with business systems like ERP, CRM, and Marketing Automation. It discovers tables, views, and columns, and supports native SQL queries to access, filter, and join your data.

Use the PostgreSQL Connector as a source or target Connection for Integration Solutions.

Use the TIBCO Scribe® Online Connector for PostgreSQL to:

• Integrate with any application across your business that uses PostgreSQL as a backend.
• Move legacy PostgreSQL data to other systems.

Connector Specifications

Supported Entities

The PostgreSQL Connector supports the following entities:

• Tables and Views from the PostgreSQL database are exposed as entities.
• Views support only Query.

Setup Considerations

The PostgreSQL Connector supports:

• PostgreSQL versions 17 and higher.
• PostgreSQL hosted on Amazon Aurora.

Selecting An Agent Type For PostgreSQL

Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.

Connecting To PostgreSQL

1. Select More > Connections from the menu.
2. From the Connections page, select Add to open the Add a New Connection dialog.
3. Select the Connector from the dropdown list in the Connection Type field, and then enter the following information for this Connection:
   • Name — This can be any meaningful name, up to 25 characters.
   • Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
   • Host — IP address or URL of the target PostgreSQL server instance. Requires a valid IPv4 IP or URL address.
   • Port — PostgreSQL server port number, which must be in the 1023 to 65535 range. The default value is 5432.
   • Database — Name of the PostgreSQL database to which you want to connect.
   • Username — Username for this PostgreSQL database instance.
   • Password — Password for this Username. PostgreSQL databases do not require a password.
   • Additional Properties — Optional field where you can specify one or more connection string parameters. See the See the Connection String Options section for a list of parameters that can be used and their default values.

     Syntax for the Additional Parameters field is as follows:

     • Connection strings have the form keyword1=value; keyword2=value; and are case-insensitive.
     • Values containing special characters (for example, semicolon (;)) can be double-quoted.
     • All blank characters, except those within a value or within quotation marks, are ignored.
     • Preceding and trailing spaces are ignored unless enclosed in single or double quotes, such as keyword=" value".
     • Semicolons (;) within a value must be delimited by quotation marks.
     • Use a single quote (') if the value begins with a double quote (").
     • Use a double quote (") if the value begins with a single quote (').
     • Parameters that are handled by other fields or default settings in the Connection dialog are ignored if used in the Additional Parameters field, including: 
       • Host
       • Port
       • Database
       • Username
       • Password
4. Select Test to ensure that the Agent can connect to PostgreSQL. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
5. Select OK/Save to save the Connection.

Metadata Notes

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

Entities

PostgreSQL Entity names shown in TIBCO Scribe® Online display both the schema name and the entity name in the following format:

[schemaname][entityname]

myschemacontact

Arrays

All arrays are considered strings, regardless of the data type of the values within the array. When you map or filter array fields using the PostgreSQL Connector, format the array as a string. Use the following syntax:

• Enclose the array elements in curly braces ({}).
• Enclose the entire array in single or double quotes.
• Separate individual values with commas.

Examples

"{Text4, Text5, Text6}"
"{1, 100, 1000}"

PostgreSQL Connector As IS Source

Consider the following when using the PostgreSQL Connector as an Integration Solution source.

• Hierarchical data is not supported.
• Relationships are not supported.

Filtering

Net Change

Net Change supports querying for new and updated records on date and timestamp fields. All logical and comparison operators are supported for Filters.

When a datetime is configured on the Query Block on the Block Properties Net Change tab to query for new and updated records, this configuration functions as an additional filter. The Net Change datetime filter applies as an AND condition after any other filters specified on the Block Properties Filter tab. TIBCO Scribe® Online combines both the Net Change filter and the filters from the Filter tab into a single query. For example, see Net Change And Filters.

Some TIBCO Scribe® Online Connectors support only one filter. For these Connectors, use either Net Change or one filter from the Filter tab, but not both simultaneously.

Native Query

The PostgreSQL Connector supports native SQL queries in Native Query Blocks, allowing you to create customized queries for your PostgreSQL database. While these queries can be as simple or complex as needed, they must return a single result set. The connector sends the native query text to PostgreSQL exactly as entered, without any modifications.

After you enter the SQL query, select Test to validate it. The Connector does not accept invalid queries. For more information, see Native Query Block and Creating Native Queries For Microsoft SQL Server.

Select columnName::TEXT from schemaname.tablename

SQL queries must explicitly reference both the schema name and the table name as demonstrated in the following example:

Select * from schemaname.tablename

Select * from bfschema1.table1

When a Native Query in a Map is tested and the source datastore yields no data, TIBCO Scribe® Online is unable to build the schema for the underlying metadata, which prevents saving the Map. To allow TIBCO Scribe® Online to build the schema, perform the following steps:

• Create a single temporary record in the source datastore that matches the Native Query.
• Test the Native Query to ensure it is successful.
• Save the Map.
• Remove the temporary record from the source datastore.

PostgreSQL Connector As IS Target

Consider the following when using the PostgreSQL Connector as an IS target.

• Batch processing is not supported.

TIBCO Scribe® Online API Considerations

To create connections with the TIBCO Scribe® Online API, the PostgreSQL Connector requires the following information:

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

License Agreement

The TIBCO Scribe® Online End User License Agreement for the PostgreSQL Connector describes TIBCO and your legal obligations and requirements. TIBCO suggests that you read the End User License Agreement.

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.