Как получить столбец с последовательными, увеличивающимися номерами, без каких-либо чисел?

Адаптировано это из предыдущего ответа. Такие вещи случаются часто, когда приложениям нужен порядок табуляции для переменных (чтение: записи в модели EAV), которые также могут быть (частью) альтернативного ключа.

• priority поле должно поддерживаться последовательно. [это порядок табуляции ]
• на INSERT: все записи с приоритетом> = новая запись должна увеличивать приоритеты
• Аналогично: на DELETE -> уменьшено
• если приоритет записи изменяется UPDATE, записи между старым и новым значением приоритета должны иметь приоритеты, смещенные вверх или вниз.
• чтобы избежать рекурсивного вызова триггера:
  • обновления на основе триггеров flipflag любой записи, к которой они прикасаются.
  • И они проверяют old.flipflag=new.flipflag чтобы обнаружить реальные обновления. (которые не вызваны триггером)

  -- Make some data DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; SET search_path=tmp; CREATE TABLE fruits ( id INTEGER NOT NULL PRIMARY KEY , priority INTEGER NOT NULL , flipflag boolean NOT NULL default false , zname varchar NOT NULL , CONSTRAINT unique_priority UNIQUE (priority) DEFERRABLE INITIALLY DEFERRED ); INSERT INTO fruits(id,zname,priority) VALUES (1 , 'Pear' ,4) ,(2 , 'Apple' ,2) ,(3 , 'Orange' ,1) ,(4 , 'Banana' ,3) ,(5 , 'Peach' ,5) ; -- Trigger functions for Insert/update/delete CREATE function shift_priority() RETURNS TRIGGER AS $body$ BEGIN UPDATE fruits fr SET priority = priority +1 , flipflag = NOT flipflag -- alternating bit protocol ;-) WHERE NEW.priority < OLD.priority AND OLD.flipflag = NEW.flipflag -- redundant condition AND fr.priority >= NEW.priority AND fr.priority < OLD.priority AND fr.id <> NEW.id -- exlude the initiating row ; UPDATE fruits fr SET priority = priority -1 , flipflag = NOT flipflag WHERE NEW.priority > OLD.priority AND OLD.flipflag = NEW.flipflag AND fr.priority <= NEW.priority AND fr.priority > OLD.priority AND fr.id <> NEW.id ; RETURN NEW; END; $body$ language plpgsql; CREATE function shift_down_priority() RETURNS TRIGGER AS $body$ BEGIN UPDATE fruits fr SET priority = priority -1 , flipflag = NOT flipflag -- alternating bit protocol ;-) WHERE fr.priority > OLD.priority ; RETURN NEW; END; $body$ language plpgsql; CREATE function shift_up_priority() RETURNS TRIGGER AS $body$ BEGIN UPDATE fruits fr SET priority = priority +1 , flipflag = NOT flipflag -- alternating bit protocol ;-) WHERE fr.priority >= NEW.priority ; RETURN NEW; END; $body$ language plpgsql; -- Triggers for Insert/Update/Delete CREATE TRIGGER shift_priority_u AFTER UPDATE OF priority ON fruits FOR EACH ROW WHEN (OLD.flipflag = NEW.flipflag AND OLD.priority <> NEW.priority) EXECUTE PROCEDURE shift_priority() ; CREATE TRIGGER shift_priority_d AFTER DELETE ON fruits FOR EACH ROW EXECUTE PROCEDURE shift_down_priority() ; CREATE TRIGGER shift_priority_i BEFORE INSERT ON fruits FOR EACH ROW EXECUTE PROCEDURE shift_up_priority() ; -- Do some I/U/D operations \echo Pears are Okay UPDATE fruits SET priority = 1 WHERE id=1; -- 1,4 SELECT * FROM fruits ORDER BY priority; \echo dont want bananas DELETE FROM fruits WHERE id = 4; SELECT * FROM fruits ORDER BY priority; \echo We want Kiwis INSERT INTO fruits(id,zname,priority) VALUES (4 , 'Kiwi' ,3) ; SELECT * FROM fruits ORDER BY priority; 

Результат:

 Pears are Okay UPDATE 1 id | priority | flipflag | zname ----+----------+----------+-------- 1 | 1 | f | Pear 3 | 2 | t | Orange 2 | 3 | t | Apple 4 | 4 | t | Banana 5 | 5 | f | Peach (5 rows) dont want bananas DELETE 1 id | priority | flipflag | zname ----+----------+----------+-------- 1 | 1 | f | Pear 3 | 2 | t | Orange 2 | 3 | t | Apple 5 | 4 | t | Peach (4 rows) We want Kiwis INSERT 0 1 id | priority | flipflag | zname ----+----------+----------+-------- 1 | 1 | f | Pear 3 | 2 | t | Orange 4 | 3 | f | Kiwi 2 | 4 | f | Apple 5 | 5 | f | Peach (5 rows)