--- title: "What you should know about constraints in PostgreSQL " description: "In this blog, we explore Postgres constraints through the pg_constraint catalog, covering table vs. column constraints, constraint triggers, domains and more." source: "https://xata.io/blog/constraints-in-postgres" --- # What you should know about constraints in PostgreSQL At [PGConf.EU](https://www.postgresql.eu/events/pgconfeu2025/schedule/session/7133-what-you-should-know-about-constraints-in-postgresql-and-whats-new-in-18/) 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](https://github.com/gulcin/presentations/tree/main/pgconfeu-2025). ![Riga 2025](https://xata.io/api/media/file/pgconfeu2025-198%202.JPG) ## 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](https://www.postgresql.org/docs/current/catalog-pg-constraint.html) catalog. As the [documentation](https://www.postgresql.org/docs/current/catalog-pg-constraint.html) 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` 🙂 ![](https://xata.io/api/media/file/pg_catalog.png) ## 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: ``` CREATE TABLE products_oct ( price numeric CHECK (price > 0) ); CREATE TABLE products_nov ( price numeric, CHECK (price > 0) ); ``` **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: ``` SELECT rel.relname AS table_name, c.conname, c.contype, c.conrelid::regclass AS table_ref, c.conkey, pg_get_constraintdef(c.oid, true) AS constraint_def FROM pg_constraint c JOIN pg_class rel ON rel.oid = c.conrelid WHERE rel.relname IN ('products_oct', 'products_nov'); ``` 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. ``` -[ RECORD 1 ]--+--------------------------- table_name | products_nov conname | products_nov_price_check contype | c table_ref | products_nov conkey | {1} constraint_def | CHECK (price > 0::numeric) -[ RECORD 2 ]--+--------------------------- table_name | products_oct conname | products_oct_price_check contype | c table_ref | products_oct conkey | {1} constraint_def | CHECK (price > 0::numeric) ``` ## 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](https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints/)", 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: ``` CREATE DOMAIN email_address AS text CHECK (VALUE ~* '^[^@]+@[^@]+\.[^@]+$'); CREATE TABLE users ( id serial PRIMARY KEY, email email_address NOT NULL ); -- This will fail INSERT INTO users(email) VALUES ('not-an-email'); -- This will be successful INSERT INTO users(email) VALUES ('ok@example.com'); ``` 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: ``` SELECT c.conname, pg_get_constraintdef(c.oid, true) AS definition, t.typname AS domain_name FROM pg_constraint c JOIN pg_type t ON t.oid = c.contypid WHERE c.contype = 'c' AND c.contypid <> 0; ``` 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: ``` conname | definition | domain_name -------------------+---------------------------------------+------------- email_address_check|CHECK (VALUE ~* '^[^@]+@[^@]+\.[^@]+$')| email_address ``` ## 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](https://xata.io/get-access).