Key takeaways
- Work read-only and qualify tables with their schema.
- COUNT(*) and COUNT(column) answer different questions.
- Nulls and integer division cause quiet reporting errors.
- Use EXPLAIN ANALYZE to understand a slow report query.
On this page
Start with read-only access
Reporting analysts should connect with a role that can read the schemas they need and nothing more. A read-only role prevents an accidental update during exploration and makes it clear that the warehouse is not an operational system.
Qualify table names with the schema, such as reporting.orders, rather than relying on the search path. Explicit names survive a change in session settings and make it obvious which layer a query uses.
- Use a dedicated read-only login for reporting.
- Set a statement timeout for exploratory queries.
- Qualify tables with the schema.
- Ask for access to a view rather than a base table when one exists.
Select and aggregate deliberately
Avoid SELECT * in saved queries. It transfers columns you do not need and breaks when the table changes. List the columns, and give computed columns clear aliases.
COUNT(*) counts rows. COUNT(column) counts non-null values in that column. COUNT(DISTINCT column) counts distinct non-null values. Choosing the wrong one changes a customer count or a response rate without any error.
| Expression | What it counts |
|---|---|
| COUNT(*) | All rows, including rows with nulls |
| COUNT(email) | Rows where email is not null |
| COUNT(DISTINCT customer_id) | Unique non-null customer IDs |
| SUM(amount) FILTER (WHERE status = 'paid') | Paid amount only |
| AVG(amount) | Average of non-null amounts |
Handle nulls and types explicitly
NULL means unknown, not zero and not an empty string. Comparing a column with = NULL returns unknown, so the row is filtered out. Use IS NULL or IS NOT NULL. Use COALESCE to substitute a value when null should be treated as zero in a total.
Integer division truncates. Dividing two integer columns can silently drop the decimal part of a rate or an average. Cast one side to numeric, or multiply by 1.0, before dividing. Check the column types before trusting a percentage.
SELECT
customer_id,
COALESCE(SUM(amount), 0) AS revenue,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'paid') / NULLIF(COUNT(*), 0),
1
) AS paid_percent
FROM invoices
GROUP BY customer_id;Treat dates and time zones carefully
PostgreSQL has date, timestamp without time zone, and timestamp with time zone. A timestamptz stores an instant in UTC and displays it in the session time zone. Two sessions can therefore show different local times for the same row.
For reporting, convert to the business time zone before truncating to a day or month: order_ts AT TIME ZONE 'Europe/London'. Without that step, late-evening orders can fall into the next day or the previous month.
- Use date for business dates that have no time.
- Use timestamptz for events that happen at an instant.
- Convert to the reporting time zone before grouping.
- Filter with half-open ranges, not BETWEEN, for timestamps.
Join and structure with CTEs
Common table expressions make a report readable by naming each step. Aggregate first, join second, and filter last where possible. That order keeps the row count predictable and makes each part testable on its own.
PostgreSQL also supports window functions, FILTER, DISTINCT ON, and LATERAL joins. Use them when they make the query clearer. DISTINCT ON returns the first row per group according to an ORDER BY, which is useful for latest-record lookups.
Check performance and safety
Run EXPLAIN ANALYZE on a slow query to see the plan and the actual time. A sequential scan on a large table may be fine for a full-month aggregate and costly for a single customer lookup. The plan tells you which case you have.
Limit exploratory queries, and avoid running a heavy report against a production system during peak hours. If a query must scan a large table repeatedly, ask whether a summary table or an index would serve the report better.
- Read the estimated rows against the actual rows in EXPLAIN ANALYZE.
- Check for repeated scans caused by the same CTE or subquery.
- Use LIMIT while exploring, then remove it for the real total.
- Test the query on a closed period where a trusted total exists.
Frequently asked questions
Is PostgreSQL internals knowledge needed for reports?+
No. You need the query language, a clear understanding of the data model, and enough of EXPLAIN to recognise an expensive plan.
What is the difference between timestamp and timestamptz?+
Timestamp without time zone stores a wall-clock value with no zone. Timestamptz stores an instant and displays it in the session time zone.
Why is a percentage showing as zero?+
Integer division probably truncated it. Cast a value to numeric or multiply by 1.0 before dividing.
Is DISTINCT ON portable?+
It is specific to PostgreSQL. Other databases use window functions or a different syntax. Use it when you are writing PostgreSQL-only queries.
Need help applying this?
Improve your data cleanup & pipelines.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
A data quality checklist before starting a BI project
The checks that prevent a dashboard from failing after launch: ownership, definitions, completeness, duplicates, and reconciliation.
When to move from spreadsheets to a database
The signals that a spreadsheet has become a database, and the simplest path to a more reliable system.