5 posts tagged with "SQL"

Filtering a jsonb field's "empty" values with IS NOT NULL lets rows whose value is JSON null sail right through — the data is empty, yet the predicate says otherwise.

Encountered this while building AI Ops — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operational strategy. Analysis decision snapshots are stored as jsonb; rows where no rule fired get JSON null written in, which silently corrupted the "has rule output" predicate.

Symptom: IS NOT NULL doesn't catch the "empty" value​

field->'key' IS NOT NULL returns true for rows whose value is JSON null — the filter does nothing, and rows that should be excluded leak into the result set. If you searched for "jsonb is not null not working", "jsonb null filter" or "postgres json null check", this is the same issue.

Root cause: JSON null is a valid jsonb value, not SQL NULL​

PostgreSQL has two kinds of "empty" here: SQL NULL means no value exists; JSON null is a legitimate jsonb value. The -> operator retrieves what the key holds — for JSON null, that's a perfectly valid value, and IS NOT NULL answers "did I get something back?", not "is it meaningful?". Verified behavior matrix (tested on a PostgreSQL 16 container):

Two combinations bite most often:

• IS NOT NULL cannot tell JSON null from a real value — the first row, exactly where the misjudgment comes from.
• Extracting text with ->> and testing IS NULL fails the same way — JSON null and a missing key both become SQL NULL (last row). Reaching for ->> to dodge the trap lands you right back in it.

The fix: ? for key existence, jsonb_typeof for value type​

Two different intents need two different tools:

-- "key exists": value irrelevant
SELECT '{"a":null}'::jsonb ? 'a';                          -- true

-- "has a real value": assert the JSON type; 'null', missing keys all excluded
SELECT jsonb_typeof(config->'rule_output') = 'array';      -- arrays only
SELECT jsonb_typeof(config->'rule_output') = 'object';     -- objects only

-- find rows that hold a JSON null (data triage)
SELECT * FROM t WHERE jsonb_typeof(config->'rule_output') = 'null';

Our concrete case: the rule engine writes JSON null into decision snapshots when no rule fired, and a downstream count used ->'rule_output' IS NOT NULL as "has rule output" — the metric was simply wrong. The fix tightened the assertion to jsonb_typeof(...) = 'array', excluding JSON null, missing keys and every other type in one stroke.

Boundary cases: JSON null is not always wrong​

To be clear: JSON null is a legitimate design — "key exists but empty" and "key absent" are different business semantics; an API returning "extra": null is not the same as omitting extra. The data isn't wrong; the predicate is. Three intents, three spellings:

A related trap lives on the update side: jsonb_set(config, '{rule_output}', 'null') writes a JSON null, while jsonb_set(config, '{rule_output}', NULL) deletes the key entirely — JSON vs SQL NULL as the argument flips the meaning. For other "query results don't match expectations" hunts, cross-query granularity mismatches causing dangling references are another frequent root cause worth comparing.

FAQ​

What is the difference between JSON null and SQL NULL in Postgres jsonb?​

JSON null is a valid jsonb value; SQL NULL means the value does not exist. Tested: ('{"a":null}'::jsonb -> 'a') IS NOT NULL returns true; jsonb_typeof returns the string 'null' for the former and SQL NULL for a missing key.

Why doesn't IS NOT NULL filter out empty jsonb values?​

->'key' returns a valid jsonb value for JSON null, so IS NOT NULL is trivially true. Assert the type instead — jsonb_typeof(field->'key') = 'array' — or check key existence with the ? operator.

How do I tell a JSON null value from a missing key in jsonb?​

Use the ? operator for key existence — it returns true even when the value is JSON null. jsonb_typeof returns the string 'null' for a JSON null value and SQL NULL for a missing key, which tells the two apart.

While aggregating daily ad data into weekly reports, the ratio metrics — PPC, CPM, ROI — jumped by tens of times: PPC went from 4.70 in the daily detail to 111.39 in the weekly report.

