PostgreSQL table function

BETA

¶

The Tinybird postgresql() table function allows you to read data from your existing PostgreSQL database into Tinybird, then schedule a regular copy pipe to orchestrate synchronization. You can load full tables, and every run performs a full replace on the data source.

To use it, define a node using standard SQL and the postgresql function keyword, then publish the node as a copy pipe that does a sync on every run. See Table functions for general information and tips.

Syntax¶

Create a new pipe node. Call the postgresql table function and pass the hostname and port, database, table, user, and password:

SELECT *
FROM postgresql(
  'aws-0-eu-central-1.TODO.com:3866',
  'postgres',
  'orders',
  {{tb_secret('pg_username')}},
  {{tb_secret('pg_password')}},
)

Publish this node as a copy pipe. You can choose to append only new data or replace all data.

Type support and inference¶

Here's a detailed conversion table:

Enabling the PostgreSQL Table Function¶

In Production¶

To enable the PostgreSQL table function in your production workspace, please contact Tinybird support. They will enable the function for your specific workspace.

For Local Development¶

After the feature is enabled for your Workspace, it becomes available for local development automatically. You do not need to take any extra steps to enable it for local use.

Using the PostgreSQL Table Function Locally¶

When using the PostgreSQL table function in your local development environment, keep the following considerations in mind:

1. Network Connection: The connection to your PostgreSQL server originates from within the Tinybird Local container (Docker or where you're running Tinybird Local).
2. Server Reachability: Ensure your PostgreSQL server is reachable from inside the Docker network.
3. Credentials: You must set the secrets for your PostgreSQL credentials in your local Tinybird project. See the tb secret documentation for details. You can set your credentials using:

tb secret set pg_username <YOUR_PG_USERNAME>
tb secret set pg_password <YOUR_PG_PASSWORD>

4. Server Address: Use the container-reachable address in your queries, not localhost.

Example query:

NODE get_ids
SQL >
%
SELECT id
FROM postgresql(
 'host.docker.internal:5432',
  '<YOUR_PG_DB>',
  '<YOUR_PG_TABLE>',
  {{ tb_secret('pg_username') }},
  {{ tb_secret('pg_password') }}
)

TYPE ENDPOINT

Considerations¶

The following considerations apply to the postgresql() table function:

• Tinybird doesn't support all PostgreSQL types directly, so some types are mapped to String, which is the most flexible type for arbitrary data.
• For the NUMERIC and DECIMAL types, Decimal(p, s) in Tinybird requires specifying precision (p) and scale (s).
• Time zone support in Tinybird's DateTime can be managed via additional functions or by ensuring consistent storage and retrieval time zones.
• Some types like INTERVAL don't have a direct equivalent in Tinybird and are usually stored as String or decomposed into separate fields.

See also¶

• Table functions