Sql – How to declare a range overlapping constraint in PostgreSQL database

constraintspostgresqlrangeunique

Let's say we are having a table with this definition:

range (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  from bigint,
  to bigint
)

This table is actually a reduced view over enormously big table:

item (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  CONSTRAINT item_colour_smell_unique UNIQUE (colour, smell, id)
)

I would like to translate item_colour_smell_unique constraint in the range table. It should watch overlaps of ranges [from, to] while taking account of colourId and smellId column values.

Best Solution

Note that any trigger-based solution is inherently unsafe from race conditions, e.g. when two concurrent transactions insert a row with conflicting ranges, neither of them will see the other conflicting row, due to the "isolation" ACID property (only commited data can be seen).

Some solutions:

Use procedures with explicit locking of the table to force serialization of inserts.
Split the [from, to] range into [from, from+1, ..., to-1, to] and insert a row for each. This way you can use a simple UNIQUE INDEX on the "range" table.

PostgreSQL developer Jeff Davis has been writing about this lately and will implement range conflict constraints in PostgreSQL 8.5

Related Solutions

Python – How to use a decimal range() step value

Rather than using a decimal step directly, it's much safer to express this in terms of how many points you want. Otherwise, floating-point rounding error is likely to give you a wrong result.

You can use the linspace function from the NumPy library (which isn't part of the standard library but is relatively easy to obtain). linspace takes a number of points to return, and also lets you specify whether or not to include the right endpoint:

>>> np.linspace(0,1,11)
array([ 0. ,  0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9,  1. ])
>>> np.linspace(0,1,10,endpoint=False)
array([ 0. ,  0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9])

If you really want to use a floating-point step value, you can, with numpy.arange.

>>> import numpy as np
>>> np.arange(0.0, 1.0, 0.1)
array([ 0. ,  0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9])

Floating-point rounding error will cause problems, though. Here's a simple case where rounding error causes arange to produce a length-4 array when it should only produce 3 numbers:

>>> numpy.arange(1, 1.3, 0.1)
array([1. , 1.1, 1.2, 1.3])

Postgresql – Creating a copy of a database in PostgreSQL

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Still, you may get:

ERROR:  source database "originaldb" is being accessed by other users

To disconnect all other users from the database, you can use this query:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

Best Solution

Related Solutions

Python – How to use a decimal range() step value

Postgresql – Creating a copy of a database in PostgreSQL

Related Question