The tag has no usage guidance.

learn more… | top users | synonyms

2
votes
1answer
66 views

Placing a uniqueness constraint on a date range

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not ...
3
votes
1answer
29 views

Constraint on row value based on values in other rows

Is it possible to have a table with four columns: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3) ); and then ...
4
votes
1answer
70 views

How can I exclude range overlaps if one range may be empty?

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table: CREATE TABLE heights_in_feet( size TEXT NOT NULL, min INTEGER NOT ...
4
votes
1answer
467 views

Uniqueness constraint with date range

Consider a prices table with these columns: id integer primary key product_id integer -- foreign key start_date date not null end_date date not null quantity integer price numeric ...
1
vote
1answer
521 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
1
vote
1answer
510 views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
4
votes
1answer
183 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
3
votes
0answers
412 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
1
vote
1answer
2k views

Constraint based rules engine

I am evaluating software systems which configure products that tout constraint based versus rules based engines. Can someone explain (with maybe an example or two) to a non-expert what the difference ...
19
votes
1answer
7k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...