Skip to main content target for loading data into postgres

Project description

Target Postgres


PyPI version

A Singer postgres target, for use with Singer streams generated by Singer taps.


  • 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.


pip install singer-target-postgres


  1. Follow the Best Practices for setting up separate tap and target virtualenvs to avoid version conflicts.

  2. Create a config file at ~/ with postgres connection information and target postgres schema.

      "postgres_host": "localhost",
      "postgres_port": 5432,
      "postgres_database": "my_analytics",
      "postgres_username": "myuser",
      "postgres_password": "1234",
      "postgres_schema": "mytapname"
  3. Run target-postgres against a Singer tap.

    ~/.virtualenvs/tap-something/bin/tap-something \
      | ~/.virtualenvs/target-postgres/bin/target-postgres \
        --config ~/ >> state.json

    If you are running windows, the following is equivalent:

    venvs\tap-exchangeratesapi\Scripts\tap-exchangeratesapi.exe | ^
    venvs\target-postgresql\Scripts\target-postgres.exe ^
    --config target_postgres_config.json


The fields available to be specified in the config file are specified here.

Field Type Default Details
postgres_host ["string", "null"] "localhost"
postgres_port ["integer", "null"] 5432
postgres_database ["string"] N/A
postgres_username ["string", "null"] N/A
postgres_password ["string", "null"] null
postgres_schema ["string", "null"] "public"
postgres_sslmode ["string", "null"] "prefer" Refer to the libpq docs for more information about SSL
postgres_sslcert ["string", "null"] "~/.postgresql/postgresql.crt" Only used if a SSL request w/ a client certificate is being made
postgres_sslkey ["string", "null"] "~/.postgresql/postgresql.key" Only used if a SSL request w/ a client certificate is being made
postgres_sslrootcert ["string", "null"] "~/.postgresql/root.crt" Used for authentication of a server SSL certificate
postgres_sslcrl ["string", "null"] "~/.postgresql/root.crl" Used for authentication of a server SSL certificate
invalid_records_detect ["boolean", "null"] true Include false in your config to disable target-postgres from crashing on invalid records
invalid_records_threshold ["integer", "null"] 0 Include a positive value n in your config to allow for target-postgres to encounter at most n invalid records per stream before giving up.
disable_collection ["string", "null"] false Include true in your config to disable Singer Usage Logging.
logging_level ["string", "null"] "INFO" The level for logging. Set to DEBUG to get things like queries executed, timing of those queries, etc. See Python's Logger Levels for information about valid values.
persist_empty_tables ["boolean", "null"] False Whether the Target should create tables which have no records present in Remote.
state_support ["boolean", "null"] True Whether the Target should emit STATE messages to stdout for further consumption. In this mode, which is on by default, STATE messages are buffered in memory until all the records that occurred before them are flushed according to the batch flushing schedule the target is configured with.

Supported Versions

target-postgres only supports JSON Schema Draft4. While declaring a schema is optional, any input schema which declares a version other than 4 will be rejected.

target-postgres supports all versions of PostgreSQL which are presently supported by the PostgreSQL Global Development Group. Our CI config defines all versions we are currently supporting.

Version Current minor Supported First Release Final Release
11 11.3 Yes October 18, 2018 November 9, 2023
10 10.8 Yes October 5, 2017 November 10, 2022
9.6 9.6.13 Yes September 29, 2016 November 11, 2021
9.5 9.5.17 Yes January 7, 2016 February 11, 2021
9.4 9.4.22 Yes December 18, 2014 February 13, 2020
9.3 9.3.25 No September 9, 2013 November 8, 2018

The above is copied from the current list of versions on

Known 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']
  • JSON Schema combinations such as anyOf and allOf are not supported.
  • JSON Schema $ref is partially supported:
    • NOTE: The following limitations are known to NOT fail gracefully
    • Presently you cannot have any circular or recursive $refs
    • $refs must be present within the schema:
      • URI's do not work
      • if the $ref is broken, the behaviour is considered unexpected
  • Any values which are the string NULL will be streamed to PostgreSQL as the literal null
  • Table names are restricted to:
    • 63 characters in length
    • can only be composed of _, lowercase letters, numbers, $
    • cannot start with $
    • ASCII characters
  • Field/Column names are restricted to:
    • 63 characters in length
    • ASCII characters

Usage Logging requires official taps and targets to collect anonymous usage data. This data is only used in aggregate to report on individual tap/targets, as well as the Singer community at-large. IP addresses are recorded to detect unique tap/targets users but not shared with third-parties.

To disable anonymous data collection set disable_collection to true in the configuration JSON file.


target-postgres utilizes for package management, and PyTest for testing.


See also:

  • DECISIONS: A document containing high level explanations of various decisions and decision making paradigms. A good place to request more explanation/clarification on confusing things found herein.
  • TableMetadata: A document detailing some of the metadata necessary for TargetPostgres to function correctly on the Remote


If you have Docker and Docker Compose installed, you can easily run the following to get a local env setup quickly.

$ docker-compose up -d --build
$ docker logs -tf target-postgres_target-postgres_1 # You container names might differ

As soon as you see INFO: Dev environment ready. you can shell into the container and start running test commands:

$ docker exec -it target-postgres_target-postgres_1 bash # Your container names might differ
root@... $ source /code/venv/target-postgres/bin/activate
root@... $ pytest

See the PyTest commands below!


To run the tests, you will need a PostgreSQL server running.

NOTE: Testing assumes that you've exposed the traditional port 5432.

Make sure to set the following env vars for PyTest:

$ EXPORT POSTGRES_HOST='<your-host-name>' # Most likely 'localhost'
$ EXPORT POSTGRES_DB='<your-db-name>'     # We use 'target_postgres_test'
$ EXPORT POSTGRES_USER='<your-user-name'  # Probably just 'postgres', make sure this user has no auth


To run tests, try:

$ python pytest

If you've bash shelled into the Docker Compose container (see above), you should be able to simply use:

$ pytest

Collaboration and Contributions

Join the conversation over at the Slack and on the #target-postgres channel.

Try to adhere to the following for contributing:

  • File New Issue -> Fork -> New Branch(If needed) -> Pull Request -> Approval -> Merge

Users can file an issue without submitting a pull request but be aware not all issues can or will be addressed.


Target Postgres is sponsored by Data Mill (Data Mill Services, LLC)

Data Mill helps organizations utilize modern data infrastructure and data science to power analytics, products, and services.

Copyright Data Mill Services, LLC 2018

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for singer-target-postgres, version 0.1.11
Filename, size File type Python version Upload date Hashes
Filename, size singer_target_postgres-0.1.11-py3-none-any.whl (42.9 kB) File type Wheel Python version py3 Upload date Hashes View hashes
Filename, size singer-target-postgres-0.1.11.tar.gz (28.4 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page