---
title: PostgreSQL table function
meta:
    description: Documentation for the Tinybird PostgreSQL table function
---

# PostgreSQL table function

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](../table-functions) for general information and tips.

## Setting secrets

Table functions require authentication credentials that must be stored securely. In Tinybird Forward, manage those credentials with `tb secret`:

```shell
tb secret set PG_USERNAME <username>
tb secret set PG_PASSWORD <password>
```

Set the secret in each environment where the copy pipe runs. For example, use `tb --cloud secret set` for Cloud and `tb --branch=<branch_name> secret set` for a branch. Then reference the secrets in SQL with `tb_secret()`.

In Tinybird Classic, use the [Environment Variables API](/api-reference/environment-variables-api) to create the same secret values.

For more details, see [tb secret](/forward/dev-reference/commands/tb-secret).

## Syntax

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

```sql {% title="Example query logic" %}
SELECT *
FROM postgresql(
  'postgresql.example.com:5432',
  '<YOUR_PG_DB>',
  '<YOUR_PG_TABLE>',
  {{ tb_secret("PG_USERNAME") }},
  {{ tb_secret("PG_PASSWORD") }}
)

TYPE COPY
TARGET_DATASOURCE pg_copy_target_ds
```

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

## Example: sync orders from PostgreSQL

The following example copies an `orders` table from PostgreSQL into a Tinybird Data Source every 5 minutes.

First, define the target Data Source. Then define a copy pipe that reads from PostgreSQL and appends rows updated in the last 10 minutes.

{% tabs initial="Tinybird CLI" %}
{% tab label="Tinybird CLI" %}

```tb {% title="datasources/pg_orders.datasource" %}
SCHEMA >
    `order_id` UInt64,
    `customer_id` UInt64,
    `status` String,
    `amount` Float64,
    `updated_at` DateTime

ENGINE "ReplacingMergeTree(updated_at)"
ENGINE_SORTING_KEY "order_id"
```

```tb {% title="pipes/pg_orders_sync.pipe" %}
NODE updated_orders
SQL >
    %
    SELECT
        order_id,
        customer_id,
        status,
        amount,
        updated_at
    FROM postgresql(
        'postgres.example.com:5432',
        'shop',
        'orders',
        {{ tb_secret("PG_USERNAME") }},
        {{ tb_secret("PG_PASSWORD") }}
    )
    WHERE updated_at >= now() - INTERVAL 10 MINUTE

TYPE copy
TARGET_DATASOURCE pg_orders
COPY_MODE append
COPY_SCHEDULE */5 * * * *
```

{% /tab %}

{% tab label="TypeScript SDK" %}

```ts {% title="tinybird.ts" %}
import { defineCopyPipe, defineDatasource, engine, node, t } from "@tinybirdco/sdk";

export const pgOrders = defineDatasource("pg_orders", {
  schema: {
    order_id: t.uint64(),
    customer_id: t.uint64(),
    status: t.string(),
    amount: t.float64(),
    updated_at: t.dateTime(),
  },
  engine: engine.replacingMergeTree({
    sortingKey: ["order_id"],
    ver: "updated_at",
  }),
  jsonPaths: false,
});

export const pgOrdersSync = defineCopyPipe("pg_orders_sync", {
  datasource: pgOrders,
  schedule: "*/5 * * * *",
  mode: "append",
  nodes: [
    node({
      name: "updated_orders",
      sql: `
        SELECT
          order_id,
          customer_id,
          status,
          amount,
          updated_at
        FROM postgresql(
          'postgres.example.com:5432',
          'shop',
          'orders',
          {{ tb_secret("PG_USERNAME") }},
          {{ tb_secret("PG_PASSWORD") }}
        )
        WHERE updated_at >= now() - INTERVAL 10 MINUTE
      `,
    }),
  ],
});
```

{% /tab %}

{% tab label="Python SDK" %}

