如何获得连续的，递增的数字的列，而又不丢失任何数字? [英] How do I get a column with consecutive, increasing numbers, without having any numbers missing?
问题描述
可能的重复项:
如何在PostgreSQL查询中显示行号?
在Postgresql中对带有标识符的列重新排序
使用带有子选择的Update进行PostgreSQL记录重新排序
Possible Duplicates:
How to show row numbers in PostgreSQL query?
Resequencing a column with identifier in Postgresql
PostgreSQL Record Reordering using Update with a Sub-Select
我只是在问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字段需要保持连续. [这是标签顺序] - 在INSERT上:所有优先级> =新记录的记录都应增加其优先级
- 类似:在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
- 基于触发器的更新会翻转其触摸的任何记录的
