6 posts tagged with "PostgreSQL"

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.

After deleting a DAG's .py file in Airflow to retire it, the dag_id still hangs around in the Web UI list and the database; even stranger — if you clear the metadata first and delete the file second, the just-cleared rows "come back to life."

Encountered this while building AI Ops — an LLM-powered analytics pipeline where retiring an old report DAG required cleaning its metadata too, otherwise the UI list and scheduled scans stayed polluted by residual rows.

TL;DR​

Airflow's dag-processor periodically scans the DAG folder and re-registers DAGs, and airflow dags reserialize doesn't purge "file-already-deleted" orphan rows — so just deleting the .py file won't make the dag_id vanish from the UI or DB. Conversely, clearing metadata before deleting the file lets the processor re-register the cleared rows on its next scan ("revival"). Correct order: ①delete the file first so the processor stops registering → ②SQL DELETE in foreign-key order → ③run airflow dags reserialize to verify.

Symptoms​

Retiring the shop_report_aggregation DAG — after deleting its .py file:

$ ls /opt/airflow/project/airflow_dags/shop_report_aggregation.py
ls: cannot access '.../shop_report_aggregation.py': No such file or directory

$ # but it's still in the database
$ docker exec cclhub-db psql -U airflow -d airflow -c \
    "SELECT dag_id, is_paused, is_active FROM dag WHERE dag_id='shop_report_aggregation';"
       dag_id            | is_paused | is_active
--------------------------+-----------+-----------
 shop_report_aggregation | f         | t          ← still there

It's not just the dag table — the matching rows in serialized_dag, dag_code, and dag_version are all still there, so the Web UI keeps showing this "deleted" DAG.

Worse is the reverse order — clear metadata first, delete file second:

T0  DELETE FROM dag WHERE dag_id='shop_report_aggregation';   ← cleared
T1  (.py file not deleted yet)
T2  dag-processor scan fires; file exists, dag table has no row → re-registers
T3  SELECT ... FROM dag WHERE dag_id='shop_report_aggregation';   ← it's back (revival)

Root Cause​

Two mechanisms stack up:

1. dag-processor scans and re-registers periodically. Airflow's dag-processor (part of the Scheduler) scans dags_folder on processor_poll_interval (default ~5 min), parses each .py file, and upserts into the metadata tables (dag, serialized_dag, dag_version). As long as the file exists, the next scan rewrites those rows. That's the direct source of "revival" — you clear the row, the file is still there, and the processor re-registers it as a new DAG.

2. reserialize ignores "file-gone" orphan rows. airflow dags reserialize re-serializes existing DAG files and refreshes serialized_dag; it does not delete orphan dag rows whose files have vanished. And airflow dags cleanup only purges expired dag_run history by default — it also leaves the dag / serialized_dag / dag_code / dag_version metadata tables alone. So after you delete the file, the metadata rows become orphans nobody cleans.

┌─ dag-processor ──────────────────────────────┐
│  scans dags_folder                            │
│  ├─ file present → upsert dag / serialized... │  ← source of revival
│  └─ file absent  → skip, no row deletion      │  ← orphan residue
└───────────────────────────────────────────────┘

Conclusion: to actually remove the metadata, you must give the processor no file to register (delete the file first), then manually clean the residual rows.

Solution​

Step 1: Delete the file first​

Make the .py file disappear from the DAG folder so dag-processor stops registering it.

# In production this is usually synced to the volume-mounted DAG folder
# /opt/airflow/project/airflow_dags/ via git pull
git pull   # removes shop_report_aggregation.py from the repo and the folder

# or delete directly (after confirming nothing depends on it)
rm /opt/airflow/project/airflow_dags/shop_report_aggregation.py

Step 2: Clean metadata in foreign-key order​

DELETE in foreign-key dependency order to avoid constraint violations. Deleting dag_run CASCADEs to task_instance:

BEGIN;

-- 1. run history (CASCADEs to task_instance)
DELETE FROM dag_run      WHERE dag_id = 'shop_report_aggregation';

-- 2. serialized DAG
DELETE FROM serialized_dag WHERE dag_id = 'shop_report_aggregation';

