Sql

There is an astonishingly large number of (SQL) developers who believe that DISTINCT is a function that needs parentheses and applies to a single column. To make this clear DISTINCT is not a function. The DISTINCT keyword always applies to all columns in the SELECT list. Enclosing one of the columns in parentheses won’t change anything in the outcome. The following statements are 100% identical in what they achieve:

I have blogged about the disadvantges of using integers instead of proper date or timestamp values.

Another (frequently ignored) advantage of using date or timestamp values, is the fact that it’s easier to do date arithmetics with them - especially when combined with the interval data type.

One of the little things that make working with Postgres easier than with other database products, are its error messages. They usually include not only the actual error but also a “Hint” that more often than not actually helps to fix the problem.

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.

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.

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.

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.

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

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

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.