Fizzbuzz with modern SQL

A popular (technical) interview question for programming jobs is the Fizz Buzz problem.

In a nutshell, the problem is to write a program that outputs the numbers from 1 to n and prints “Fizz” for numbers that can be divided by 3, “Buzz” for numbers that can be divided by 5 and “Fizz Buzz” for numbers that can be divided by 15.

Converting query results to JSON

In my post about dynamic SQL I showed how PostgreSQL’s query_to_xml() could be used to run dynamic SQL and process the result in a regular SQL query.

If you prefer to deal with JSON, rather than XML, it’s quite easy to write a corresponding query_to_jsonb() function.

Dynamic SQL without stored procedures

Sometimes it’s necessary to run dynamic SQL, but it’s not always possible (or sensible) to write a stored procedure just for that.

Postgres and Oracle both support functions that can run queries using dynamic SQL and return the result of those queries as XML.

PIVOT Alternatives

One type of problems is inherently complicated with SQL: turning rows into columns. This is very often referred to as a “crosstab” or “pivot”.

GROUP BY vs DISTINCT

Every now and then I see queries that use GROUP BY without any aggregates which is the same as using DISTINCT instead.

Unpivot with Postgres

Sometimes it’s necessary to normalize de-normalized tables - the opposite of a “crosstab” or “pivot” operation. Postgres does not support an UNPIVOT operator like Oracle or SQL Server, but simulating it, is very simple.

Performance of stored functions

Something that users coming from other database systems easily overlook is, that Postgres supports different languages for writing stored functions (and procedures). Choosing the right language can influence the performance of your functions.

Migrate your mindset too

With the increasing popularity of Postgres, I see more and more questions requiring help to migrate code from other database systems such as Oracle or SQL Server to Postgres.

What I very often see is, that the migration tries to simply translate the syntax from one system to another. This isn’t limited to migrations to Postgres. But very often migrations from Oracle to SQL Server or from SQL Server to Oracle, fall into the same trap.