-- 3. version
DELETE FROM dag_version  WHERE dag_id = 'shop_report_aggregation';

-- 4. dag main table
DELETE FROM dag          WHERE dag_id = 'shop_report_aggregation';

-- 5. dag_code is keyed by source hash; multiple DAGs may share the same code;
--    only delete hashes no longer referenced by any serialized_dag
DELETE FROM dag_code
WHERE dag_hash NOT IN (SELECT dag_hash FROM serialized_dag);

COMMIT;

Step 3: Verify​

airflow dags reserialize

# confirm the dag row is not rebuilt
docker exec cclhub-db psql -U airflow -d airflow -c \
  "SELECT count(*) FROM dag WHERE dag_id='shop_report_aggregation';"
#  count
# -------
#      0   ✅

After reserialize, dag / serialized_dag / dag_code / dag_version are all 0 for that dag_id, and the next processor scan doesn't rebuild them — the cleanup is stable.

As a side note, on the same pipeline, pandas NaN crashing XCom serialization is another pitfall worth bookmarking.

Notes​

FAQ​

Why does a DAG still show in Airflow after deleting its .py file?​

Deleting the file doesn't clean the database. Rows in dag / serialized_dag / dag_code / dag_version still exist, and the Web UI reads those tables to render the list, so the deleted DAG keeps showing. Airflow has no built-in command to purge these orphan rows automatically; you must SQL DELETE them manually in foreign-key order.

How do I completely delete an Airflow DAG and all its metadata?​

Three steps: 1) delete the .py file so dag-processor stops registering it; 2) SQL DELETE in foreign-key order (dag_run → serialized_dag → dag_version → dag → orphan dag_code); 3) run airflow dags reserialize, then query the dag table to confirm the dag_id row count stays at 0 and isn't rebuilt.

What's the correct order to clean Airflow DAG metadata, and why not clear metadata before deleting the file?​

Delete the file first, then clean metadata. If you reverse it, the .py file still exists, so dag-processor re-registers the cleared dag row on its next scan — the metadata "comes back to life." Only by making the file vanish first (so the processor has nothing to register) and then cleaning the residual rows can you fully retire the DAG.

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.

When an Airflow DAG reads a .sql template file as a single string and passes it to PostgresHook.get_pandas_df(), prior SELECT results are silently dropped — the DAG reports "SQL query returned no results", but copying the same SQL into psql returns data normally.

Encountered this while building AI Analytics — an LLM-powered analytics pipeline where an Airflow DAG reads multi-query report templates from .sql files and executes them.

TL;DR​

PostgresHook.get_pandas_df(sql) internally calls pandas.read_sql(sql, conn) → psycopg2 cursor.execute(sql). When sql is a single string with multiple ;-separated SELECTs, the DBAPI only exposes the cursor of the last result set — prior query results are silently dropped with no error. Fix: split by top-level semicolons into a list[str] and call get_pandas_df per statement, or pass the list directly so DbApiHook runs them sequentially.

Symptom​

The DAG task executing shop_monthly_overview.sql reports "SQL query returned no results":

sql_count = 1   ← template clearly contains 4 queries
result = "❌ SQL query returned no results"

But the same SQL pasted into psql against the same database with the same parameters returns data for all 4 SELECTs.

Reproduction​

Verify get_pandas_df behavior with multi-statement SQL directly inside the Airflow container:

from airflow.providers.postgres.hooks.postgres import PostgresHook

hook = PostgresHook(postgres_conn_id="postgres_default")

# Three SELECTs concatenated into one string
sql = "SELECT 1 AS a; SELECT 2 AS b; SELECT 99 AS c WHERE 1=0;"

df = hook.get_pandas_df(sql)
print(df.columns.tolist())  # ['c']  ← only got the last statement's columns
print(df)                    # Empty  ← the last statement itself returns 0 rows

Expected three result sets, got only the last one (SELECT 99 ... WHERE 1=0, 0 rows). The first two completely disappear with no error or warning.

Root Cause​

The call chain is PostgresHook.get_pandas_df → DbApiHook.get_pandas_df → pandas.io.sql.read_sql → psycopg2 cursor.execute(sql).

