PostgreSQL table function
BETA
¶The Tinybird postgresql
table function is currently in private beta. If you want access, contact Tinybird.
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. Based on ClickHouse's postgresql
table function, the Tinybird table function uses all the same syntax, requiring no additional tooling.
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.
Set up¶
Prerequisites¶
Your postgres database needs to be open and public (exposed to the internet, with publicly-signed certs), so you can connect it to Tinybird via the hostname and port using your username and password.
You'll also need familiarity with making cURL requests to manage your secrets.
Type support and inference¶
Since this table functions is based on ClickHouse's postgresql
table function, Tinybird inherits the same types support and inference. Here's a detailed conversion table:
PostgreSQL Data Type | ClickHouse Data Type |
---|---|
BOOLEAN | UInt8 or Bool |
SMALLINT | Int16 |
INTEGER | Int32 |
BIGINT | Int64 |
REAL | Float32 |
DOUBLE PRECISION | Float64 |
NUMERIC or DECIMAL | Decimal(p, s) |
CHAR(n) | FixedString(n) |
VARCHAR (n) | String |
TEXT | String |
BYTEA | String |
TIMESTAMP | DateTime |
TIMESTAMP WITH TIME ZONE | DateTime (with appropriate timezone handling) |
DATE | Date |
TIME | String (since there is no direct TIME type) |
TIME WITH TIME ZONE | String |
INTERVAL | String |
UUID | UUID |
ARRAY | Array(T) where T is the array element type |
JSON | String or JSON (ClickHouse's JSON type for some versions) |
JSONB | String |
INET | String |
CIDR | String |
MACADDR | String |
ENUM | Enum8 or Enum16 |
GEOMETRY | String |
Notes:
- ClickHouse does not support all PostgreSQL types directly, so some types are mapped to String in ClickHouse, which is the most flexible type for arbitrary data.
- For the NUMERIC and DECIMAL types, Decimal(p, s) in ClickHouse requires specifying precision (p) and scale (s).
- Time zone support in ClickHouse's DateTime can be managed via additional functions or by ensuring consistent storage and retrieval time zones.
- Some types like INTERVAL do not have a direct equivalent in ClickHouse and are usually stored as String or decomposed into separate fields.
About secrets¶
The Environment Variables API is currently only accessible at API level. UI support will be released in the near future.
Pasting your credentials into a Pipe Node or .datafile
as plain text is a security risk. Instead, use the Environment Variables API to create two new secrets for your postgres username and password. In the next step, you'll then be ready to interpolate your new secrets using the tb_secret
function:
{{tb_secret('pg_username')}} {{tb_secret('pg_password')}}
Load a PostgreSQL table¶
In the Tinybird UI, create a new Pipe Node. Call the postgresql
table function and pass the hostname & port, database, table, user, and password:
Example Node logic with actual values
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, thereby running the query manually. You can choose to append only new data, or replace all data.
Alternative: Use datafiles¶
As well as using the UI, you can also define Node logic in Pipe .datafile
files. An example for an ecommerce orders_backfill
scenario, with a Node called all_orders
, would be:
NODE all_orders SQL > % SELECT * FROM postgresql( 'aws-0-eu-central-1.TODO.com:3866', 'postgres', 'orders', {{tb_secret('pg_username')}}, {{tb_secret('pg_password')}}, ) TYPE copy TARGET_DATASOURCE orders COPY_SCHEDULE @on-demand COPY_MODE replace
Include filters¶
You can use a source column in postgres and filter by a value in Tinybird, for example:
Example Copy Pipe with postgresql function and filters
SELECT * FROM postgresql( 'aws-0-eu-central-1.TODO.com:3866', 'postgres', 'orders', {{tb_secret('pg_username')}}, {{tb_secret('pg_password')}}, ) WHERE orderDate > (select max(orderDate) from orders)
Schedule runs¶
When publishing as a Copy Pipe, most users set it to run at a frequent interval using a cron expression.
It's also possible to trigger manually:
curl -H "Authorization: Bearer <PIPE:READ token>" \ -X POST "https:/tinybird.co/api/v0/pipes/<pipe_id>/run"
Having manual Pipes in your Workspace is helpful, as you can run a full sync manually any time you need it - sometimes delta updates are not 100% accurate. Some users also leverage them for weekly full syncs.
Observability¶
Job executions are logged in the datasources_ops_log
Service Data Source. This log can be checked directly in the Data Source view page in the UI. Filter by datasource_id
to monitor ingestion through the PostgreSQL table function from the datasources_ops_log
:
Example query to the datasources_ops_log Service Data Source
SELECT timestamp, event_type, result, error, job_id FROM tinybird.datasources_ops_log WHERE datasource_id = 't_1234' AND event_type = 'copy' ORDER BY timestamp DESC
Limits¶
The table function inherits all the limits of Copy Pipes.
Secrets are created at a Workspace level, so you will be able to connect one PostgreSQL database per Tinybird Workspace.
Check the limits page for limits on ingestion, queries, API Endpoints, and more.
Billing¶
When set up, this functionality is a Copy Pipe with a query (processed data). There are no additional or specific costs for the table function itself. See the billing docs for more information on data operations and how they're charged.