Encountered this while building AI Ops — LLM-powered analysis that surfaces market trends, user behavior, and sales insights to drive precise operations strategy. The ad data API returns daily detail rows, which must be aggregated to week granularity before feeding reports. The first check after launch: PPC 111.39 (daily actual 4.70), CPM 1585.9 (daily actual 68.6), ROI 169.98 (daily actual 1.82) — all 13 ratio/average columns distorted.

TL;DR​

Applying SUM directly to ratio/average columns like CTR, PPC, ROI in an aggregation query yields "the sum of daily ratios," not a mean — values inflate proportionally to the number of days aggregated. Two rules: aggregate only additive columns (impressions, clicks, cost — the totals), skipping every ratio column; recompute ratios from the totals after aggregating (cost ÷ clicks, clicks ÷ impressions). If you only have ratios without the underlying totals, take a weighted average using the denominator as weight — never a plain average.

The Symptom​

The aggregation code SUMmed "every numeric column," silently sweeping ratio columns along:

-- Wrong: SUM every column
SELECT
  campaign_id,
  date_trunc('week', day) AS week,
  SUM(clicks)      AS clicks,
  SUM(impressions) AS impressions,
  SUM(ppc)         AS ppc,   -- sum of 7 daily PPC values!
  SUM(ctr)         AS ctr,   -- sum of 7 daily CTR values!
  SUM(roi)         AS roi    -- sum of 7 daily ROI values!
FROM daily_ad_report
GROUP BY campaign_id, date_trunc('week', day);

Measured comparison (one campaign, one week):

No errors anywhere: data ingested normally, reports rendered normally — only by comparing the weekly report against daily details side by side could you see values off by one to two orders of magnitude.

Root Cause​

Ratios and averages are non-additive derived quantities. Each day's ppc = cost / clicks has a different denominator; adding 7 daily PPC values mathematically yields "a sum of 7 relative values," which has no business meaning. CTR and ROI are the same.

"Sum all numeric columns" is a silent trap. Aggregation code usually loops over columns uniformly, and ratio columns slip in without error or warning — results just quietly distort. The more columns you have, the harder it is to spot by eye.

An ROI inflated 93× is actually more deceptive. It reads as "outstanding ad performance." If downstream consumers read the weekly report directly to make budget decisions, the wrong number propagates all the way into operational actions. In this incident, several read-only consumers queried the weekly table directly — until the table was corrected, every consumer was reading wrong data.

The Fix​

Step 1: Keep only additive columns in the aggregation​

CREATE VIEW weekly_ad_totals AS
SELECT
  campaign_id,
  date_trunc('week', day) AS week,
  SUM(impressions) AS impressions,
  SUM(clicks)      AS clicks,
  SUM(cost)        AS cost,
  SUM(gmv)         AS gmv
FROM daily_ad_report
GROUP BY campaign_id, date_trunc('week', day);

Additive columns are counts/totals (impressions, clicks, cost, orders) — summing them across time intervals still means something.

Step 2: Recompute all ratios from totals after aggregating​

SELECT
  campaign_id,
  week,
  impressions,
  clicks,
  cost,
  CASE WHEN clicks > 0
       THEN cost / NULLIF(clicks, 0)::numeric
       ELSE 0 END AS ppc,
  CASE WHEN impressions > 0
       THEN clicks::numeric / NULLIF(impressions, 0)
       ELSE 0 END AS ctr,
  CASE WHEN cost > 0
       THEN (gmv - cost)::numeric / NULLIF(cost, 0)
       ELSE 0 END AS roi
FROM weekly_ad_totals;

Two details: PostgreSQL integer division truncates, so cast with ::numeric before dividing; return 0 when the denominator is 0, keeping the same convention as the daily layer.

Step 3: When you only have ratios, use a weighted average​

-- Aggregate daily CTR weighted by impressions (expanded form: SUM(ctr × impressions) / SUM(impressions))
SELECT
  date_trunc('week', day) AS week,
  SUM(clicks)::numeric / NULLIF(SUM(impressions), 0) AS ctr_weighted
FROM daily_ad_report
GROUP BY date_trunc('week', day);

