Constraints (сonstraints) PostgreSQL: exclude partial unique constraints deferred, etc

data Integrity is easily broken. It so happens that in the price field gets a value of 0 due to an error in the application code (periodically news, in particular, an Internet store selling goods at $ 0). Or is that removed the user from the table, but any data on it left in the other tables, and this data got out in some interface.

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

the

1. Custom subtype using the keyword DOMAIN


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

the 
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 the regexps check the different versions of his writing. Now no one can mistakenly write “Barmaleeva street”, there is only the index:

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

In addition, it improves code readability, as the type itself explains that it is, in contrast to the faceless integer or text.

the

2. Check (especially important to check * PostgreSQL hstore and jsonb)


Above, we used us_postal_code used the operator CHECK. Exactly the same can be written in the design of the CREATE TABLE statement.

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

Or the table with the goods, you can put check (price > 0), then you won't sell the laptops for 0 rubles. Or you can write hranicu and use check(superCheckFunction(price)), and in this Hranice a bunch of logic to check.

By the way, varchar(100) is the same as type of text with optional check for length.
We must understand that the check occurs on every insert or update, so if your table is 100 500 records per second, check it is possible to do.

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

the

3. Checking for uniqueness, both simple and partial.


Simple check email from different users should be different:

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

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

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

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

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

It should also be noted that it is possible to make unique indexes on two or more fields, i.e. to check unique combination.

the

4. EXCLUDE


With the help of the EXCLUDE statement you can make another kind of uniqueness. The fact that posgres many data types, both built-in and added via extensions. For example, there is the ip4r data type, it can be used to store a range of ip addresses in a single field.

And, let's say you want the table to store the disjoint ranges. In General, test cross, whether two ranges using operator &&, for example, SELECT ‘127.0.0.0/24’ && ‘127.0.0.1/32’ will return true.

In the end, just do:

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

And then when you insert/update postgres to watch every line that does not intersect with whether it is inserted (i.e. does not return whether the use of the operator && true). Due to the gist index this test is very quick.

the

5. NOT NULL


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

the 
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 you add new column with not null to the existing table need to be careful. The fact that the conventional column, where valid is null, PostgreSQL adds instantly, even if the table is very large, for example, tens of millions of rows. Because he didn't have to physically change the data lying on a disk, null postgres does not occupy space. However, when you add column name text not null default ‘Bob’, posgres in fact climb to do update each row, and it can take a lot of time that may be unacceptable in some situations.

So often in the huge table the following columns are added in two stages, ie first fill in the data packs of a new column, and then put her not null.

the

6. Primary key i.e. primary key


Time is the primary key, it must be unique and cannot be empty. In General, in PostgreSQL, a PRIMARY KEY works as a combination of UNIQUE and NOT NULL.

In other databases a PRIMARY KEY is doing other things, for example, if I am not mistaken, in MySQL (Innodb), the data and automatically cluster around PK to speed up access by this field. (Posgres, by the way, so too can be done, but manually by the CLUSTER command. But usually this is not necessary)

the

7. FOREIGN KEY


For example, you have a table

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

and the table with the statuses

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

You can specify the status_id column corresponds to the Id of the table status_dictionary. For example:

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

Now you will be able to record only the status_id is null or Id from the table status_dictionaries, and nothing more.

Also you can do it by two fields:

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

When pasting again, there is some overhead, because each time you insert, the DBMS is forced lochit quite a lot of things. Therefore, when a (very) intense insert possible should not be abused utilizing a Foreign key

the

8. DEFERRABLE


If performance necessary to defer the checking of constraints, constraints can be marked DEFERRABLE keyword.

They come in different types, for example if you make UNIQUE(email) DEFERRABLE INITIALLY DEFERRED, then inside a transaction, you can write

the 
SET CONSTRAINTS ALL DEFERRED

And then all the checks will be Delayed and will occur only before the word commit
This will work for UNIQUE, PRIMARY KEY and REFERENCES, but does not work for NOT NULL and CHECK.
Article based on information from habrahabr.ru

Популярные сообщения из этого блога

Approval of WSUS updates: import, export, copy

Изображение
the Backstory Until recently, I worked as edikasyon a major Russian company that has many offices throughout the country. In my jurisdiction there were eleven sites, located in different cities of the Far East (this is important). In each of these offices had its own, not connected with other network infrastructure — your domain AD own subnet, etc. Once the leadership gave me the task to organize the automatic updating of the OS and programs from MS and allowed to expand on accountable sites WSUS . Problem After WSUS was deployed, binding policy, computers to WSUS groups are configured, the directory synchronization content server MS held, etc., the question arose: who and how will approve the update? Imagine 11 cities that are so "fast" and "stable", that rare moment when latency of ICMP replies are only 800 MS, are perceived as unheard of luck. And in each you test all updates before deploying. The access to the servers at the sites was only v...
Далее...

Kaspersky Security Center — the fight for automation

oddly enough, I found habré only one article on the subject — the sandbox much unfinished actually contains a piece of the slightly redesigned the product help. Yes, and Google query klakaut silent. I'm not going to tell you how to administer the hierarchy of Kaspersky Security Center (hereinafter KSC) from the command line — I do not yet need never. Just want to share some thoughts about automation tools to those who need it and disassemble one case with which I had to face. If you, %habrauser%, this topic will be interesting — welcome under kat. Historically, the anti-virus protection on the job, I prefer products of Kaspersky Lab (hereafter LK). Causes and other Holy war of personal opinions, I'll leave behind the scenes. Naturally, I would like to centrally deploy, protect, to protect and to enforce the law to draw beautiful graphics to integrate into existing monitoring systems and to do other work shifting the blame to a healthy server. And if you deplo...
Далее...

The Hilbert curve vs. Z-order

Изображение
Repeatedly heard the opinion that of all swept curves . it the Hilbert curve the most promising for spatial indexing. The explanation given is that it does not contain discontinuities and therefore in some sense “well-arranged”. Whether so it actually and what is the spatial indexing, we will understand under the cut. The concluding article of the series: the the Why doesn't the straightforward approach to spatial indexing swept curves the Presentation the 3D and obvious optimization the 8D and perspectives OLAP the 3D field as it looks the 3D field benchmarks the How about indexing non-point objects? The Hilbert curve has the remarkable property — the distance between two successive points is always equal to one (step current of detail, to be precise). Therefore, data is not much different Hilbert curve values are close to each other (the inverse, incidentally, is wrong — adjacent points may be very different in meaning). For this property it is us...
Далее...