Exclusion constraints are a feature that is not very well known, but can be used to implement highly sophisticated constraints. A few years ago, Hans wrote his blog post about EXCLUDE USING GIST… WITH. Recently we received a note from someone dealing with a very tricky problem concerning exclusion constraints: (many thanks to @necrotikS at […]

© Laurenz Albe 2023 This article is about the notoriously difficult problem of “conditional foreign keys”. In object-oriented programming languages, polymorphism is a natural concept. However, it does not translate well to SQL. As a consequence, many people whose data models are driven by an object-oriented application design end up trying to implement conditional foreign […]

© Laurenz Albe 2023 Unless you use the binary collation, creating a b-tree index to support a LIKE condition in PostgreSQL is not straightforward. This keeps surprising Oracle users, who claim that a regular b-tree index will of course always support LIKE. I decided to explore the differences between Oracle and PostgreSQL when it comes […]

© Laurenz Albe 2023 SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you have to do is surround the query with parentheses, like (SELECT …), and you can use it in arbitrary expressions. This makes SQL a powerful language – and one that can be […]

There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. Two examples are: To be able to generate keys independently of the database To move sets of related records between different databases without having to deal with renumbering everything However, like everything good in life, UUID’s come with their own downsides. […]

One way to organize data in PostgreSQL is to make use of schemas. What is a schema in PostgreSQL? And more importantly: What is the purpose of a schema and how can schemas be used to make your life easier? Let’s dive in and find out. The purpose of a schema Before you figure out […]

© Laurenz Albe 2023 Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don’t understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better! Default […]

© Laurenz Albe 2023 A while ago, I wrote about how difficult it is to get an execution plan for a parameterized query. The method suggested in that article works, but is still somewhat complicated. So I wrote a patch to support an EXPLAIN option GENERIC_PLAN, which provides native support for that. My patch got […]

What is a parallel aggregate? In PostgreSQL, a parallel aggregate refers to a way of processing aggregate functions (such as SUM, AVG, MAX, MIN, etc.) on large amounts of data in a parallel and distributed manner, thereby making the query execution faster. When executing an aggregate query, the database system automatically breaks up the result […]

© Laurenz Albe 2023 I am on a spree! After writing about breaking your database and transaction ID wraparound, here I am writing about damage caused by CHECK constraints! Trust me, I don’t want to destroy PostgreSQL databases. It’s just that this Twitter message was brought to my attention and triggered memories of other reports […]

This blog introduces you to PostgREST and helps you to get started with it quickly. What is PostgREST? First think about this: a modern database application usually doesn’t connect to the database directly and issue arbitrary SQL commands. In general, there is always some kind of abstraction layer in between. This layer could be Hibernate […]

Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA’s alike. Stored procedures basics In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command […]