A weighted average is essentially "reconstruct numerator and denominator, then divide" — as long as you can still access the weight column, always prefer it over a plain average.

After the fix, all 13 ratio columns in the weekly report matched the measured daily values. Historical dirty data was backfilled with the same formulas, and read-only downstream consumers became correct automatically — without changing a single line of code.

FAQ​

Can you sum percentages?​

No. Percentages and ratios are relative values with different denominators per row — SUMming them yields a sum of N relative values, inflated by the number of periods aggregated, with no business meaning. The correct approach is to skip ratio columns during aggregation and recompute from totals afterward (clicks ÷ impressions, cost ÷ clicks); when you only have ratios without totals, take a weighted average using the denominator as weight.

Can you add percentages together to get an average?​

Only when the denominators are identical. Averaging percentages with different denominators is an unweighted average that skews toward small-denominator items (extreme ratios from low-traffic days get amplified). The correct approach is to sum numerators and denominators separately, then divide — mathematically equivalent to a weighted average by denominator.

While triaging a dashboard lead in an ad-data pipeline: an offer was flagged "suspected paused" in the diagnostic detail, complete with a reinvestment suggestion — yet the "suggested actions" list had no row for it. The detail page pointed at a table that didn't contain it.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; the diagnostic detail and the suggestion list come from two adjacent queries in the pipeline.

TL;DR​

Two queries defined the same business predicate ("suspected paused") at different aggregation granularity: the detail query judged at campaign×offer level — any single campaign with no spend in the last week flags it; the candidate list judged at whole-offer level — all campaigns must be spend-free to qualify. One offer still had spend (3.09) in one campaign, so the detail flagged it while the summary skipped it, leaving a dangling reference in the UI. Three fixes: unify the definition at the coarser granularity, make the predicate CTEs verbatim-identical, and verify row-set containment before cross-referencing.

Symptoms​

Two queries, each with its own definition of "suspected paused":

-- Query 4, diagnostic detail: pair granularity (campaign × offer)
WITH consumption_paused_q4 AS (
  SELECT offer_id, campaign_id
  FROM spend_weekly
  GROUP BY offer_id, campaign_id        -- each campaign judged alone
  HAVING SUM(spend) FILTER (WHERE is_last_week) = 0
)

-- Query 5, suggestion candidates: offer granularity (summed across campaigns)
WITH suspected_paused_q5 AS (
  SELECT offer_id
  FROM spend_weekly
  GROUP BY offer_id                     -- the whole offer judged together
  HAVING SUM(spend) FILTER (WHERE is_last_week) = 0
)

The problematic offer hung under multiple campaigns, one of which still had spend (3.09) in its last week:

Query 4 (pair level):   campaign A last-week spend = 0    → flagged ✓, suggestion attached
Query 5 (offer level):  summed last-week spend = 3.09     → excluded ✗
UI: detail says "see the suggestion table"; the suggestion table has no such row

No errors, plausible totals — the dangling reference only surfaces when someone follows a specific detail row.

Root Cause​

The "same source" contract had a coverage gap. The pipeline spec requires the feature-column CTEs of adjacent queries to be verbatim identical — that rule was followed to the letter. But it only covers feature columns; the predicate set (the business judgment before WHERE) was out of scope. "Suspected paused" was implemented twice, at different granularities: pair-level judgment is sensitive to "one campaign has no spend", offer-level judgment to "all campaigns have no spend". For any offer spanning both situations, the two queries must disagree — the overlap zone is mathematically guaranteed.

Cross-query referencing amplified the fork into a dangling reference: the UI treats query 4's rows as details and query 5's table as the entry point, with nobody ever checking "rows(q4) ⊆ rows(q5)". Aggregation-level inconsistency is the classic data-warehouse consistency trap — our earlier post on SUM over ratio columns inflating aggregated metrics is the same disease in another organ: aggregation happening at the wrong level.

Solution​

Step 1: Decide the canonical definition before touching SQL​

The business question has one answer: "should we keep funding this offer" is an offer-level decision, so the predicate must tighten to whole-offer granularity — every attached campaign spend-free in the last week AND no operation annotation rows. Bump the rule version so the change is traceable.

