PostgreSQL Domain Integrity In Depth


For example, consider URIs. The naive way to store them is in a text field. One step better is to add a regex to match URI syntax, and wrap the check in a custom domain as we have done so many times in this article already.

.. To extract the pieces-to-be from a raw blob of address data you can use PostgreSQL extension helper functions. PostGIS provides a basic parser, and then there’s the pgsql-postal extension powered by libpostal. The latter is the big gun, but may consume large amounts of RAM during its operation.

Email Addresses

Email addresses are case insensitive, so it makes sense to represent them that way. Also it’s not a concern of domain integrity per se, but Web applications often add a uniqueness constraint on user emails. That’s because it doesn’t make sense to use the same email address between multiple user accounts. The constraint ought to prevent duplicates including those of case variation. One way to do this is make a unique index on a text expression, like

CREATE UNIQUE INDEX users_lower_email_key
ON users (LOWER(email));
Unfortunately it’s touchy. Any queries filtering the users table by email must remember to lowercase the prospective value. It would be better if any comparison between emails was case insensitive so that nobody has to remember to explicitly lowercase the values.

This situation is perfect for the citext (aka Case Insensitive Text) extension. It’s a type that stores text verbatim, but compares without regard to case.


A few RDBMSes (PostgreSQL and MySQL) have a special enum type that ensures a variable or column must be one of a certain list of values. This is also enforcible with custom domains.

(‘notice’, ‘warning’, ‘error’, ‘severe’);

— allows convenient queries like
SELECT * FROM log WHERE level >= ‘warning’;
This is the kind of logic you would otherwise have to implement yourself.

If you do want to use the enum type, then changing its values requires some special DDL commands.

Normally CHECK conditions in domains have limited complexity. They can’t contain subqueries, or reference other rows. However using stored procedures we can get around these restrictions.

PostgreSQL 10: Partitions of… partitions!

PostgreSQL version 10 brings a much anticipated feature: (Native) Table Partitioning.

(Emphasis on the NATIVE, PostgreSQL supported partitioning on previous versions by other means.)

There are a few gotchas you have to keep in mind when using this new feature: No PK allowed; No ON CONFLICT clauses; etc… In my experience, these are all just slight annoyances, hardly a reason to not benefit from this amazing feature.

You are also limited to two types of partitioning: RANGE and LIST.

A typical RANGE declaration would look something like this:

CREATE TABLE dt_totals (
dt_total date NOT NULL,
geo varchar(2) not null,
impressions integer DEFAULT 0 NOT NULL,
sales integer DEFAULT 0 NOT NULL
We would then create a table to act as a partition of the above:

CREATE TABLE dt_totals_201801
PARTITION OF dt_totals
FOR VALUES FROM (‘2018-01-01’) TO (‘2018-01-31’);
Any data for January would be stored in this partition. When it comes to performance for large tables, this is a fantastic new feature!

There’s one big reason that Postgres can’t kill Oracle, and it’s not the technology

Postgres is popular but can’t unseat Oracle due to its business practices. Here’s how Oracle wins with its contracts and database stickiness.

A few years back EnterpriseDB, built on Postgres, offered enterprises something they shouldn’t have been able to refuse: All the Oracle database goodness without any of the Oracle. Enterprises were intrigued, but the efforts foundered. The problem? Contracts. As Keith Alsheimer, chief marketing officer for EnterpriseDB, explained:

Some of the practices that Oracle has of locking customers in contractually is a real challenge. Even if they want to move over [to another database system], they still have this number of licenses they bought, and they have to pay support for them [even if those licenses remain unused]. It’s very hard to get out of that.

It’s not merely a matter of contracts, but also how Oracle sells. Over the years, Oracle has acquired a bevy of applications (ERP, CRM, SCM, etc.) and tends not to certify that those applications will work with any other database. They probably would work fine, but not many CIOs would take that risk, especially given that doing so would violate their (wait for it!) contracts.