> ## Documentation Index
> Fetch the complete documentation index at: https://docs.adriel.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data reference

> Query model, field type mapping, refresh cadence, and limits for the Postgres data source in Adriel.

## Introduction

PostgreSQL is an open-source relational database. Customers connect their own PostgreSQL database as an Adriel data source, and any table in the `public` schema that the connecting user can read becomes available. The connector binds one Adriel data source to one table, discovers that table's columns, and lets those columns be used as metrics and breakdowns in widgets. When a widget loads, the connector auto-generates a SQL statement from the widget configuration, executes it against the connected database, and returns the result.

As a database connector, the available fields are not a fixed catalog — they are derived from the schema of the bound table. Data types are mapped from native Postgres types to Adriel field types, numeric columns become metrics, and non-numeric columns can serve as either a metric or a breakdown. Type-driven aggregation, configurable string-aggregation modes, and JSONB extraction keep the connector usable across schemas that were not designed for reporting.

To connect this data source, see [How to connect Postgres to Adriel](/data-sources/o-z/postgres/how-to-connect).

## Data refresh strategy

Postgres data is fetched **on demand**. There is no incremental sync — every dashboard, widget, or report load issues a live query against the bound table, so results always reflect what the source database returns at request time.

**No query-result cache.** Unlike a warehouse connector, query result caching is disabled for the SQL database connectors: each load re-runs the query rather than serving a stored result.

**Column-metadata cache.** The table's column list (from `information_schema.columns`) is cached for **180 seconds** per table, guarded by a distributed lock to prevent cache-miss stampedes.

**Field-metadata cache.** The V3 field metadata (column names and Adriel types) is cached for **12 hours**; columns that no longer exist in the database are removed on refresh. New columns added on the Postgres side become visible after this cache refreshes.

## Architecture levels

Database → `public` schema → Table.

1. **Database** — the connection points at a single PostgreSQL database.
2. **Schema** — table discovery walks the `public` schema only. Tables in other schemas are not directly visible.
3. **Table** — every table for which the connecting user holds SELECT privilege is surfaced (discovery joins `pg_user` with `pg_tables` and checks `has_table_privilege`).

One Adriel data source binds to one table. To expose a table in another schema, or to combine tables, create a view in `public` and bind to that.

## Date range limits

There is no platform-imposed date range limit. Query bounds are set by the widget's date-range control combined with the date column configured for the data source in Blend Data settings. If no date field is configured, no date filter is applied and the full table (subject to the row cap) is scanned. A per-data-source row cap applies via the generated `LIMIT`; a hard **100,000-row** response ceiling protects the connector regardless of configured limits.

## Query model

SQL is auto-generated from widget configuration against the bound table using a Knex query builder. Each request produces a single statement:

```sql theme={null}
SELECT <metrics>, <breakdowns>
FROM <table>
WHERE <widget filters> [AND <date range>]
GROUP BY <breakdowns>
ORDER BY <sort>
LIMIT <row cap>
```

Key rules applied by the query builder:

* **Proxy transport.** Queries are routed through a shared HTTP DB proxy by default (`shouldUseProxy = true`) for network isolation; internal Adriel cache databases bypass the proxy.
* **NULL breakdowns become "Unknown".** When a column containing NULLs (or empty / undefined values) is used as a breakdown, those values are converted to the literal string `"Unknown"` in post-processing so the group stays visible. A filter value of `Unknown` additionally matches `IS NULL`.
* **ARRAY columns avoid `unnest`.** `ARRAY` columns are serialized with a `:&:` delimiter inside `array_to_string`, then merged and deduplicated in application code after the query. `unnest` is intentionally avoided because it would multiply rows and break aggregations.
* **JSONB extraction.** JSONB sub-keys are projected as breakdowns using the `column->>'key'` accessor; filter operators work on the extracted values.

For TEXT and other non-summable columns being aggregated, a string-aggregation mode controls how multiple values in a group are combined:

| Mode                      | Behavior                                                                                |
| ------------------------- | --------------------------------------------------------------------------------------- |
| `truncatedList` (default) | Up to 5 distinct values; literal `AND_MORE` appended if more exist                      |
| `uniqueOrMultiValuesKey`  | Single value if only one distinct exists in the group, otherwise literal `MULTI_VALUES` |
| `uniqueOrNone`            | Single value if only one distinct exists in the group, otherwise `null`                 |

