Restrictions (сonstraints) PostgreSQL: exclude, partial unique, pending restrictions, etc.

Data integrity is easy to break. It so happens that the value 0 falls into the price field due to an error in the application code (news pops up periodically, as in a particular online store, goods were sold for $ 0). Or it happens that the user was deleted from the table, but some data about him remained in other tables, and this data got out in some kind of interface.

PostgreSQL, like any other DBMS, is able to do some checks when inserting / changing data, and it is imperative to be able to use it. Let's see what we can check:

1. Custom subtype via DOMAIN keyword

In PostgreSQL, you can create your own type based on some int or text with an additional check of some things:
')

CREATE DOMAIN us_postal_code AS TEXT CHECK( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' ); 

We create the type us_postal_code, in which we regularly check various spellings of it. Now no one can go there by mistake to write “Barmaleev Street”, there will only be an index:

 CREATE TABLE users ( id integer, name text, email text, postal_code us_postal_code ) ; 

In addition, it improves the readability of the code, as the type itself explains what lies in it, unlike faceless integer or text.

2. Check (especially relevant for checking jsonb and hstore)

Above, we used us_postal_code to use the CHECK operator. You can write exactly the same in the CREATE TABLE construct.

 CREATE TABLE users ( id integer, name text, email text, postal_code us_postal_code, CHECK (length(name) >= 1 AND length(name) <= 300) ) ; 

Or in the table with the goods, you can put a check (price> 0), then you will not sell laptops for 0 rubles. Or you can write a store and use check (superCheckFunction (price)), and check out a bunch of logic in this store.

By the way, the varchar (100) type is the same as the text type with an additional check in length.
You have to understand that check happens every time you insert or update, so if you have 100,500 records per second in your table, then you probably shouldn't do checkout.

It is important to check with checks universal data types, such as jsonb or hstore, because there you can cram anything. You can check for the existence of some keys in json or that their value corresponds to what should be there.

3. Check for uniqueness, both simple and partial.

A simple check that email from different users should be different:

 CREATE TABLE users ( id integer, name text, email text, postal_code us_postal_code, deleted boolean, UNIQUE(email) ) ; 

However, sometimes you need to check the uniqueness not for the entire table, but only, for example, for users with a certain status.

Instead of a simple UNIQUE, you can add such a unique index:

 CREATE UNIQUE INDEX users_unique_idx ON users(email) WHERE deleted = false; 

Then the uniqueness of the email will be checked only for undeleted users. In where you can insert any conditions.

It should also be noted that it is possible to create unique indices for two or more fields at once, i.e. check the unique combination.

4. EXCLUDE

Using the EXCLUDE operator, you can make another kind of uniqueness. The fact is that in the world there are a lot of data types, both embedded and added through extensions. For example, there is an ip4r data type, with its help you can store a range of ip-addresses in one field.

And let's say you need to store non-overlapping ranges in the table. In general, you can check whether the two ranges intersect using the && operator; for example, SELECT '127.0.0.0/24' && '127.0.0.1/32' will return true.

As a result, we do simply:

 CREATE TABLE ip_ranges ( ip_range ip4r, EXCLUDE USING gist (ip_range WITH &&) ); 

And then when inserting / updating, postgres will look at every line, if it does not intersect with the inserted line (i.e., whether the use of the && operator returns true). Thanks to the gist index, this check is very fast.

5. NOT NULL

Everything is clear, the column cannot be NULL. Often (but not necessarily) comes in conjunction with DEFAULT.

For example:

 CREATE TABLE users ( id integer, name text NOT NULL, email text NOT NULL, postal_code us_postal_code, is_married BOOLEAN NOT NULL DEFAULT true, UNIQUE(email) ) ; 

When adding a new column with not null to an existing table, you need to be careful. The fact is that PostgreSQL adds an ordinary column where null is permissible instantly, even if the table is very large, for example, tens of millions of rows. Because it does not need to physically change the data on the disk, null in postgres does not take up space. However, if you add the column name text not null default 'Vasya', then after the fact it is useful to do update each line, and this can take a long time, which may be unacceptable in some situations.

Therefore, such columns are often added to huge tables in two stages, i.e. at first, the new column data is filled in with packs, and only then they put it not null.

6. Primary key, i.e. primary key

Since this is a primary key, it must be unique and cannot be empty. In general, in PostgreSQL, PRIMARY KEY works like a combination of UNIQUE and NOT NULL.

In other databases, PRIMARY KEY does other things, for example, if I'm not mistaken, in MySQL (Innodb), the data is also automatically clustered around PK to speed up access to this field. (By the way, by the way, this can also be done, but manually, with the CLUSTER command. But usually this is not necessary)

7. FOREIGN KEY

For example, you have a table

 CREATE TABLE items ( id bigint PRIMARY KEY, name varhar(1000), status_id int ); 

and table with statuses

 CREATE TABLE status_dictionary ( id int PRIMARY KEY, status_name varchar(100) ); 

You can indicate to the database that the status_id column matches the Id from the status_dictionary table. For example:

 CREATE TABLE items ( id bigint PRIMARY KEY, name varhar(1000), status_id int REFERENCES status_dictionary(id) ); 

Now you can write only null or Id from the status_dictionaries table in status_id, and nothing else.

You can also do this in two fields:

  FOREIGN KEY (a,b) REFERENCES other_table(x,y); 

Again, when inserting, there is some overhead, because with each insertion, the DBMS has to trap a lot of things. Therefore, with (very) intensive insertion, you probably should not abuse the use of the Foreign key.

8. DEFERRABLE

If you need to postpone checking for performance for performance, you can mark the constraints with the keyword DEFERRABLE.

They are of different kinds, for example, if you make UNIQUE (email) DEFERRABLE INITIALLY DEFERRED, then inside the transaction you can write

 SET CONSTRAINTS ALL DEFERRED 

And then all checks will be postponed and in fact will occur only before the word commit
This will work for UNIQUE, PRIMARY KEY and REFERENCES, but will not work for NOT NULL and CHECK.