PostgreSQL

Currently, Stream Pipelines for PostgreSQL supports these platforms:

  • Amazon Aurora PostgreSQL
  • Azure Databases for PostgreSQL

Stream Pipelines is compatible with PostgreSQL version 10 and later versions.

To establish a connection, ensure that the database endpoint can be accessed publicly through the JDBC connection. The database must be authenticated with a user and password.

We recommend that you create a database user for Stream Pipelines and grant the user appropriate read and write permissions.

Stream Pipelines does not support creating tables automatically in the database. You must create tables in PostgreSQL. When you create tables, ensure that you use PostgreSQL and that the column names and data types match the properties of the corresponding objects. To guarantee successful data delivery with Pipelines, we recommend that you refer to the provided table that maps data types of object properties, as defined in Data Catalog, to their corresponding PostgreSQL data types. Ensure that the case sensitivity of the object property names and the corresponding table column names is identical.

This table shows the object properties data types, as defined in Data Catalog, and their corresponding PostgreSQL data types:

Category Data Catalog data type PostgreSQL data type | alias
Date and Time data types Datetime:
  • Standard
  • American
  • Three-character month
 Timestamp without time zone | timestamp or timestamp with time zone or timestamptz
Datetime:
  • Other
 Character varying | varchar
Date:
  • Standard
  • American
  • Basic
 Date
Time:
  • Other
 Varchar
Time:
  • Standard
 Time without time zone | time
Number Numeric | numeric
Numeric data types Datetime:
  • Epoch
 BigInt or Int
Integer:
  • Maximum: Less than 32767
  • Exclusive maximum: Less than 32768
  • Minimum: More than -32767
  • Exclusive minimum: More than -32768
  • Multiple of (number of digits)
 Smallint | int2
Integer:
  • Maximum: Between 32767 and 2147483647
  • Exclusive maximum: Between 32768 and 2147483647
  • Minimum: Between -2147483647 and -32767
  • Exclusive minimum: Between -2147483648 and -32768
  • Multiple of (number of digits)
  • None of the above is provided
 Integer | int4
Integer:
  • Maximum: More than 2147483647
  • Exclusive maximum: More than 2147483648
  • Minimum: Less than -2147483647
  • Exclusive minimum: Less than -2147483648
  • Multiple of (number of digits)
 Bigint | int8
Number Numeric | numeric
Logical data types Boolean Boolean | bool
String Character varying | varchar
Object Character varying | varchar
Note: Data Catalog object data types are supported for use with objects that have localized strings that are defined as part of the Data Catalog Locale Selections feature. Currently, Stream Pipelines processes only the first value of the object values. For example, if the localized value within the NDJSON record is {“desc”: {“en_US”:”Blue Car”, “de_DE”: “Blaues Auto”, “es_ES”: “Coche Azul”},, then only the first value, ”Blue Car”, is stored in the table column.