Target Postgres

A Singer sql target that supports Postgres and Redshift, for use with Singer streams generated by Singer taps.

Features

• Creates SQL tables for Singer streams
• Denests objects flattening them into the parent object's table
• Denests rows into separate tables
• Adds columns and sub-tables as new fields are added to the stream JSON Schema
• Full stream replication via record version and ACTIVATE_VERSION messages.

Usage

Create a config file with connection information and target schema. Only the target_connection config is neccessary for postgres connections.

  {
    "target_connection": {
      "host": "localhost",
      "port": "5439",
      "username": "my_username",
      "password": "my_pw",
      "database": "my_db",
      "target_catalog": "my_db_schema"
    }
    ,
    "target_s3": {
      "bucket": "my_bucket_name",
      "aws_access_key_id": "",
      "aws_secret_access_key": ""
    }
  }

Run target-redshift or target-postgres against a Singer stream.

	tap-something | target-redshift --config config.json
    tap-something | target-postgres --config config.json

It ignores "STATE" type Singer messages.

Limitations

• Requires a JSON Schema for every stream.
• Only string, string with date-time format, integer, number, boolean, object, and array types with or without null are supported. Arrays can have any of the other types listed, including objects as types within items.
  • Example of JSON Schema types that work
    • ['number']
    • ['string']
    • ['string', 'null']
  • Exmaple of JSON Schema types that DO NOT work
    • ['string', 'integer']
    • ['integer', 'number']
    • ['any']
    • ['null']
• Types must be arrays
• Types cannot change
• JSON Schema combinations such as "anyOf" and "allOf" are not supported.
• JSON Schema $ref is not supported.