A SQL unpivot of spreadsheet-loaded month columns fails on some engines and gives wrong totals on others because some columns are text and some numeric; what is happening, and how do you fix it?
answer
- twelve columns become one column
- one output column, one type
- engines reconcile types differently
- stop relying on the implicit rule
- clean, then cast every column
basics
~20 sAn unpivot stacks many columns into one, which needs one type, and engines reconcile mismatches differently: some reject, some fail at run time, some convert silently. Fix it by cleaning and casting every listed column to one chosen type.
solid answer
~50 sAn unpivot pours twelve columns into one output column, and a column has one type, so the engine must reconcile them position by position. In PostgreSQL, `VALUES` rows follow the same rules as `UNION`. The outcome depends on the engine. PostgreSQL rejects integer with text before running, and Oracle rejects mixed type groups too. SQL Server applies type precedence, so text converts to integer, and it fails at run time only on reaching a value like `n/a`. MySQL makes the output a string, and a later `SUM` in a `SELECT` converts each value with only a warning, so `'1,200'` counts as 1. That is the wrong total. The fix is to stop relying on the implicit rule: choose a target type, clean each column, turn placeholders into NULL with `NULLIF`, and `CAST` explicitly. Then an unexpected value such as `TBC` fails loudly instead of skewing the numbers.
code
sql · 13 linesWITH sales_wide (product_id, jan_sales, feb_sales) AS (
VALUES (1, 10, 'n/a'),
(2, 20, '1,200')
)
SELECT w.product_id, m.month_no,
CAST(NULLIF(REPLACE(m.raw_amount, ',', ''), 'n/a')
AS numeric(12, 2)) AS amount
FROM sales_wide AS w
CROSS JOIN LATERAL (
VALUES (1, CAST(w.jan_sales AS text)),
(2, CAST(w.feb_sales AS text))
) AS m (month_no, raw_amount)
ORDER BY w.product_id, m.month_no;go deeper
Know that all unpivoted columns end up in one output column, so they need compatible types, and that CAST is how you make them match.
Explain that the engine reconciles types by position, that VALUES and UNION share that work, and that a mismatch can be an error or a silent conversion depending on the engine.
Diagnose from the symptom: an early error, a late conversion failure or a quietly wrong total each point to a different engine rule. Replace implicit rules with a clean-then-cast step that fails loudly.
Decide how bad cells are handled across feeds: stop the load, or route rows to a rejects table with an owner. Lenient coercion is the option that eventually costs trust in the numbers.
## Why types collide in an unpivot In a wide table, each column has its own type. A spreadsheet loader usually infers that type from the cells, so a clean month becomes numeric, while a month where someone typed `n/a` or `1,200` becomes text. An unpivot stacks those columns into one **output column**, and one output column has one type. The engine has to pick it, and both portable forms hand this to the same machinery: PostgreSQL's documentation says a `VALUES` list resolves column types with the same rules as `UNION`. Moving between the two forms therefore does not make the problem go away. ## What each engine does The same mix of an integer column and a text column gives four different outcomes. | Engine | Integer with text | What you see | |---|---|---| | PostgreSQL | Rejected at analysis | An immediate error naming both types | | Oracle | Rejected at analysis | An immediate datatype mismatch error | | SQL Server | Text converts to integer | Runtime failure on the first bad value | | MySQL | Output becomes a string | No error, later arithmetic may be wrong | Each of them produces its own kind of incident: - **An early rejection** is the cheapest. You find out when you write the query, or when a reload changes a column's inferred type. - **A run-time failure** can pass every test and then fail in production, when the first bad value arrives in a later file. A filter or a change of plan can move when it appears. - **A silent string** is the worst. In MySQL, `SUM` over strings converts each value numerically and, in a plain `SELECT`, only warns about the bad ones. The leading digits of `'1,200'` count as 1, and `'n/a'` counts as 0, so the total is simply wrong and looks fine. SQLite types each value rather than each column, so it does not reject the stack at all, and the mix flows on to whatever reads it. SQL Server's `UNPIVOT` operator is stricter than its `UNION ALL`: the listed columns must share one exact type, or the query is rejected. So moving to the operator can surface a problem the hand-written form was hiding. ## The numeric-only version of the same problem Text is the visible case, but an all-numeric mix can still lose something. In PostgreSQL, stacking `numeric` with `double precision` gives `double precision`, so exact decimal amounts become floating-point in the output. Nothing errors, and the difference may only appear when a reconciliation is off by a cent. Casting to one explicit type is the fix here too. ## The fix The work has a set order, and each step removes one implicit decision. 1. **Choose the target type on purpose**, such as `numeric(12, 2)` for money, and write it down. 2. **Bring every listed column to a common type** so the stack is legal. Text is the easy common type when some columns are dirty. 3. **Clean in one place**, such as removing thousands separators with `REPLACE`, which is widely available. 4. **Map known placeholders to NULL** with `NULLIF(raw, 'n/a')`, so they follow your rule for missing months. 5. **Cast to the target type**, so anything unforeseen raises an error instead of turning into a number. ```sql SELECT w.product_id, m.month_no, CAST(NULLIF(REPLACE(m.raw_amount, ',', ''), 'n/a') AS numeric(12, 2)) AS amount FROM sales_wide AS w CROSS JOIN LATERAL ( VALUES (1, CAST(w.jan_sales AS text)), (2, CAST(w.feb_sales AS text)), (3, CAST(w.mar_sales AS text)) ) AS m (month_no, raw_amount); ``` Step 5 is deliberately loud. A value like `TBC` stops the load with an error, which is better than a total that looks plausible and is wrong. If a load has to keep going, route bad rows to a rejects table so they are counted and visible, instead of quietly coercing them. Some engines offer a safe-cast function that returns NULL on failure, and it is worth knowing that it hides the same bad values unless you count them. ## How to catch it early - **Fix column types at load**, instead of letting the loader infer them per file, so one stray cell cannot change a column's type. - **Reconcile after the cast**, by counting NULL amounts that were not NULL in the source. - **Test with bad values on purpose**, such as `n/a`, a thousands separator and an empty string, since clean sample files prove nothing here. - **Run on the engine you deploy to**, because the table above means a query that errors on one engine can run quietly on another. An empty string needs its own decision. It is not NULL in most engines, and Oracle treats it as NULL, so a cleaning step should say what it wants rather than assume.
- The same unpivot passes on a clean test file on SQL Server and fails in production a month later. How do you explain that?SQL Server converts the text column to the higher-precedence integer type, and the conversion only fails on a row that actually holds a non-numeric value. Clean test files never contain one, so the statement looks correct until a real `n/a` arrives. I would add a dirty fixture to the tests, and replace the implicit conversion with an explicit clean and cast.
- Why not cast straight to numeric in each branch, rather than going through text?You can, and if every column is already numeric you should. Going through text only helps when some columns are dirty, since you can then write the cleaning once, around one column, and not twelve times. The cost is a redundant conversion of the clean columns, which is usually minor in a load step.
- Moving from UNION ALL to LATERAL (VALUES ...) avoids type mismatches between columns.
- Every engine reports an integer and text mix as an error up front.
- Each output row of an unpivot keeps its source column's own type on every engine.
- If an unpivot ran without errors, its totals must be right.
- Casting bad values away so the load never fails is always the better choice.