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, butCREATE 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
"You Tried to Execute a Query That Does Not Include the Specified Aggregate Function"
How to Get Other Columns When Using Spark Dataframe Groupby
Equivalent Function for Dateadd() in Oracle
Cross Join Without Duplicate Combinations
Regular Expression in Postgresql Like Clause
Can SQL Server Express Localdb Be Connected to Remotely
Why Does Isnumeric('.') Return 1
A Way to Extract from a Datetime Value Data Without Seconds
Is There a Nesting Limit for Correlated Subqueries in Some Versions of Oracle
How to Run Multiple Ddl Statements Inside a Transaction (Within SQL Server)
SQL Comma-Separated Row with Group by Clause
How to Specify Date Literal When Writing SQL Query from SQL Server That Is Linked to Oracle
SQL Query That Gives Distinct Results That Match Multiple Columns