<Note>
  **Outbound queries originate from the DB proxy**

  Queries reach the customer database from the DB proxy's egress addresses, not from the customer's own network. Firewalls and `pg_hba.conf` rules must allow those addresses, or the connection must use an SSH tunnel. A read-only database role is recommended so the connector can only run `SELECT` statements.
</Note>

## Filters

Widget filters translate into Postgres WHERE conditions using a whitelisted operator set. Date-range filtering is applied separately from the configured date column.

### Supported filter operators

The following operators are enabled for widget filters on Postgres data sources:

* `EQUAL`
* `IN` (values including `Unknown` also match `IS NULL`)
* `LIKE`
* `STARTS_WITH`
* `ENDS_WITH`
* `REGEXP` (validated for safety; unsafe patterns are rejected)
* `EXIST`

The logical combinators `OR`, `AND`, and `NOT` are supported and can be nested. `GREATER_THAN` and `LESS_THAN` are defined but **not** enabled for Postgres widget filters; for numeric range conditions, apply a pre-filter or expose a view.

### Data-source pre-filters

Any WHERE field that is not already part of the breakdowns or metrics is automatically appended to the breakdown list so the row survives later revalidation. A field prefixed with `RAW:` is injected as raw SQL (advanced use), and `products_ids` uses the Postgres array-overlap operator (`&&`). These pre-filters narrow the result before widget filters apply.

### Date-range filtering

Date-range filtering is applied to the column configured as the date field in **Blend Data settings** (the field prefix is stripped before SQL generation). The generated clause depends on the column's `dateFormat`:

| `dateFormat`                  | Handling                                                           |
| ----------------------------- | ------------------------------------------------------------------ |
| `ISO`, `TIMESTAMP_TZ`         | Cast to `DATE` and compared to ISO date strings                    |
| `TIMESTAMP_MS`, `TIMESTAMP_S` | Cast to `bigint` and compared to millisecond / second epoch bounds |
| `EPOCH_TIMESTAMP`             | `to_timestamp(...)` compared to date strings                       |
| custom format                 | `TO_DATE(...)` with the supplied format string                     |
| (none)                        | Plain string comparison (`field >= from AND field <= to`)          |

For the `TIMESTAMP_MS` and `TIMESTAMP_S` formats, boundary calculations use a configurable timezone (`dateFieldTimezone`, default `Asia/Seoul`).

## Field type mapping

Postgres has no fixed metric or breakdown catalog. The available fields are the columns of the bound table, resolved from `information_schema.columns` at query time.

<Note>
  **How to read the columns**

  The **Data type** column uses the platform's ten-value vocabulary: **Number**, **Currency**, **Percentage**, **Ratio**, **Duration**, **Date**, **Text**, **URL**, **Array**, **Boolean**. The **Field role** column indicates whether the field can serve as a metric, a breakdown, or both.
</Note>

<Note>
  **`_id` columns are always text**

  Any column whose name ends in `_id` or `ID` (or matches `variants.barcode`) is treated as non-summable text even when its native type is numeric, so identifier columns behave as breakdowns rather than metrics.
</Note>

Each native Postgres type maps to an Adriel field type as follows.

| Postgres native type                                                                | Adriel field type     | Data type | Field role                                                         |
| ----------------------------------------------------------------------------------- | --------------------- | --------- | ------------------------------------------------------------------ |
| `smallint`, `integer`, `bigint`, `smallserial`, `serial`, `bigserial`               | `number`              | Number    | Metric                                                             |
| `decimal`, `numeric`, `real`, `double precision`                                    | `fullNumber`          | Number    | Metric                                                             |
| `date`, `timestamp`, `timestamp with time zone`                                     | `date`                | Date      | Metric or breakdown; used for date-range filtering when configured |
| `ARRAY`                                                                             | `array`               | Array     | Breakdown; joined for display                                      |
| `jsonb`, `json`                                                                     | `object`              | Text      | Breakdown; sub-keys accessed via `column->>'key'`                  |
| `character varying`, `text`, `char`, `uuid`, `bytea`, `boolean`, and any other type | `string`              | Text      | Metric or breakdown                                                |
| Column name ends in `_id` / `ID`                                                    | `string` *(override)* | Text      | Breakdown                                                          |

