How can I have an ordinal key in Postgres and be able to automatically update when inserting a new row in the middle?

Question

Let's say I have a simple table in Postgres 9.5:

CREATE TABLE "public"."outcomes" (
    "id" serial,
    "ordinal" int NOT NULL,
    "outcome" varchar,
    PRIMARY KEY ("id")
);

I need to maintain an arbitrary order, as defined by ordinal. I need to be able to insert a row in between other rows, as defined by their ordinal.

I've got some basic data in the table:

INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('1', 'foo');
INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('2', 'bar');
INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('3', 'baz');

I want to be able to run this:

INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('2', 'glorb');

and have the 'bar' and 'baz' rows automatically increment their ordinal.

I got as far as adding a deferrable unique constraint:

ALTER TABLE outcomes
ADD CONSTRAINT outcomes_ordinal_key
UNIQUE (ordinal) DEFERRABLE INITIALLY IMMEDIATE

and tried to insert thusly:

INSERT INTO outcomes("ordinal", "outcome")
VALUES('2', 'glorb')
ON CONFLICT (ordinal)
DO UPDATE SET "ordinal" = "outcomes"."ordinal" + 1 WHERE "outcomes"."ordinal" >= EXCLUDED.ordinal;

but that yields this error:

ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters

and if the constraint is not deferrable, it immediately fails because when it tries to update bars ordinal from 2 to 3, it conflicts with baz's ordinal.

How can I accomplish this with Postgres?

In doing this you're fighting heap tables and the DB - order almost never matters to a RDBMS. The standard answer is to use an window function like `row_number()` and define your order utilising other data. However, the requirement for the order to be arbitrary stops you from using this approach. TBH, I'd reexamine your requirements - [tell us your actual problem rather than where you're falling down with your proposed solution](http://meta.stackexchange.com/q/66377/179419). — Ben, May 30 '16 at 19:41
@Ben The problem is: I am tracking a series of matches between players. Sometimes, someone may forget to immediately score a match and go back and do it later, after other matches have been played. The order of the matches is important, because the (separate) scoring algorithm for players is dependent on the rating of the players at the time the match was played (i.e. as of the previous match). — micahbf, May 30 '16 at 19:44
Can't you just allow the user to modify the "input" datetime in that case? Stopping the potential for abuse is a UX issue and you can use the datetime for ordering. It doesn't sound like you're likely to have much concurrency going on, which is good but updating all those rows afterwards will always be hellish if your application is of any size if you go down the current route. — Ben, May 30 '16 at 19:48
Yes, the issue is further compounded by historical data which does not have datetimes, in which case it just needs to be ordered by the primary key. So the window function was a bit cumbersome, and I was hoping and explicit order might be easier. But, I agree that this probably isn't the case. — micahbf, May 30 '16 at 20:07
I think that's easy to deal with... `row_number() over (order by desc nulls last, desc)` - you can specify two orders and as you only use the PK when there is no date then this should work - you also get your tiebreaker in the case where two users inserted rows at exactly the same time. — Ben, May 30 '16 at 20:17
I usually solve this by saving those ordinals with an increment. e.g. 10,20,30,40 and so on (good old BASIC trick with linen umbers from the 80's). then you can always insert something in between. And it's pretty easy to renumber the rows in a single update statement once the "gaps" get to small. — , May 30 '16 at 20:21
@a_horse_with_no_name I think in the OP's case he could even do with a *second* serial with increment(stepsize)=10 (and starting at the correct position: current max(ordinal) *10) /uglyhack — wildplasser, May 30 '16 at 21:00
http://stackoverflow.com/a/14092775/905902 previous answer to a very similar question. — wildplasser, May 30 '16 at 22:34
You may use decimal numbers instead of integers. The default value is still the nextval of a sequence, but to insert between A and B, the ordinal value for the new row is `(A+B)/2.0`. — Daniel Vérité, May 30 '16 at 23:52

score 1 · Answer 1 · answered May 31 '16 at 03:04

Enum types to the rescue:

CREATE TYPE public.ordinal_enum AS ENUM ('foo', 'bar', 'baz');

CREATE VIEW public.ordinal AS
SELECT row_number() OVER (ORDER BY oid) AS id,
       row_number() OVER (ORDER BY enumsortorder) AS ordinal,
       enumlabel::text AS outcome
FROM pg_enum 
WHERE enumtypid = 'public.ordinal_enum'::regtype;

-- SELECT * FROM public.ordinal ORDER BY 1;

ALTER TYPE public.ordinal_enum ADD VALUE IF NOT EXISTS 'glorb' BEFORE 'bar';

SELECT * FROM public.ordinal ORDER BY 1;

Easy.

score 0 · Answer 2 · answered May 30 '16 at 19:41

0

You need create an INSERT / DELETE trigger

Insert probably like this

UPDATE outcomes
SET ordinal = ordinal + 1
WHERE ordinal > NEW.ordinal

answered May 30 '16 at 19:41

Juan Carlos Oropeza

47,252
12
78
118

1

This is effectively the "correct" answer, however, the performance will be appalling on an application of any size at all. You'll also be updating the table you're inserting into, which normally causes ACID issues (can't remember how/if Postgres deals with it). – Ben May 30 '16 at 19:50
@Ben You probably right, I just try to help to solve the OP question. – Juan Carlos Oropeza May 30 '16 at 20:04

score 0 · Answer 3 · answered May 31 '16 at 12:28

Building on @Daniel comment to insert the average ordinal:

insert into outcomes (ordinal, outcome)
select (2 + max(ordinal)) / 2, 'glorb'
from outcomes
where ordinal < 2
;
select * from outcomes order by ordinal;
 id | ordinal | outcome 
----+---------+---------
  1 |       1 | foo
  4 |     1.5 | glorb
  2 |       2 | bar
  3 |       3 | baz

insert into outcomes (ordinal, outcome)
select (2 + max(ordinal)) / 2, 'xorb'
from outcomes
where ordinal < 2
;
select * from outcomes order by ordinal;
 id | ordinal | outcome 
----+---------+---------
  1 |       1 | foo
  4 |     1.5 | glorb
  5 |    1.75 | xorb
  2 |       2 | bar
  3 |       3 | baz

The data:

create table outcomes (
    id serial primary key,
    ordinal real not null,
    outcome varchar,
    unique (ordinal)
);
insert into outcomes (ordinal, outcome) values
    (1, 'foo'),(2, 'bar'),(3, 'baz');

How can I have an ordinal key in Postgres and be able to automatically update when inserting a new row in the middle?

3 Answers3