如何获得具有连续递增数字的列，而不会丢失任何数字? [英] How do I get a column with consecutive, increasing numbers, without having any numbers missing?
本文介绍了如何获得具有连续递增数字的列，而不会丢失任何数字?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
可能的重复:
如何在 PostgreSQL 查询中显示行号?
在 Postgresql 中对具有标识符的列重新排序
PostgreSQL 记录重新排序使用带有子选择的更新
我只是问在 PostgreSQL 中是否存在这样的可能性，如果我有 5 行并且在一列中有数字 1, 2, 3, 4, 5 并且在这些列中不是主键如果我删除 postgreSQL 重新枚举该列的第三行，那么我可以使用 1, 2, 3, 4 而不是 1, 2, 4, 5?
I am just asking if in PostgreSQL exist such possibility that if I have 5 rows and in one column there are numbers 1, 2, 3, 4, 5 and in those column is not primary key if I delete say tird row that postgreSQL re enumerate this column so I can have 1, 2, 3, 4 instead of 1, 2, 4, 5?
推荐答案
改编自上一个答案.当应用程序需要一个 tabbing-order 变量(读取:EAV 模型中的记录)时，这种事情经常发生，它也可能是(部分)备用键.
Adapted this from a previous answer. This kind of stuff happens often when applications want a tabbing-order for variables (read: records in an EAV model) , which could also be (part of) an alternate key.
priority字段需要保持连续.[这是tabbing-order]- 在插入时:所有具有优先级 >= 新记录的记录的优先级应该增加
- 同样:在 DELETE -> 递减
- 如果一条记录的优先级被 UPDATE 改变，那么旧优先级值和新优先级值之间的记录的优先级应该向上或向下移动.
- 避免递归触发器调用:
- 基于触发器的更新会翻转它们接触的任何记录的
flipflag. - 他们测试
old.flipflag=new.flipflag以检测真实 更新.(那些不是由触发器引起的)
- the
priorityfield needs to be kept consecutive. [this is the tabbing-order] - on INSERT: all records with priority >= the new record should have their priorities incremented
- similarly: on DELETE -> decremented
- if a record's priority is altered by an UPDATE, the records between the old and the new priority value should have their priorities shifted up or down.
- to avoid recursive trigger invocation:
- the trigger-based updates flip the
flipflagof any record they touch. - And they test for
old.flipflag=new.flipflagto detect real updates. (those not caused by a trigger)
-- 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)这篇关于如何获得具有连续递增数字的列，而不会丢失任何数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
- the trigger-based updates flip the
- 基于触发器的更新会翻转它们接触的任何记录的
查看全文
