如何在不丢失任何数字的情况下获得一个连续不断增加的数字列



可能的重复:
如何在PostgreSQL查询中显示行号
在Postgresql中重新排序具有标识符的列
PostgreSQL记录重新排序使用带子选择的更新

我只是想问,在PostgreSQL中是否存在这样的可能性,如果我有5行,在一列中有数字1, 2, 3, 4, 5,并且在这些列中不是主键,如果我删除了PostgreSQL重新枚举该列的第四行,那么我可以用1, 2, 3, 4而不是1, 2, 4, 5

根据前面的答案改编。当应用程序需要变量(读取:EAV模型中的记录(的选项卡顺序时,这种情况经常发生,该顺序也可能是备用键的一部分。

  • CCD_ 4字段需要保持连续。[这是选项卡顺序]
  • 插入时:优先级>=的所有记录的优先级都应该增加
  • 类似地:在DELETE上->递减
  • 如果记录的优先级被UPDATE更改,则新旧优先级值之间的记录的优先级应该向上或向下移动
  • 为了避免递归触发器调用:
    • 基于触发的更新翻转他们触摸的任何记录的CCD_ 5
    • 他们测试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)

相关内容

  • 没有找到相关文章

最新更新