The trick with partial (partial) index

channel #postgresql we heard today a very interesting question (modified for readability):

I need to create a restriction (constraint) on the table. The restriction should validate uniqueness of two fields: string and Boolean. However, there is an additional requirement. There must be only one record with a certain string and value is TRUE. At the same time, there may be several records with a certain string, but a value of FALSE. For example, there may be several {"abc", false}, but only one string {"abc", true}.


Well, it would seem, what's the importance of this objective? It's just a very good example of using flexibility PostgreSQL, which has to its credit the mechanism of partial indexes (partial indices). The solution is simple and elegant:

CREATE TABLE foo(bar TEXT, bing BOOLEAN);

CREATE UNIQUE INDEX baz_index ON foobar(bar, bing) WHERE bing = 't';

INSERT INTO foobar VALUES('1''t');
INSERT INTO foobar VALUES('2''t');
INSERT INTO foobar VALUES('1''f');
INSERT INTO foobar VALUES('1''f');
INSERT INTO foobar VALUES('1''t');
ERROR: duplicate key value violates unique constraint "baz_index"

All ingenious is simple. So what!
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
Далее...

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
Далее...

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
Далее...