Postgres Constraint for Unique Datetime Range

Postgres constraint for unique datetime range

You can keep your separate timestamp columns and still use an exclusion constraint on an expression:

CREATE TABLE tbl (
tbl_id serial PRIMARY KEY
, starts_at timestamp
, ends_at timestamp
, EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&) -- no overlap
);

Constructing a tsrange value without explicit bounds as tsrange(starts_at, ends_at) assumes default bounds: inclusive lower and exclusive upper - '[)', which is typically best.

db<>fiddle here

Old sqlfiddle

Related:

  • Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Add constraint to existing table

ALTER TABLE tbl ADD CONSTRAINT tbl_no_overlapping_time_ranges
EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)

Syntax details are the same as for CREATE TABLE.

Applying unique constraint of date on TIMESTAMP column in postgresql

If you do not need a time zone for your created date : create a unique index has follows :

create unique index idx_user_review_uniq_key on  table_name (expiry_date, cast(created_at as date));

If you need that badly to have a time zone then you need to use a little trick (https://gist.github.com/cobusc/5875282) :

create unique index idx_user_review_uniq_key on  table_name (expiry_date, date(created_at at TIME zone 'UTC'));

How to create a unique constraint on the date of a timestamp

Strange, it seems that PostgreSQL allows only for columns in unique constraint, but expressions are not allowed here.



You can create an unique functional index (as a workaround), indexes on expressions are supported from version 9.1:

create unique index service_unique 
ON table_name(patient_recid, date_trunc('day',tservice));

Date ranges unique constraint in Database

You can create a stored procedure wich take datestart and dateend of current row and use them parameter of this procedure. This procedure return 1 if exist in table a bad range and otherwise 0. Then you create your constraint check when this result procedure =0

Add datetime constraint to a PostgreSQL multi-column partial index

You get an exception using now() because the function is not IMMUTABLE (obviously) and, quoting the manual:

All functions and operators used in an index definition must be "immutable" ...

I see two ways to utilize a (much more efficient) partial index:

1. Partial index with condition using constant date:

CREATE INDEX queries_recent_idx ON queries_query (user_sid, created)
WHERE created > '2013-01-07 00:00'::timestamp;

Assuming created is actually defined as timestamp. It wouldn't work to provide a timestamp constant for a timestamptz column (timestamp with time zone). The cast from timestamp to timestamptz (or vice versa) depends on the current time zone setting and is not immutable. Use a constant of matching data type. Understand the basics of timestamps with / without time zone:

  • Ignoring time zones altogether in Rails and PostgreSQL

Drop and recreate that index at hours with low traffic, maybe with a cron job on a daily or weekly basis (or whatever is good enough for you). Creating an index is pretty fast, especially a partial index that is comparatively small. This solution also doesn't need to add anything to the table.

Assuming no concurrent access to the table, automatic index recreation could be done with a function like this:

CREATE OR REPLACE FUNCTION f_index_recreate()
RETURNS void
LANGUAGE plpgsql AS
$func$
BEGIN
DROP INDEX IF EXISTS queries_recent_idx;
EXECUTE format('
CREATE INDEX queries_recent_idx
ON queries_query (user_sid, created)
WHERE created > %L::timestamp'
, LOCALTIMESTAMP - interval '30 days'); -- timestamp constant
-- , now() - interval '30 days'); -- alternative for timestamptz
END
$func$;

Call:

SELECT f_index_recreate();

now() (like you had) is the equivalent of CURRENT_TIMESTAMP and returns timestamptz. Cast to timestamp with now()::timestamp or use LOCALTIMESTAMP instead.

  • Select today's (since midnight) timestamps only

db<>fiddle here

Old sqlfiddle


If you have to deal with concurrent access to the table, use DROP INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY. But you can't wrap these commands into a function because, per documentation:

... a regular CREATE INDEX command can be performed within a
transaction block, but CREATE INDEX CONCURRENTLY cannot.

So, with two separate transactions:

CREATE INDEX CONCURRENTLY queries_recent_idx2 ON queries_query (user_sid, created)
WHERE created > '2013-01-07 00:00'::timestamp; -- your new condition

Then:

DROP INDEX CONCURRENTLY IF EXISTS queries_recent_idx;

Optionally, rename to old name:

ALTER INDEX queries_recent_idx2 RENAME TO queries_recent_idx;

2. Partial index with condition on "archived" tag

Add an archived tag to your table:

ALTER queries_query ADD COLUMN archived boolean NOT NULL DEFAULT FALSE;

UPDATE the column at intervals of your choosing to "retire" older rows and create an index like:

CREATE INDEX some_index_name ON queries_query (user_sid, created)
WHERE NOT archived;

Add a matching condition to your queries (even if it seems redundant) to allow it to use the index. Check with EXPLAIN ANALYZE whether the query planner catches on - it should be able to use the index for queries on an newer date. But it won't understand more complex conditions not matching exactly.

You don't have to drop and recreate the index, but the UPDATE on the table may be more expensive than index recreation and the table gets slightly bigger.

I would go with the first option (index recreation). In fact, I am using this solution in several databases. The second incurs more costly updates.

Both solutions retain their usefulness over time, performance slowly deteriorates as more outdated rows are included in the index.

Postgres date overlapping constraint

Ok i ended up doing this :

CREATE TABLE test (
from_ts TIMESTAMPTZ,
to_ts TIMESTAMPTZ,
account_id INTEGER DEFAULT 1,
product_id INTEGER DEFAULT 1,
CHECK ( from_ts < to_ts ),
CONSTRAINT overlapping_times EXCLUDE USING GIST (
account_id WITH =,
product_id WITH =,
period(from_ts, CASE WHEN to_ts IS NULL THEN 'infinity' ELSE to_ts END) WITH &&
)
);

Works perfectly with infinity, transaction proof.

I just had to install temporal extension which is going to be native in postgres 9.2 and btree_gist available as an extension in 9.1 CREATE EXTENSION btree_gist;

nb : if you don't have null timestamp there is no need to use the temporal extension you could go with the box method as specified in my question.

Create table with date/time range constraint to prevent overlapping

The phrase you want to google for is "postgresql exclusion constraint".

There is an example doing exactly what you want here.

http://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT

SQL Server Unique Key Constraint Date/Time Span

I don't believe that you can do that using a UNIQUE constraint in SQL Server. Postgres has this capability, but to implement it in SQL Server you must use a trigger. Since your question was "how can I do this using a unique key constraint", the correct answer is "you can't". If you had asked "how can I enforce this non-overlapping constraint", there is an answer.



Related Topics



Leave a reply



Submit