Database Reference
In-Depth Information
Download and install the ip4r datatype module for PostgreSQL, so we can get access to a good
datatype for solving this type of problem. Download the ip4r module from the following website:
http://pgfoundry.org/projects/ip4r/
and then create a table like the following, and populate it with the same data in a slightly
different form:
CREATE TABLE iprange2
(iprange ip4r
,owner text);
INSERT INTO iprange2 VALUES
('192.168.0.1-192.168.0.16', 'Simon');
INSERT INTO iprange2 VALUES
('192.168.0.17-192.168.0.24', 'Greg');
INSERT INTO iprange2 VALUES
('192.168.0.32-192.168.0.64', 'Hannu');
You can now create a unique exclusion constraint on the table using the following command:
ALTER TABLE iprange2
ADD EXCLUDE USING GIST (iprange WITH &&);
Real World Example: Range of time
In many databases there will be historical data tables with data that has a
START_DATE
and an
END_DATE
, or similar. Another external module for PostgreSQL supports this, with
a datatype named a "period".
A period is a pair of TIMESTAMPTZ values that allow you to define the start and end
timestamptz (date/time). Take a look at the following website:
http://temporal.projects.postgresql.org/
Real World Example: Prefix ranges
Another common problem is assigning credit card numbers or telephone numbers. For
example, with credit card numbers, we may need to perform additional checking for certain
financial institutions.
The prefix range datatype has been specifically designed to address this class of problem.
Again, this is available as a PostgreSQL plugin at the following URL:
http://github.com/dimitri/prefix
