A view is the most elegant way to encapsulate a recurring query — right up to the moment the query needs an argument from outside. A view has no parameters. As soon as a value belongs in the WHERE that the caller supplies, the table-valued function is the tool of choice: a function that returns an entire result set … 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
A PL/pgSQL function without a volatility marker is VOLATILE by default. That sounds harmless but has a real cost: the planner calls the function again for every row, never pre-computes it once, and excludes it from every functional index. The damage is invisible — until the same query suddenly takes seconds instead of milliseconds. Good PL/pgSQL function conventions start … Read more
Creating a table takes thirty seconds – being annoyed about its name, its key, and its column types then takes two years. Postgres table conventions take that annoyance off the table up front: consistent names, a predictable primary key, data types without surprises, and audit columns that explain every row. Without them, a schema drifts further apart … Read more
An AI writes you a stored procedure in seconds. It compiles, it runs – and it looks like a stranger wrote it. The next artifact looks different again. Generated SQL is technically correct but stylistically arbitrary – and without an enforced convention, a generated collection drifts apart just like one five developers write by hand. The obvious … Read more
A stored procedure, a migration script, a complex report — Claude Code writes them in seconds. That’s the easy part. The hard part starts afterwards: generated SQL that belongs to no one drifts apart just like hand-written code — only faster, because the AI produces hundreds of lines on demand. AI-assisted SQL development only pays off when the … 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
