Posts

Dynamic unpivot with Postgres

In my previous post about unpivot in Postgres I showed how this can be done in a compact manner without using a series of UNION statements.

But Postgres offers an even more compact and dynamic way to do this.

JSON Path support in Postgres 12

The SQL:2016 standard introduced JSON and various ways to query JSON values. Postgres has been supporting JSON for a long time but defined its own set of operators and functions to query JSON values.

With PostgreSQL 12, the standard JSON path query functionality is now also supported by Postgres. Although the functions accepting JSON path queries do not conform to the SQL standard, the parameters and behavior does.

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.

Epoch Mania

Recently I see an increase in questions in various forums (including Stackoverflow) where people are using (big) integer values instead of proper timestamp values to represent timestamps values (less so for DATE values though).

All modern databases systems provide highly efficient data types to store real timestamp values, but I often get questions asking what the actual downside of using a “UNIX epoch” instead of a proper timestamp is.

On the choice of datatypes

I have been wondering for a while, why there are so many databases where all well known best practises for good data modelling are thrown overboard and the wrong data types are used.

Assignment performance in PL/pgSQL

In my post about choosing the language for stored functions I showed what impact the different languages have.

When choosing PL/pgSQL as the language, there is one performance optimization that is often overlooked: assignment of values to variables. Different approaches have a different performance.

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.