Postgres output

Load rows into a PostgreSQL table (append, truncate, or upsert). The load is one transaction by default, so a load that fails leaves the table as it was.

Category

Output

Connection

PostgreSQL

What it does

Loads the incoming rows into a PostgreSQL table through a saved Connection. Add the rows to what's already there, empty the table and reload it, or merge them in on a key so re-running doesn't duplicate anything. If the target table doesn't exist it is created from the incoming columns.

Inputs and outputs

When to use it

• Load results into a warehouse or an application database
• Refresh a reporting table completely on every run
• Merge updates in on a key, so a re-run corrects rather than duplicates

Examples

Table: stg_orders · Pre-SQL: TRUNCATE TABLE stg_orders · Post-SQL: INSERT INTO orders SELECT * FROM stg_orders ON CONFLICT (id) DO UPDATE SET total = EXCLUDED.total

Table: public.customers · Mode: append

Mode: truncate

Mode: upsert · Key columns: id

Key columns: region, order_id

Tips

• Each pre- and post-SQL statement has its own ⤢, the SQL Studio, with the connection's tables and columns beside the statement. It has no Run button: a Test run executes none of these statements.
• Pre-SQL and post-SQL run on this same connection, around the load. Pre-SQL runs and commits BEFORE the load starts; post-SQL runs only AFTER it has finished successfully, so a failed load never fires the merge.
• One statement per row, run in order. If one fails the run stops there, and the message names which statement it was and what the database said.
• A Test run (Run to here) executes none of them, so testing stays safe on a pipeline whose pre-SQL truncates a table.
• Credentials live on the Connection, never in the pipeline.
• The table is auto-created from the incoming columns if it doesn't exist, and for upsert it gets a unique constraint on the key columns, so there is something to match on.
• Dates, times and decimals load natively: an auto-created table gets real DATE, TIMESTAMP, TIMESTAMPTZ, TIME and NUMERIC(p,s) columns, and an existing typed table accepts them as they are.
• Upsert needs key columns (or an existing unique constraint) to match on; without one there is nothing to merge against.
• In truncate mode an EMPTY result still empties the table. That is “replace with nothing”, which is usually what you want, but it means a failing upstream filter can clear a reporting table.
• A Test run writes nothing to the database, so you can press Test as often as you like.
• All or nothing (on by default): the create, the truncate and every insert run in ONE transaction, so a failure part way (a rejected row, a lost connection, a cancel, another branch failing) leaves the table exactly as it was. Turn it off for a load too long to hold open (a truncate locks the table for the whole load; a very long transaction holds back VACUUM): each statement then commits as it runs.

Related nodes