2 Bad SQL Usage

Let’s begin our journey into the land of PostgreSQL mistakes with what most relational database users are familiar with: Structured Query Language (SQL). In this chapter, we examine some bad SQL habits that can lead to bad query performance or, even worse, getting wrong query results. Postgres is very particular about the way it wants queries to be written and in most cases follows a strict interpretation of the SQL Standard. This means you need to be careful about the way you construct those queries and always check that you are actually getting exactly the results you want.

2.1 Using NOT IN to exclude

It’s often that we need to specify a condition with a negative expression. That doesn’t mean that we are trying to be unpleasant, just that instead of specifying what we want, we specify the inverse of that condition or what we don’t want in our results, in order to define the query.

Negative predicates are expressed with the NOT keyword and they can be used to invert any SQL expression, including those based around IN.

2.1.1 Performance implications

2.1.2 Alternative

2.2 Selecting ranges with BETWEEN

2.3 Not using CTEs

2.4 Using uppercase identifiers

2.5 Dividing INTEGERs

2.6 COUNTing NULL values

2.7 Querying indexed columns with expressions

2.8 Selecting and fetching all the data

2.9 Not taking advantage of checkers/linters/AI

2.9.1 Code checkers/linters

2.9.2 Large Language Models (LLMs)

2.10 Summary