PostgreSQL Queries

PostgreSQL queries are queries written in the Compass module against a PostgreSQL data source added in Port, using standard SQL and PostgreSQL extensions.

Strengths

Feature	Description
Full ANSI SQL	Full support for JOIN, CTE (`WITH`), window functions, HAVING
TimescaleDB Compatibility	High-frequency time-series queries with `time_bucket()`
JSONB Support	Query semi-structured data with `data->>'key'`
Schema Organization	Multi-schema database structure with `schema_name.table_name`
Advanced Type Casting	Type casting via `::` such as `::timestamp`, `::numeric`
Array Support	Array operations with `ARRAY_AGG()`, `UNNEST()`

Common Query Patterns in Pirivision

Demo Factory dataset

The examples below use the Demo Factory PostgreSQL schema: sensor_zaman_serisi (30 days of temperature/pressure), vardiya_uretim (shift production), oee_gunluk, alarm_log, duris_log, recete_parametre plus the hatlar / makineler / urun_aileleri lookup tables. Port data source name: Demo Fabrika - PostgreSQL.

To generate the same data in your own environment → Synthetic Data Generation — PostgreSQL

-- Instant KPI (latest OEE value)
SELECT ROUND(oee_yuzde, 1) AS oee
FROM   oee_gunluk
ORDER  BY ts DESC
LIMIT  1;

-- Time series — last 24 hours, parameterized line (hat)
SELECT ts,
       sicaklik AS sicaklik_c,
       basinc   AS basinc_bar,
       makine_id
FROM   sensor_zaman_serisi
WHERE  ts >= NOW() - INTERVAL '24 hours'
  AND  hat_id = {{hat_id}}
ORDER  BY ts;

-- TimescaleDB / DATE_TRUNC — hourly average
SELECT
    DATE_TRUNC('hour', ts) AS saat,
    makine_id,
    AVG(sicaklik)          AS ort_sicaklik
FROM   sensor_zaman_serisi
WHERE  ts >= NOW() - INTERVAL '7 days'
GROUP  BY saat, makine_id
ORDER  BY saat;

-- GROUP BY summary — OEE by line
SELECT hat_id,
       AVG(oee_yuzde)::numeric(5,1) AS oee
FROM   oee_gunluk
WHERE  ts >= CURRENT_DATE - 7
GROUP  BY hat_id
ORDER  BY hat_id;

Important PostgreSQL Functions

Category	Function	Example
Time	`NOW()`	`WHERE ts > NOW() - INTERVAL '1 hour'`
Time	`DATE_TRUNC()`	`DATE_TRUNC('day', ts)`
Time	`EXTRACT()`	`EXTRACT(HOUR FROM ts)`
Time	`CURRENT_DATE`	`WHERE ts >= CURRENT_DATE - 7`
Type	`::type`	`ts::date`, `sicaklik::numeric(5,1)`
Text	`TO_CHAR()`	`TO_CHAR(ts, 'DD.MM.YYYY HH24:MI')`
Text	`CONCAT()`	`CONCAT(deger, ' ', birim)`
Aggregation	`PERCENTILE_CONT`	`PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY sicaklik)`
TimescaleDB	`time_bucket()`	`time_bucket('1 hour', ts)`

Requirements

Requirement	Description
PostgreSQL data source in Port	Connection must be tested
SELECT permission	The DB user must have permission on the queried tables

Guides

Guide	Description
Create PostgreSQL Query	Write a new PostgreSQL query in Compass
Edit PostgreSQL Query	Update an existing query
Move Query	Move to a different folder
Duplicate Query	Copy a query
Delete Query	Remove a query

→ Create PostgreSQL Query