<Note>
  **Postgres has no dedicated Boolean field type**

  Unlike the BigQuery connector, the Postgres type map classifies only numeric columns as numbers; every other native type — including `BOOLEAN` — falls through to `string`. Boolean columns therefore surface as **Text**, not **Boolean**.
</Note>

### Adriel-added fields

Adriel adds one synthetic field on top of the table's own columns.

| Field     | Description                                                                 | Data type | API Key             |
| --------- | --------------------------------------------------------------------------- | --------- | ------------------- |
| Row count | Total number of rows in the bound table (or in the widget-filtered subset). | Number    | `postgres:rowCount` |

### Aggregation defaults

When no explicit aggregation is set on a field, the query builder chooses one from the column's type category:

| Type category             | Types                                                                                                   | Default aggregation                                                                        |
| ------------------------- | ------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ |
| `SUM_TYPES_POSTGRES`      | `smallint`, `integer`, `bigint`, `decimal`, `numeric`, `real`, `double precision`, and the serial types | `SUM(COALESCE(CAST(x AS double precision), 0))`                                            |
| `CANT_SUM_TYPES_POSTGRES` | `character varying`, `text`, `date`, `timestamp with time zone`                                         | String aggregation (`truncatedList` by default)                                            |
| `CONCAT_TYPES_POSTGRES`   | `ARRAY`                                                                                                 | `array_agg(array_to_string(x, ':&:'))`, then merged and deduplicated                       |
| `CONCAT_TYPES_POSTGRES`   | `jsonb`                                                                                                 | `array_agg(...)`; a `->>` property field is summed as `SUM(CAST(... AS double precision))` |
| Non-summable override     | Columns ending in `_id` / `ID` or matching `variants.barcode`                                           | String aggregation, even when the native type is numeric                                   |

Per-field overrides can replace the default via advanced settings. The supported override aggregations are `sum`, `mean` (`AVG`), `median` (percentile-based), `min`, `max`, `unique` (`COUNT(DISTINCT)`), and `custom.creativeUrl` (detects a URL pattern for creative-image dashboards).

## Limitations

Based on the current connector:

* **One table per data source.** Multi-table joins and tables outside the `public` schema are not supported directly — pre-join or expose them as a view in `public`.
* **`public` schema only.** Table discovery is limited to tables in the `public` schema for which the connecting user has SELECT privilege.
* **Filter operator whitelist.** Only `EQUAL`, `IN`, `LIKE`, `STARTS_WITH`, `ENDS_WITH`, `REGEXP`, and `EXIST` (plus `OR` / `AND` / `NOT`) are available in widget filters; `GREATER_THAN` and `LESS_THAN` are not enabled.
* **No `unnest` for arrays.** `ARRAY` columns are serialized and deduplicated in application code; per-element expansion is intentionally avoided.
* **Proxy egress addresses must be reachable.** Queries originate from the DB proxy, so the customer's firewall and `pg_hba.conf` must allow those addresses (or use an SSH tunnel).
* **12-hour field-metadata cache.** New or removed columns become visible only after the field cache refreshes (up to 12 hours).
* **100,000-row response ceiling.** A hard maximum caps the rows returned to the connector regardless of the configured row limit.
* **No dedicated Boolean type.** Boolean columns surface as Text (see Field type mapping).

## API references

* [PostgreSQL documentation](https://www.postgresql.org/docs/)
* [Data types](https://www.postgresql.org/docs/current/datatype.html)
* [JSON functions and operators (`->>`)](https://www.postgresql.org/docs/current/functions-json.html)
* [`array_to_string` and array functions](https://www.postgresql.org/docs/current/functions-array.html)
* [Client authentication (`pg_hba.conf`)](https://www.postgresql.org/docs/current/client-authentication.html)

## See also

* [How to connect Postgres](/data-sources/o-z/postgres/how-to-connect) (paired how-to)
* [MySQL data reference](/data-sources/g-n/mysql/data-reference) — alternative SQL database
* [MongoDB data reference](/data-sources/g-n/mongodb/data-reference) — document database alternative
* [Redshift data reference](/data-sources/o-z/redshift/data-reference) — cloud data warehouse alternative
* [Google BigQuery data reference](/data-sources/g-n/google-bigquery/data-reference) — cloud data warehouse alternative