```python {% title="tinybird.py" %}
from tinybird_sdk import define_copy_pipe, define_datasource, engine, node, t

pg_orders = define_datasource("pg_orders", {
    "schema": {
        "order_id": t.uint64(),
        "customer_id": t.uint64(),
        "status": t.string(),
        "amount": t.float64(),
        "updated_at": t.date_time(),
    },
    "engine": engine.replacing_merge_tree({
        "sorting_key": ["order_id"],
        "ver": "updated_at",
    }),
    "json_paths": False,
})

pg_orders_sync = define_copy_pipe("pg_orders_sync", {
    "datasource": pg_orders,
    "copy_schedule": "*/5 * * * *",
    "copy_mode": "append",
    "nodes": [
        node({
            "name": "updated_orders",
            "sql": """
                SELECT
                    order_id,
                    customer_id,
                    status,
                    amount,
                    updated_at
                FROM postgresql(
                    'postgres.example.com:5432',
                    'shop',
                    'orders',
                    {{ tb_secret("PG_USERNAME") }},
                    {{ tb_secret("PG_PASSWORD") }}
                )
                WHERE updated_at >= now() - INTERVAL 10 MINUTE
            """,
        }),
    ],
})
```

{% /tab %}
{% /tabs %}

Use `ReplacingMergeTree(updated_at)` when the source can send multiple versions of the same row. Query with `FINAL` when you need the latest version at read time.

## Type support and inference

Here's a detailed conversion table:

| PostgreSQL data type | Tinybird 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 |
| JSONB | String |
| INET | String |
| CIDR | String |
| MACADDR | String |
| ENUM | Enum8 or Enum16 |
| GEOMETRY | String |

## 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.

## Use the PostgreSQL table function locally

There are two primary scenarios for connecting to a PostgreSQL database from Tinybird Local:

### Connect to PostgreSQL running on your host machine

When connecting to PostgreSQL running directly on your local machine (not in a container), keep the following considerations in mind:

1. **Network connection**: The connection to your PostgreSQL server originates from within the Tinybird Local container.
2. **Server reachability**: Ensure your PostgreSQL server is reachable from inside the Docker network.
3. **Credentials**: Set the secrets for your PostgreSQL credentials in your local Tinybird project. You can provide default values for these credentials inside the pipe SQL, but not through the CLI:

```shell
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:

```sql
NODE get_ids
SQL >
%
SELECT id
FROM postgresql(
  'host.docker.internal:5432',
  '<YOUR_PG_DB>',
  '<YOUR_PG_TABLE>',
  {{ tb_secret("PG_USERNAME", "<YOUR_DEFAULT_USERNAME>") }},
  {{ tb_secret("PG_PASSWORD", "<YOUR_DEFAULT_PWD>") }}
)

TYPE COPY
TARGET_DATASOURCE pg_copy_target_ds
```

### Connect to PostgreSQL running in a Docker container

When connecting to a PostgreSQL container running in Docker, follow these steps to set up network communication between Tinybird Local and your PostgreSQL container:

1. Create a shared Docker network for PostgreSQL and Tinybird Local to communicate:

```shell
docker network create tbnet
```

2. Run PostgreSQL container in the shared network:

```shell
docker run --name local-postgres \
  --network tbnet \
  -e POSTGRES_USER=tb_user \
  -e POSTGRES_PASSWORD=tb_pass \
  -e POSTGRES_DB=test_db \
  -p 5432:5432 \
  -d postgres:15
```

3. Connect Tinybird Local to the shared network:

```shell
docker network connect tbnet tinybird-local
```

4. Verify network connectivity by checking that both containers are on the same network. The NetworkID, Gateway, and IPAddress values should match:

```shell
docker inspect tinybird-local --format '{{json .NetworkSettings.Networks}}' | jq
docker inspect local-postgres --format '{{json .NetworkSettings.Networks}}' | jq
```

5. Set secrets in Tinybird Local to match your PostgreSQL container configuration:

```shell
tb secret set PG_USERNAME tb_user
tb secret set PG_PASSWORD tb_pass
```

6. Update the PostgreSQL host in your query to use the container name as the hostname:

```sql
NODE get_ids
SQL >
%
SELECT id
FROM postgresql(
  'local-postgres:5432',
  '<YOUR_PG_DB>',
  '<YOUR_PG_TABLE>',
  {{ tb_secret("PG_USERNAME") }},
  {{ tb_secret("PG_PASSWORD") }}
)

TYPE COPY
TARGET_DATASOURCE pg_copy_target_ds
```

7. Build and deploy your pipe:

```shell
tb build
tb deploy
```

8. Test that the connection works. Run the copy pipe with `tb copy run <pipe_name>` and check that PostgreSQL data lands in the target Data Source.

## 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](../table-functions)
{% - [Copy pipes](/forward/core-concepts/copy-pipes) /%}