The DBAPI protocol (PEP 249) allows execute to accept a string with multiple ;-separated statements. PostgreSQL executes all of them, but the cursor only exposes the last result set — this is inherent PostgreSQL wire protocol behavior, not an Airflow or pandas bug.

┌────────────────────────────────────────────────────┐
│ SELECT 1;  ← executed, result set 1 dropped at once │
│ SELECT 2;  ← executed, result set 2 dropped at once │
│ SELECT 99 WHERE 1=0;  ← executed, result set 3 exposed │
└────────────────────────────────────────────────────┘
                       ↓
            pandas.read_sql only fetches result set 3

The root cause in our code is task_execute_sql reading the entire .sql file as a single string and passing it to get_pandas_df:

# ❌ Problematic code
sql_text = open(sql_path).read()  # full text with 4 SELECTs
df = pg_hook.get_pandas_df(sql_text)  # only gets the last result

Why does psql return data? Because the psql frontend actively iterates through all result sets and prints them one by one, while a DBAPI cursor does not.

Solution​

Option A (Recommended): Split by top-level semicolon, execute one by one​

Suitable for .sql template files — they contain comments, quotes, and multiple queries that need robust splitting.

def split_sql_statements(sql: str) -> list:
    """
    Split SQL by top-level semicolons, correctly handling:
    - Semicolons inside single-quoted strings ('a;b' is not split)
    - SQL-standard '' escape ('it''s' is not split)
    - Semicolons inside -- line comments (-- note; not split is not split)
    """
    statements = []
    buf = []
    i, n = 0, len(sql)
    in_quote = False

    while i < n:
        ch = sql[i]

        # Inside a single-quoted string
        if in_quote:
            buf.append(ch)
            if ch == "'":
                # '' = literal single quote, does not end the string
                if i + 1 < n and sql[i + 1] == "'":
                    buf.append(sql[i + 1])
                    i += 2
                    continue
                in_quote = False
            i += 1
            continue

        # Top level
        if ch == "'":
            in_quote = True
            buf.append(ch)
        elif ch == '-' and i + 1 < n and sql[i + 1] == '-':
            # Line comment, swallow to end of line (; inside is not a split point)
            while i < n and sql[i] != '\n':
                buf.append(sql[i])
                i += 1
            continue
        elif ch == ';':
            stmt = ''.join(buf).strip()
            if stmt:
                statements.append(stmt)
            buf = []
            i += 1
            continue
        else:
            buf.append(ch)
        i += 1

    # Trailing block without a final semicolon
    stmt = ''.join(buf).strip()
    if stmt:
        statements.append(stmt)

    return statements


# Caller
sql_text = open(sql_path).read()
statements = split_sql_statements(sql_text)

# Execute one by one, collect all results
all_results = []
for idx, stmt in enumerate(statements, start=1):
    df = pg_hook.get_pandas_df(stmt)
    if not df.empty:
        all_results.append({
            "sql_index": idx,
            "sql": stmt,
            "data": df.to_dict("records"),
            "columns": df.columns.tolist(),
            "row_count": len(df),
        })

Option B: Pass a list directly to DbApiHook​

Airflow's DbApiHook.run and get_records accept list[str] and execute sequentially — but get_pandas_df return behavior in list mode is inconsistent across providers. For production, Option A gives you full control.

Why not sqlparse.split?​

Community answers often recommend sqlparse.split(sqlparse.format(sql, strip_comments=True)), but strip_comments=True discards comments. If your downstream processor depends on metadata in comments (e.g. -- dimension: shop), you lose context. A hand-rolled splitter preserves the original comment text and gives you control.

Caveats​

FAQ​

How do I execute multiple SQL statements in Airflow PostgresHook?​

Pass list[str] instead of a single string. DbApiHook.get_pandas_df and run accept sql as a list and execute sequentially; a single string with semicolon-separated statements causes psycopg2 to return only the last result set. For production, split yourself and call per-statement so you control result aggregation and sql_index.

Why does get_pandas_df only return the last result for multi-statement SQL?​

pandas.io.sql.read_sql calls psycopg2 cursor.execute with the full string; the DBAPI protocol only exposes the cursor of the last result set for multi-statement execution, and prior SELECT results are dropped by the server immediately, with no error or warning. psql returns data because its frontend actively iterates all result sets, while a DBAPI cursor does not.

