Connect PostgreSQL to Mammoth and import tables or SQL

What you can bring in from PostgreSQL

• Any table you can read, grouped by schema (the default schema is public).
• The result of your own SELECT query, including joins and filters.
• A query Mammoth writes from a plain-language description, when AI query is on for your workspace.

The dataset can refresh daily or only when you ask.

Before you connect PostgreSQL

You need:

• A PostgreSQL server reachable from the internet, or through an SSH bastion host.
• A database user with SELECT permission on the tables you want to import.
• The server's hostname or IP address, port (default 5432), database name, username and password.
• Mammoth's IP address added to your PostgreSQL allowlist (pg_hba.conf and any cloud security group). The connection form shows the address. See Allow Mammoth through your firewall.

Connect PostgreSQL to Mammoth

These steps follow the shared flow in Connect a database or app.

1. Go to the Data Library and click New dataset.
2. Choose Connectors.
3. Search for PostgreSQL and click its tile. The connection form opens.
4. Fill in the fields below.
5. Click Connect. Mammoth checks the details and saves the connection. If the check fails, the error appears at the bottom of the form.

Once connected, the connector workspace opens on your tables.

Connect PostgreSQL through an SSH tunnel

Use this when your database is reachable only through an SSH bastion host.

1. On the connection form, open Connection options.
2. Tick Connect through an SSH tunnel.
3. Fill in the SSH fields below, then click Connect.

With a tunnel, Mammoth reaches the SSH host, so allow Mammoth's IP address on the SSH host, not on the database. Host URL and Port are the database's address as the SSH host sees it.

Query a PostgreSQL table in the SQL view

1. In Tables, click a table. Mammoth opens its rows with the SELECT * query that read them, shown above the rows.
2. To change the query, click Edit SQL, edit it, and click Run (or press Ctrl+Enter).
3. To keep only some columns, tick them in the table header.

Example query:

SELECT order_id, customer_id, amount, created_at
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
  AND status = 'completed'

Write a PostgreSQL query from a description

When AI query is on for your workspace, click Edit SQL to see the Describe the data you need... box above the editor.

1. Describe what you want, for example "all completed orders in the last 90 days".
2. Click Write SQL. Mammoth writes the query from your table structure.
3. Check the query, then click Run.

If a query fails, click Fix with AI to ask the chat to correct it. Connect a database or app covers the full SQL view.

Save PostgreSQL data as a dataset

1. Click Save as dataset.
2. Enter a Name and choose a folder under Save in.
3. Under Keep it up to date, choose Daily at 06:00 or Only when I ask.
4. Under On each refresh, click Change and pick what a refresh does (see the table below).
5. Click Save as dataset. When the dataset is ready, click Open dataset.

A key column is a column whose values only ever go up, such as an ID or a date.

Refresh PostgreSQL data on a schedule

Choosing Daily at 06:00 creates a Dataset Refresh automation for the dataset. To run it on another schedule, edit that automation. For a dataset saved with Only when I ask, create a Dataset Refresh automation yourself.

After you save, you can change what a refresh does in the dataset's Update method setting. It offers Replace all data, Add new rows only and Keep only the newest rows.

Troubleshoot PostgreSQL connections

Related

• Connect a database or app: the shared connector workspace steps and the full SQL view.
• Allow Mammoth through your firewall: the IP address to allow and how.
• Manage saved connections: edit, reuse or delete this connection.
• Refresh a dataset: change when the dataset refreshes.
• Speed up pipelines and connectors: strategies for large tables.
• Connector reference: every source Mammoth connects to.