PostgreSQL
Write data to a PostgreSQL table using INSERT ON CONFLICT for upsert. Use SignalSmith to materialize model results or enriched data into PostgreSQL.
Prerequisites
- A PostgreSQL database (version 9.5+ for upsert support)
- A database user with INSERT, UPDATE, DELETE, and CREATE TABLE privileges
- Network connectivity between SignalSmith and the PostgreSQL server
Authentication
PostgreSQL uses Username & Password authentication.
- Enter the database Username and Password in SignalSmith
Configuration
| Field | Type | Required | Description |
|---|---|---|---|
| Host | Text | Yes | PostgreSQL server hostname or IP address |
| Port | Number | No | Server port. Default: 5432 |
| Database | Text | Yes | Database name |
| SSL Mode | Select | No | SSL mode: Require, Verify Full, Prefer, or Disable. Default: Prefer |
Target Settings
| Field | Type | Required | Description |
|---|---|---|---|
| Schema | Text | Yes | Database schema to write to. Default: public |
| Table | Text | Yes | Target table name |
Supported Operations
Sync Modes: Upsert, Insert, Update, Mirror
Audience Sync Modes: Add, Remove, Mirror, Upsert
Features
- Field Mapping: Yes
- Schema Introspection: Yes — SignalSmith reads column metadata from PostgreSQL’s information_schema
Troubleshooting
Connection refused
Verify the host, port, and that the PostgreSQL server is accepting remote connections. Check pg_hba.conf for allowed hosts.
SSL connection required
If the server requires SSL, set SSL Mode to Require or Verify Full.
Insufficient privileges
The user needs INSERT, UPDATE, DELETE privileges on the target table, and CREATE TABLE if the table does not yet exist.
Table does not exist
SignalSmith creates the target table automatically on the first sync. Ensure the user has CREATE TABLE privileges on the target schema.