Tag: constraint
Exclusion constraints in PostgreSQL and a tricky problem
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 […]
Breaking your PostgreSQL database with bad CHECK constraints
© 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 […]
A unique constraint where NULL conflicts with everything
© Laurenz Albe 2022 I have been faced with a request for an unusual unique constraint that puzzled me for a while. Since the solution I came up with is a nice show-case for range data types, I’ll share it with you. Also, it allows me to rant some about NULL, which is a temptation […]
Primary Keys vs. UNIQUE Constraints in PostgreSQL
Most of my readers will know about primary keys and all kinds of table constraints. However, only a few of you may have ever thought about the difference between a primary key and a UNIQUE constraint. Isn’t it all just the same? In both cases, PostgreSQL will create an index that avoids duplicate entries. So […]
Constraints over multiple rows in PostgreSQL
Manage constraints over multiple rows: In PostgreSQL and many other relational databases, constraints are an integral part of the feature set. Many people are aware of primary keys, foreign keys, CHECK-constraints, and table constraints. However, from time to time, the situation is way more complicated. That’s when some more advanced techniques are needed to enforce […]
PostgreSQL: Foreign keys and insertion order in SQL
In SQL, the concept of foreign keys is an important one that can be found in all professional databases used in the industry. The core idea is to prevent your PostgreSQL database from storing inconsistent data by enforcing constraints ensuring the correctness of your tables (at least as far as relations between objects are concerned). […]
PostgreSQL: Useful new data types – CREATE DOMAIN
UPDATED 08.05.2023 – SQL and especially PostgreSQL provide a nice set of general purpose data types you can use to model your data. However, what if you want to store fewer generic data? What if you want to have more advanced server-side check constraints? The way to do that in SQL and in PostgreSQL in […]
Triggers to enforce constraints in PostgreSQL
Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. In such a case it is tempting to use triggers instead. This article describes how to do this and what to watch out for. It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”. […]
PostgreSQL exclusion constraints: Beyond UNIQUE
UPDATED AUGUST 2023: A couple of years ago, PostgreSQL introduced so called “exclusion constraints”, which are defined with the EXCLUDE keyword. This feature is not very well known. However, people can use it to implement highly sophisticated constraints which are very hard to do on the application level. In a way, “exclusion constraints” are “beyond […]