PL/Java Adoption
PL/Java Adoption
PL/Java has been around since 2005, but it has regularly struggled to gain users. Unfortunately, a lot of these problems are specific to the Java language and hamper its adoption. First, there are a ...
Outer Joins and Where Clauses
Outer Joins and Where Clauses
Postgres supports both traditional join syntax, which uses the where clause to specify joined columns, and ansi join syntax, that uses the word join in the from clause. While both syntaxes can be used...
When to Use Server-Side Logic
When to Use Server-Side Logic
The use of server-side logic, particularly stored procedures, has been a highly contentious topic among database professionals for decades. The question has always been what amount of logic should be ...
Allowing Only One NULL
Allowing Only One NULL
While the SQL standard allows multiple NULLS in a unique column, and that is how Postgres behaves, some database systems (e.g. MS_SQL) allow only a single NULL in such cases. Users migrating from othe...
Postgres Keywords
Postgres Keywords
You might be aware that the SQL standard reserves certain identifiers that cannot be used for naming user objects. Postgres follows that standard, with slight modifications. For example, you cannot cr...
Wait Event Reporting
Wait Event Reporting
Postgres often lacks the sophisticated reporting of more established enterprise relational database systems. Sometimes that is because Postgres doesn't require as much monitoring and tuning, but there...
Statistics on Expression Indexes
Statistics on Expression Indexes
Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where clauses. However, there is another benefit to exp...
Going Deep on Stats
Going Deep on Stats
Postgres includes lots of monitoring tools that allow you to see what is happening, but there are a few settings that really go into detail, e.g. log_statement_stats: SET client_min_messages = log; S...
Creating Extensions in Schemas
Creating Extensions in Schemas
Postgres extensions are a powerful way to add functionality to Postgres and dozens of extensions are available. However, it can be difficult to manage them. By default, extensions are loaded into the ...