Step 2: Share the predicate CTE verbatim​

Extract the predicate CTE into one text block referenced by both queries; upgrade the contract at the same time: verbatim sharing covers the predicate set (including granularity), not just feature columns. One definition, one place to change.

Step 3: Before cross-referencing, check row-set containment​

Make "marker set ⊆ target row set" a standing check (promote it to a test):

-- Dangling detection: flagged by q4 but absent from q5
SELECT q4.offer_id
FROM consumption_paused_q4 q4
LEFT JOIN suspected_paused_q5 q5 USING (offer_id)
WHERE q5.offer_id IS NULL;

Only when this returns 0 rows does the UI earn the right to render both outputs on one page.

Step 4: Replay against history before shipping​

Replay the new definition over historical snapshots: verify every verdict flip (flagged ↔ normal) is correct, with zero collateral flips and zero dangling references, then re-run production validation.

FAQ​

Why do two SQL queries return different results on the same data?​

Three usual differences: aggregation granularity (different GROUP BY dimensions — campaign×entity vs whole entity in this case), filter logic (different WHERE/HAVING conditions), and point in time. Granularity is the sneakiest: both queries are individually correct, yet their verdicts can contradict.

When do I use HAVING vs WHERE for aggregate conditions in SQL?​

WHERE filters rows before grouping; HAVING filters groups after. But before picking the keyword, pick the granularity — "what counts as one group" determines sensitivity: finer granularity flags more easily (any group qualifies), coarser granularity is conservative (all groups must qualify). Different granularity, opposite verdicts.

How do I keep data definitions consistent across reports?​

One definition per business predicate, predicate CTEs shared verbatim across queries, containment checks (A ⊆ B) before cross-query references, and versioned definition changes replayed over history. Consistency doesn't come from convention — it comes from contracts plus checks.

After switching an ads data source from daily tables to weekly tables, I found an empty table still sitting in the schema — one that was only ever CREATEd in the earliest migration, held zero rows, and was never referenced by runtime code. It even carried stale field names and unnormalized columns.

Encountered this while building AI Ops — AI-powered analytics that surfaces market trends, user behavior, and sales data to drive precise operational strategy. After this data-source switch, the write path had long since moved to the new weekly tables and a cleanup migration had already dropped the old daily table. The one thing missed was a monthly table that existed only in the baseline CREATE — it had no new writes, no corresponding DROP, and just sat dormant in the schema with its deprecated field definitions.

TL;DR​

The signature of an orphan table: only CREATEd in an early/baseline migration, zero references in current code, often with stale or unnormalized column names. Batch migrations don't touch these automatically. You have to actively list every table in the schema with information_schema.tables, compare against code references, identify the orphans, and write a DROP migration to clean them up — not just psql your way to a one-off delete.

The symptom​

A typical orphan table looks like this:

• Zero rows — the business stopped writing to it long ago;
• Zero runtime references — no SELECT/INSERT anywhere in code, only the CREATE in a migration file;
• Stale fields — column names from a previous naming convention (e.g. ad_plan_id/product_id), out of step with current standards;
• Unnormalized columns — possibly even Chinese column names that were never cleaned up.

It doesn't crash and doesn't affect production, so from a "nothing's broken online" perspective it's invisible. But the harm is implicit: it misleads newcomers into thinking it's still in use, pollutes the schema namespace, adds noise to cross-table audits, and could be read as dirty data by some mistaken SELECT *.

Root cause​

Database migrations follow a pervasive pattern: migrations are "additive".

A data-source switch typically evolves like this:

1. An early baseline migration CREATEs a batch of tables (daily, monthly);
2. Once the business runs, the write path starts depending on them;
3. Requirements change, new tables (weekly) are introduced, and writes migrate over;
4. The old daily table's writes stop, and a migration DROPs it;
5. But the monthly table (or any table that was only ever CREATEd in the baseline and never directly used by the write path) gets no corresponding DROP.

