PostgreSQL Sink

Plugin version: 1.10.0

Inserts records into a PostgreSQL table. Each record is written to a row in the table.

If you need to perform upserts, use a PostgreSQL Execute action plugin. See Performing upserts on a PostgreSQL Database.

Configuration

Property	Macro Enabled?	Version Introduced	Description

Property	Macro Enabled?	Version Introduced	Description
Use Connection	No	6.7.0/1.8.0	Optional. Whether to use a connection. If a connection is used, you do not need to provide the credentials.
Connection	Yes	6.7.0/1.8.0	Optional. Name of the connection to use. Project and service account information will be provided by the connection. You also can use the macro function ${conn(connection-name)}.
JDBC Driver Name	No		Required. Name of the JDBC driver to use. Default is postgresql.
Host	Yes		Required. Host that PostgreSQL is running on. Default is localhost.
Port	Yes		Required. Port that PostgreSQL is running on. Default is 5432.
Username	Yes		Optional. User identity for connecting to the specified database.
Password	Yes		Optional. Password to use to connect to the specified database.
Connection Arguments	Yes		Optional. A list of arbitrary string key/value pairs as connection arguments. These arguments will be passed to the JDBC driver as connection arguments for JDBC drivers that may need additional configurations.
Reference Name	No		Required. Name used to uniquely identify this sink for lineage, annotating metadata, etc.
Database	Yes		Required. PostgreSQL database name.
Table Name	Yes		Required. Name of the table to export to.
Connection Timeout	No		Optional. The timeout value used for socket connect operations. If connecting to the server takes longer than this value, the connection is broken.The timeout is specified in seconds and a value of zero means that it is disabled. Default is 100.

Example

You want to write output records to "users" table of PostgreSQL database named "prod" that is running on "localhost", port 5432, as "root" user with "root" password. Ensure that the driver for PostgreSQL is installed. You can also provide driver name for some specific driver, otherwise "postgresql" will be used, and then configure the plugin with:

Property	Value

Property	Value
Reference Name	`snk1`
Driver Name	`postgresql`
Host	`localhost`
Port	`5432`
Database	`prod`
Table Name	`users`
Username	`postgres`
Password	`postgres`

Data Type Mapping

All PostgreSQL specific data types mapped to string and can have multiple input formats and one 'canonical' output form. Refer to PostgreSQL data types documentation to figure out proper formats.

PostgreSQL Data Type	CDAP Schema Data Type	Comments

PostgreSQL Data Type	CDAP Schema Data Type	Comments
bigint	int
bigserial	long
bit(n)	string	string with '0' and '1' chars exact n length
bit varying(n)	string	string with '0' and '1' chars max n length
boolean	boolean
bytea	bytes
character	string
character varying	string
double precision	double
integer	int
numeric(precision, scale)/decimal(precision, scale)	decimal
real	float
smallint	int
smallserial	int
serial	int
text	string
date	date
time [ (p) ] [ without time zone ]	time
time [ (p) ] with time zone	string
timestamp [ (p) ] [ without time zone ]	timestamp
timestamp [ (p) ] with time zone	timestamp	stored in UTC format in database
xml	string
tsquery	string
tsvector	string
uuid	string
box	string
cidr	string
circle	string
inet	string
interval	string
json	string
jsonb	string
line	string
lseg	string
macaddr	string
macaddr8	string
money	string
path	string
point	string
polygon	string