How would I define a column in PostgreSQL such that each value must be in a sequence, not the sequence you get when using type serial but one such that a value 2 cannot be inserted unless there exists a value 1 already in the column?
Asked
Active
Viewed 698 times
2
DanielGibbs
- 9,910
- 11
- 76
- 121
-
Are gaps allowed? What about deletions or updates that change the column's value? – mu is too short Aug 08 '11 at 22:17
-
This is just theoretical, I was wondering how to do it if it was needed. But I would say that such deletions and updates should not be allowed. – DanielGibbs Aug 09 '11 at 00:20
3 Answers
1
I wrote a detailed example of a gapless sequence implementation using PL/PgSQL here.
The general idea is that you want a table to store the sequence values, and you use SELECT ... FOR UPDATE followed by UPDATE - or the shorthand UPDATE ... RETURNING - to get values from it while locking the row until your transaction commits or rolls back.
Community
- 1
- 1
Craig Ringer
- 307,061
- 76
- 688
- 778
0
Theoretically, you could use a constraint that worked like this. (But it won't work in practice.)
- Count the rows.
- Evaluate
max(column) - min(column) + 1. - Compare the results.
You'd probably have to insert one row before creating the CHECK constraint. If you didn't, max(column) would return NULL. With one row,
- Count the rows (1).
- Evaluate
max(column) - min(column) + 1. (1 - 1 + 1 = 1) - Compare the results. (1 = 1)
With 10 rows . .
- Count the rows (10).
- Evaluate
max(column) - min(column) + 1. (10 - 1 + 1 = 10) - Compare the results. (10 = 10)
It doesn't matter whether the sequence starts at 1; this way of checking will always show a gap if one exists. If you needed to guarantee that the gapless sequence started at 1, you could add that to the CHECK constraint.
As far as I know, there isn't any way to do this declaratively with any current dbms. To do it, you'd need support for CREATE ASSERTION. (But I could be wrong.) In PostgreSQL, I think your only shot at this involves procedural code in multiple AFTER triggers.
I only have one table that needs to be gapless. It's a calendar table. We run a query once a night that does these calculations, and it lets me know whether I have a gap.
Mike Sherrill 'Cat Recall'
- 91,602
- 17
- 122
- 185
-
You can do it with explicit locking. Just `LOCK TABLE ... IN EXCLUSIVE MODE` before the `INSERT`, so you block everything except `SELECT`. – Craig Ringer Oct 21 '12 at 01:41
-
I *think* the point was whether a declarative constraint could guarantee a gapless sequence. (The OP *might* not have meant that, but that's want I was aiming at.) An exclusive lock on the table can guarantee a gapless insert, but that's not quite the same thing. – Mike Sherrill 'Cat Recall' Oct 21 '12 at 02:08
-
Ah, that makes sense. So it can really one be done with an `AFTER` trigger as you noted - and even then you have to rely on trigger ordering. – Craig Ringer Oct 21 '12 at 05:29
-1
You write an on insert tigger or a check constraint. However, this will still allow to delete "1" afterwards and "2" stays in the table, you'll probably have to address this too.
C. Ramseyer
- 2,322
- 2
- 18
- 22
-
-
untested, should be something like: `... v integer check (v > select max(v) from mytable)` or `check (v = (select max(v) from mytable) + 1)` – C. Ramseyer Aug 08 '11 at 21:44
-
1A check constraint won't work. A check constraint should be true for each row all the time so you'd have problems when doing updates. – mu is too short Aug 08 '11 at 22:01