This tips were inspired, among other sources and personal experience, by the PostgreSQL Wiki.
NOT IN or a combination such as
NOT (x IN (...)) is a bad idea. If
you need something like
NOT IN (SELECT ...) you can rewrite it using
One of the main reasons for this is that if there is NULL in the set of values,
NOT IN will never evaluate to
TRUE. This is because when comparing a value
that is not in the set to
NULL will be generated.
NOT (NULL) is
NULL, so it is not possible for any value to evaluate to
there is a
NULL in the exclusion set.
The other reason is because the query planner, as a consequence of the reason
outlined above, might make queries with
NOT IN (SELECT ...) very slow.
However, it is OK to use
NOT IN with a list of constants.
BETWEEN uses a closed-interval comparison, which always includes both ends.
Even if this sometimes is what you want, using it is still considered a bad
practice by many, as it will often return unexpected results, mainly if dealing
For instance, the two queries below might have a non-empty intersection, as a timestamp of 2019-01-02 00:00:00.000000 will match both queries.
SELECT ... WHERE time BETWEEN '2019-01-01' AND '2019-01-02'; SELECT ... WHERE time BETWEEN '2019-01-02' AND '2019-01-03';
timestamp with time zone instead. Timestamp with time zone records a
point in time in UTC, performing the necessary conversions and ensuring
arithmetic works as expected. However, despite what the name suggests, this
type does not store the entered time zone.
There is no way for the database to know that UTC is the timezone of the stored
values if you are using
timestamp. This complicates otherwise simple time
calculations, because now you must specify the time zones.
This does not truncate values, it rounds them. This can cause unexpected
behavior such rounding
now() to a moment in the future.
Most importantly, try to never use
char(n) as it has some very strange
behavior and even though you might think it is a fixed-width type, it is not as
the actual number of bytes varies since characters may take up more than one
Unless you have a very good reason to limit the length of a text column, do not
varchar, or, better yet,
The currency used for the money data type is the same for the whole database.
Additionally, it’s rounding behavior is likely not what you want. Using a pair
numeric and a currency value is a much better solution.