A SQL Server to PostgreSQL schema migration looks finished the moment the CREATE TABLE script runs without an error. That is exactly when the real trouble begins: the table is there, the data is loaded — and the first INSERT that should hand out a new ID collides with an existing key. The reason is not a typo but a change … Read more
A migration from SQL Server to PostgreSQL rarely fails at actually copying the data. It fails at datetime, where the choice between timestamp and timestamptz is anything but cosmetic, at bit, which is not a boolean, and at money, which you’d be better off not touching in PostgreSQL at all. The SQL Server to PostgreSQL data type mapping decides whether the data arrives cleanly — … Read more
Bad data gives no warning. An age of 200 years, a duplicate customer number, a country code that doesn’t exist — in the source system nobody notices. Only when the ETL run tries to push the rows into the strictly modelled target layer does the load break: on a CHECK, on a UNIQUE index, on a foreign key. Checking … Read more
“We do ETL.” You hear it in almost every data team — and it is often wrong. If every transformation runs as a stored procedure after the raw data has already been loaded, unchanged, into a staging table, then what you have is really ELT. The label hides what is actually happening. The difference between ETL vs. ELT is … Read more
If you write a stored procedure, you write it for someone who doesn’t know it — usually for yourself, 18 months later, at 11 p.m., while an ETL run is stuck. Readability isn’t cosmetics, it’s debugging time. PostgreSQL forces almost nothing on you: names are free, indentation doesn’t matter, a RAISE EXCEPTION swallows any string you assemble inline. That’s exactly … Read more
A single date string that cannot be parsed, and the entire ETL run aborts. The design pattern for ETL process architecture presented here prevents exactly that: bad data is isolated, not passed along. TL;DR — what this article covers: Prerequisite. Basic familiarity with ETL processes. This is a conceptual article — not a step-by-step tutorial. Root of … Read more
If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: data quality starts with type conversion. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT handles the documented formats, anything else needs a function of your own. What you’ll take away: Prerequisites: SQL Server 2017+ (for TRY_CONVERT styles 23/126), PostgreSQL 12+ for the … Read more
Data quality starts with type conversion — and for bit columns it shows up right at the input value: yes/no information comes out of legacy sources in a wide variety of notations (‘J’, ‘Y’, ‘ON’, ‘1’, ‘x’, ‘-‘, …). SQL Server’s built-in TRY_CONVERT(bit, …) only covers the integer standard plus ‘true’/’false’ — everything else needs a dedicated conversion function. This article describes both: what’s built in, when … Read more
Data quality with floating-point columns is a discipline of its own — and TRY_CONVERT(float, …) has a few quirks that tend to slip past the import path: an empty string becomes 0 (not NULL), a comma as decimal separator yields NULL, and even after a clean conversion 2 + 3.4 – 3.4 – 2 does not return exactly 0 as a float. This article sorts out the … Read more
Anyone who has imported a point-of-sale report with values like ‘1.234,56 €’ from a CSV into a SQL Server database knows the pattern: TRY_CONVERT(money, ‘1,234.56’) yields 1234.5600. Yet TRY_CONVERT(money, ‘1.234,56’) yields NULL. And even when the import runs cleanly: money / 100 * 100 is not necessarily the same as the input value. At a glance — what this article covers: Prerequisite: SQL Server 2017+ (TRIM is used … Read more