The problem is step 5: migration attention focuses on "tables in use right now" — which ones are being written, which ones queries hit. A table that "once existed but never entered the main path" is in neither the write path nor the query path, so it never triggers a DROP and becomes an orphan. This is the same family as Airflow DAG metadata lingering after deletion: "removed the entry, forgot to clean the structure" — a high-frequency failure mode in migration work.

The fix​

Core flow: list all tables → compare references → confirm empty → write a DROP migration → verify.

Step 1: list all base tables in the schema with information_schema​

-- List all base tables in a schema (exclude views)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

information_schema.tables is a SQL-standard catalog view, portable across PostgreSQL/MySQL/SQL Server with stable fields — ideal for baking into an audit script.

Step 2: grep the codebase to confirm runtime references​

For each candidate table, search for references in the codebase, excluding migration files themselves:

# Search runtime code references, excluding the migrations directory
grep -rn "ad_product_monthly_stats" src/ --include="*.py" \
  | grep -v "migrations/"
# 0 lines of output → no runtime reference, it's a candidate

Zero references is the key evidence for an orphan. Make sure to exclude the migration directory — the CREATE in the baseline doesn't count as a "reference".

Step 3: confirm it's empty​

SELECT count(*) FROM your_schema.ad_product_monthly_stats;
-- 0 → confirmed no data, safe to clean up

Be extra careful with tables that have data: first confirm they're truly abandoned (not just recently unwritten), and back up logically if in doubt.

Step 4: write a DROP migration (not a manual delete)​

-- db-migrations/{project}/027_drop_ad_product_monthly_stats.sql
DROP TABLE IF EXISTS your_schema.ad_product_monthly_stats;

Always go through a migration file: it's version-controlled, replayed consistently across environments (dev/staging/prod), and leaves an audit trail. A one-off psql delete only works on the current machine — on another box, the table grows back.

Step 5: verify the drop​

SELECT to_regclass('your_schema.ad_product_monthly_stats');
-- Returns NULL → the table no longer exists

to_regclass() is the standard way to check whether a relation exists; NULL confirms the drop succeeded.

Batch audit: sweep same-prefix siblings at once​

After dropping one table, list all same-prefix siblings and walk through each — avoid "dropped one, missed its siblings":

-- List all tables under a prefix, run steps 2-5 on each
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_name LIKE 'ad_%'
ORDER BY table_name;

FAQ​

How do I list all tables in a PostgreSQL database?​

Query information_schema.tables, filtering by table_schema and table_type = 'BASE TABLE' to list all base tables in a schema. It's more scriptable than psql's \dt, and because it's the SQL standard, the same query is portable across databases.

How do I find unused or orphan tables in PostgreSQL?​

List all tables with information_schema.tables, then compare against references in your codebase or query logs. Tables with zero runtime references and no writes are orphan candidates; confirm the row count with SELECT count(*), and once you've verified no data and no foreign-key dependencies, write a DROP migration to clean them up.

What's the difference between information_schema and pg_catalog?​

information_schema is the SQL-standard catalog view — portable across PostgreSQL/MySQL/SQL Server with stable fields, ideal for portable audit scripts. pg_catalog is the PostgreSQL-specific catalog, richer and more detailed (e.g. precise row-count estimates, storage details) but subject to change between versions. For portable schema audits, prefer information_schema.

Right after tightening a table's unique key — dropping a column that no longer discriminated between rows — every previously working UPSERT immediately failed in bulk with there is no unique or exclusion constraint matching the ON CONFLICT specification.

Encountered this while building AI Analytics — an LLM-powered analytics pipeline that surfaces market trends, user behavior, and sales data for precise operations.

TL;DR​

PostgreSQL's ON CONFLICT (cols) requires cols to exactly match an existing unique constraint or unique index (same columns, same order — otherwise SQL state 42P10). The moment you ALTER the unique key, every INSERT ... ON CONFLICT that references it must be updated; and once the migration lands, the write side must deploy immediately, because the in-between window keeps erroring.

The symptom​

