Data reference

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

Database — the connection points at a single PostgreSQL database.
Schema — table discovery walks the public schema only. Tables in other schemas are not directly visible.
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:

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`

Outbound queries originate from the DB proxyQueries 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.

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.

How to read the columnsThe 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.

_id columns are always textAny 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.

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

Postgres has no dedicated Boolean field typeUnlike 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.

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

Introduction

Data refresh strategy

Architecture levels

Date range limits

Query model

Filters

Supported filter operators

Data-source pre-filters

Date-range filtering

Field type mapping

Adriel-added fields

Aggregation defaults

Limitations

API references

See also

​Introduction

​Data refresh strategy

​Architecture levels

​Date range limits

​Query model

​Filters

​Supported filter operators

​Data-source pre-filters

​Date-range filtering

​Field type mapping

​Adriel-added fields

​Aggregation defaults

​Limitations

​API references

​See also

Introduction

Data refresh strategy

Architecture levels

Date range limits

Query model

Filters

Supported filter operators

Data-source pre-filters

Date-range filtering

Field type mapping

Adriel-added fields

Aggregation defaults

Limitations

API references

See also