Query Rewriting and Validating
Explore techniques for rewriting SQL queries to maintain the same results with different structures. Understand how to implement and run unit tests using pgTap in PostgreSQL, ensuring your SQL code is reliable and maintainable within an interactive SQL REPL setup.
We'll cover the following...
Unit testing
SQL is code, so it needs to be tested. The general approach to unit testing applies beautifully to SQL: given a known input, a query should always return the same desired output. That allows us to change our query spelling at will and still check that the alternative still passes our tests.
Examples of query rewriting would include in-lining common table expressions as subqueries, expanding or branching in a where clause as UNION ALL branches, or maybe using a window function rather than complex juggling with subqueries to obtain the same result. What we mean here is that there are a lot of ways to rewrite a query ...