As soon as the unique-key change went live, the scheduled import job failed across the board, with only this line in the write log:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10

Zero rows written to the business table, while plain SELECTs against the same table worked fine — the failure was isolated to the ON CONFLICT write path.

Root cause​

The column set you pass to ON CONFLICT (cols) is the arbiter. PostgreSQL requires it to exactly match some UNIQUE constraint or unique index on the table:

• the set of columns must be the same;
• the order of columns must be the same;
• for a partial unique index (one with a WHERE), ON CONFLICT must carry the same WHERE.

When nothing matches, PostgreSQL has no index to decide what "conflict" means, and raises 42P10.

The classic trigger is shrinking a unique key. The original key had 3 columns; you realize one of them (say audience) has 4 distinct values whose metric rows are 100% identical — pure redundancy — so you drop it down to 2 columns. That's the right optimization, but the old INSERT still says ON CONFLICT (c1, c2, c3) while only (c1, c2) remains as a unique constraint. The arbiter has no landing spot, and the statement errors out.

old unique key: UNIQUE (store_id, metric_key, audience)
new unique key: UNIQUE (store_id, metric_key)

old INSERT: ON CONFLICT (store_id, metric_key, audience)   ← no match

The fix​

Here is a minimal reproduction — create, trigger, and fix in one go, runnable directly in psql:

-- 1. A table with a 3-column unique key
CREATE TABLE daily_metric (
    store_id   TEXT NOT NULL,
    metric_key TEXT NOT NULL,
    audience   TEXT NOT NULL,
    value      NUMERIC,
    CONSTRAINT daily_metric_unique UNIQUE (store_id, metric_key, audience)
);

-- 2. Old UPSERT: ON CONFLICT includes audience
INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key, audience)
DO UPDATE SET value = EXCLUDED.value;

-- 3. Shrink the unique key: drop audience
ALTER TABLE daily_metric
    DROP CONSTRAINT daily_metric_unique,
    ADD  CONSTRAINT daily_metric_unique_new UNIQUE (store_id, metric_key);

-- 4. Re-run the INSERT from step 2 — it now errors ↓
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

The fix is to shrink the INSERT's ON CONFLICT columns to match the 2-column key. Since audience no longer discriminates, pin it to a literal on the write side so incoming parameters can't synthesize extra rows:

INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key)         -- ← shrunk to match
DO UPDATE SET value = EXCLUDED.value;

The part that actually bites is the deployment order, not the SQL itself:

1. Ship the migration first (DROP old constraint + ADD new constraint);
2. Immediately ship the write-side code (the INSERT's ON CONFLICT becomes 2 columns);
3. Leave no gap between the two — old code against the new schema raises 42P10, and new code against the old schema raises 42P10 just the same (no 2-column unique constraint exists yet).

If you use an ORM like Drizzle, an ON CONFLICT column list baked into a sql template is easy to forget when the schema changes — the cost of schema/write-side drift shows up in another Drizzle + PostgreSQL pitfall too.

Caveats​

FAQ​

Does PostgreSQL ON CONFLICT require a unique constraint?​

Only when you name columns. ON CONFLICT (cols) must match an existing UNIQUE constraint or unique index exactly, or you get 42P10. If you just want "skip on any conflict" without caring which constraint fired, use ON CONFLICT DO NOTHING without columns — it needs no specific index.

Can PostgreSQL ON CONFLICT target multiple unique constraints?​

No. A single INSERT's ON CONFLICT can name only one arbiter constraint (one column set, or one index name). A table may have multiple unique keys, but a single statement picks exactly one for conflict detection. To handle different unique keys differently, either split into multiple writes or query first in the application layer before choosing INSERT vs UPDATE.

How to fix there is no unique or exclusion constraint matching the ON CONFLICT specification?​

That is error code 42P10: the ON CONFLICT column set has no matching unique index on the table. Check in order: a UNIQUE constraint covers those columns, the columns and their order match exactly, and any INSERT was updated after a recent unique-key change. If the arbiter is a partial unique index with a WHERE, add the same WHERE clause to ON CONFLICT.