What you should know about constraints in PostgreSQL

At PGConf.EU this year, I presented my new talk "What you should know about constraints in PostgreSQL (and what's new in 18)". In this blog post, I will go deeper into some of the topics I've covered there. If you'd like to jump straight to the slides, here is the link.

On stage at PGConf.EU in Riga

What is a constraint?

When you define a table (or a column), you can attach rules that check or enforce something about the data. These rules are called constraints.

Data types also restrict the kind of data a column can hold but they’re often too general. For example, you can’t make a numeric type accept only positive values. To handle more specific rules (like ensuring unique product numbers or positive prices), SQL lets you define constraints on columns and tables.

Constraints give you fine-grained control over data integrity and if any inserted or default value violates them, PostgreSQL raises an error.

In short, constraints are rules enforced by the database to keep your data valid and consistent. When constraints are not enforced, data issues start to leak in and eventually turn into bugs. Spending time understanding constraints helps prevent subtle data bugs later on.

pg_constraint catalog

There is another way to look at constraints. Internally, constraints are all represented as rows in the pg_constraint catalog.

As the documentation for pg_constraint states, all constraints are listed in this catalog. If you checked the same page in versions prior to Postgres 18, you would notice a warning explaining that not-null constraints on relations are not represented here but instead in pg_attribute, as they only started getting their own entries in this catalog with Postgres 18! We'll talk more about this later.

So, each constraint is recorded in this table with a corresponding contype . We will also talk more about them later on, especially the unusual one represented with a t 🙂

Column constraints vs table constraints

Let's keep reading pg_constraint documentation:

In PostgreSQL’s internal catalog, there is no real distinction between column constraints and table constraints. For the SQL user, there are two syntaxes as seen below:

A column constraint is written right after a column’s definition (the first query). It applies to that one column only.

A table constraint is written after all the columns are listed (the second query). It can apply to one or more columns.

To Postgres internally, there's one representation, always a row in pg_constraint. That's why we can do ALTER TABLE.. DROP CONSTRAINT.. regardless of how it was written. There is no column constraint flag inside the catalog. A column constraint is just a table constraint that happens to involve one column.

Let's write a query to check the constraints on our two example tables (products_oct and products_nov) to see whether there are any differences between the constraints we just created:

The (expanded) output of the query looks like this. As you can see, there is pretty much no difference between the column constraint and the table constraint aside from the tables they're attached to and the names Postgres generated for them.

Constraint trigger

Again, let's keep reading pg_constraint documentation:

Remember the contype table from earlier, where standard constraints like UNIQUE is represented with u, CHECK constraints with c, etc. Then there is also a t for constraint trigger?!

I don't think this is very well known. We're familiar with triggers and constraints in Postgres, but there's also something called a constraint trigger. What is that exactly?

Deferrable triggers

Constraint triggers are created using the CREATE CONSTRAINT TRIGGER command. It's almost like creating a regular trigger (CREATE TRIGGER) but when you specify the CONSTRAINT option, you end up with a constraint trigger instead. The main difference - and the biggest benefit in my opinion - is that we can now adjust the timing of the trigger firing using SET CONSTRAINTS.

Normal triggers fire immediately after or before the event and you can't defer them. Constraint triggers, on the other hand, integrate with the constraint system and can be declared DEFERRABLE or INITIALLY DEFERRED just like constraints. This means you can delay their execution until COMMIT and control them at runtime with SET CONSTRAINTS.

AFTER triggers

When creating a trigger, you have to specify whether the function is called BEFORE, AFTER or INSTEAD OF the event. A constraint trigger can only be specified as AFTER.

Constraint triggers are not meant to change the data flow, only to check conditions after the fact. The idea behind constraints is data validation while triggers are usually about data modification. Constraint triggers belong to the validation category and are expected to raise an exception when the constraints they implement are violated.

FOR EACH ROW triggers

When creating a trigger, you can specify whether the trigger function should be fired once every row affected by the trigger event using FOR EACH ROW or just once per SQL statement using FOR EACH STATEMENT.

Constraint triggers can only be specified as FOR EACH ROW. That's because constraint enforcement depends on the individual row values.

Why constraint triggers exist

In his blog post "Triggers to enforce constraints in PostgreSQL", Laurenz Albe says:

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. 

He also gives a good example of how this could be used and shows how MVCC behavior differs between constraints and triggers. I'd recommend giving it a read.

In practice though, most users will never create a constraint trigger by hand. Postgres primarily uses them internally as a building block for constraint enforcement. Foreign keys in particular are implemented using automatically generated constraint triggers. This design is what makes DEFERRABLE and INITIALLY DEFERRED foreign keys possible.

Domain

One last look at the pg_constraint documentation:

So, now we see a mention of domains 😊 I think this is another not-very-well-known concept.

Let's have a quick example to see how domains work:

The example defines a new type called email_address. Now every column of that type automatically checks the regex when inserting or updating values. If you try to insert anything other than a valid email, it fails even though the table itself has no explicit CHECK constraint.

Generally, constraints belong to tables, but Postgres also lets you attach constraints on domains also as mentioned in the pg_constraint documentation above. I wanted to show how we can query those constraints that are attached to domains in the query below:

So, the key difference between table constraints and constraints on domains is that table constraints use conrelid to point to a table while domain constraints use contypid to point to a type (the domain).

And the output of our query looks like this, listing the constraint name, definition and domain name:

Conclusion

In this blog, we covered what constraints mean in Postgres by walking through the pg_constraint catalog documentation and demonstrating concepts such as table constraints vs. column constraints, constraint triggers and domains using simple SQL queries.

Next, we will continue with the newly introduced temporal keys in Postgres 18.

Bonus: If you'd like to try Postgres 18 on Xata, you can get access today.