How do I split SQL by semicolon safely with comments and quotes?​

Scan character by character and split only at top-level semicolons outside single-quoted strings and -- line comments. Single-quote literals use the SQL-standard '' escape; do not use str.split(';'), it will mis-cut semicolons inside comments and strings. If you use sqlparse.split, note that strip_comments=True discards the original comment text.

Encountered this issue while building an e-commerce analytics platform for a client. Here's the root cause and solution.

TL;DR​

In Drizzle ORM's sql template tag, sql.join(values.map(v => sql(v))) parameterizes all values. If the values array contains SQL expressions (like date_trunc('week', '2026-05-17'::date)::date), PostgreSQL treats them as plain strings and throws invalid input syntax for type date. SQL expressions must use sql.raw() or be written separately in the template.

The Problem​

Data collection pipeline: Chrome extension → CCLHub server → Analytics API → PostgreSQL. Symptoms:

1. CCLHub logs show correct collected data (uv: 403, payAmt: 19478.47)
2. Analytics API returns 200 success
3. But database query shows all zeros: uv: 0, pay_amt: 0.00

-- Actual database data
 report_date | uv  | pay_amt  | reveal_cnt
-------------+-----+----------+------------
 2026-05-12  | 392 |  7333.67 |      11879  -- old data fine
 2026-05-13  |   0 |     0.00 |          0  -- new data all zeros!

Analytics error log reveals:

PostgresError: invalid input syntax for type date:
  "date_trunc('week', '2026-05-17'::date)::date"

Root Cause​

The original code mixed parameterized values with SQL expressions:

// ❌ Problem code
const insertVals: (string | number | null)[] = [
  String(shop_id),
  String(platform_id),
  reportDate,
  tenant_id,
  `date_trunc('week', '${reportDate}'::date)::date`, // ← SQL expression
];

// sql.join parameterizes ALL values, including the date_trunc expression
await db.execute(sql`
  INSERT INTO table (..., week_start_date)
  VALUES (${sql.join(insertVals.map(v => sql`${v}`), sql`,`)})
  ...
`);

Generated SQL:

-- PostgreSQL receives $5 as a literal string value
INSERT INTO table (..., week_start_date)
VALUES ($1, $2, $3, $4, $5, ...)
-- $5 = "date_trunc('week', '2026-05-17'::date)::date"  ← treated as string!

PostgreSQL tries to parse "date_trunc('week', '2026-05-17'::date)::date" as a date type → error.

Why zeros instead of an error? Because the same table has a separate inquiry INSERT (PARTIAL UPSERT) that succeeded, creating rows with dashboard columns defaulting to 0. The daily report UPSERT failed but didn't roll back the existing rows.

Solution​

Separate SQL expressions from parameterized values using sql.raw() or direct template embedding:

// ✅ Fix: separate parameterized values from SQL expressions
const insertCols = ['shop_id', 'platform_id', 'report_date', 'tenant_id'];
const insertVals: (string | number | null)[] = [
  String(shop_id), String(platform_id), reportDate, tenant_id,
];

// 19 data columns parameterized normally
for (const [apiKey, dbCol] of Object.entries(DAILY_COLUMNS)) {
  insertCols.push(dbCol);
  insertVals.push(row[apiKey] != null ? String(row[apiKey]) : '0');
}

// week_start_date uses SQL expression, NOT in parameterized array
await db.execute(sql`
  INSERT INTO table (${sql.raw(insertCols.join(', '))}, week_start_date)
  VALUES (
    ${sql.join(insertVals.map(v => sql`${v}`), sql`,`)},
    date_trunc('week', ${reportDate}::date)::date  -- ← directly in template
  )
  ...
`);

Key distinction:

Caveats​

TL;DR​

Two common networking pitfalls with WSL2 + Docker Desktop:

1. Silently occupied port: When a Docker container maps 5432, SSH tunnel localhost:5432 connects to the container's PostgreSQL instead of the remote server — the password is correct, but you're hitting the wrong instance
2. Host mode localhost unreachable: network_mode: host shares the Docker utility VM's network, not WSL2's — curl localhost:8080 fails