Início / dba / Perguntas / 301806
Accepted
albttx
Asked: 2021-10-29 02:03:23 +0800 CST

PostgreSQL: INSERT em uma sequência e incremente todos os valores acima do INSERT (dê "espaço" para o INSERT) [duplicado]

  • 772

Eu tenho uma tabela que requer armazenar meu item em uma posição específica, e o usuário pode "mover" as posições dos itens para "dar espaço" para o novo item.

Aqui está a aparência da mesa

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,
    context    VARCHAR(128) NOT NULL,
    position   INTEGER      NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position)
);

INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

Eu gostaria de poder inserir uma chave na posição 1 ou mover a chave positionde uma ( UNIQUE), e isso incrementa automaticamente o positioninteiro em 1 para cada valor maior que o novo INSERT.

Aqui está o que eu tentei até agora

UPDATE keys
SET position = position + 1
WHERE context = 'ctx_A' AND position >= 2;

ERRO: valor de chave duplicado viola restrição exclusiva "keys_context_position_key" DETALHE: A chave (context, "position")=(ctx_A, 3) já existe.

Mas não está funcionando.

EDITAR

Estou usando uma imagem do Dockerpostgres:12.7

Eu descobri que usar UNIQUE (context, position) DEFERRABLEme permite executar

UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

Mas algo como

BEGIN;
  UPDATE keys SET position=2 WHERE context='ctx_A' AND key_name='A.4';
  UPDATE keys SET position=3 WHERE context='ctx_A' AND key_name='A.3';
COMMIT;

ainda não está funcionando!

postgresql unique-constraint

2 respostas

  1. Best Answer

    Primeiro, responderei à pergunta conforme solicitado , depois sugiro algumas melhorias como vejo e, em seguida, sugiro que você revise completamente seu esquema! Todo o código abaixo está disponível no violino aqui . Eu também incluí um novo violino separado para uma estrutura "simplificada" sem PRIMARY KEYatualização - ele faz uso de GENERATEDcolunas como uma possível estratégia de simplificação - veja aqui .

    Pergunta como feita:

    CREATE TABLE   keys 
(
  key_name   VARCHAR(128),

  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,  -- changed to "pos" - position is a keyword
                                  -- https://www.postgresql.org/docs/12/sql-keywords-appendix.html
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

  marker TEXT NOT NULL,

  CONSTRAINT keys_pk PRIMARY KEY (key_name)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ctxt_pos_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);

    Alguns pontos a serem observados:

    • Mudei a coluna chamada "position" para "pos" porque a palavra 'position' é uma palavra- chave do PostgreSQL.

    • Eu coloquei uma coluna chamada "marcador" como uma coluna fictícia para que eu pudesse acompanhar minhas modificações - você pode, é claro, removê-lo - mas ajuda ter um campo prontamente identificável para teste.

    Então eu preenchi da seguinte forma:

    INSERT INTO keys (key_name, context, pos, marker)
VALUES
('A.1', 'ctx_A', 0, 'marker 1 initial'), 
('A.2', 'ctx_A', 1, 'marker 2 initial'), 
('A.3', 'ctx_A', 2, 'marker 3 initial'), 
('A.4', 'ctx_A', 3, 'marker 4 initial'),
('B.1', 'ctx_B', 0, 'marker 5 initial'), 
('B.2', 'ctx_B', 1, 'marker 6 initial'), 
('B.3', 'ctx_B', 2, 'marker 7 initial'), 
('B.4', 'ctx_B', 3, 'marker 8 initial');

    Então, corri:

    BEGIN TRANSACTION;
  UPDATE keys
    SET 
      key_name = 
        LEFT(key_name, STRPOS(key_name, '.')) || ((SPLIT_PART(key_name, '.', 2)::INT + 1))::TEXT,
      context  = 'ctx_A',
      pos = pos + 1
    WHERE LEFT(key_name, STRPOS(key_name, '.')) = 'A.';
  INSERT INTO keys (key_name, context, pos, marker)
    VALUES ('A.1', 'ctx_A_new', 1, 'marker_new');
COMMIT;

    e, em seguida, execute (check) SELECT * FROM keys ORDER BY key_name;.

    Resultado:

    key_name      context   pos                    created_at   marker
     A.1    ctx_A_new   1   2021-10-28 14:00:46.929092+01   marker_new
     A.2    ctx_A       1   2021-10-28 14:00:46.916923+01   marker 1 initial
     A.3    ctx_A       2   2021-10-28 14:00:46.916923+01   marker 2 initial
     A.4    ctx_A       3   2021-10-28 14:00:46.916923+01   marker 3 initial
     A.5    ctx_A       4   2021-10-28 14:00:46.916923+01   marker 4 initial
     B.1    ctx_B       0   2021-10-28 14:00:46.916923+01   marker 5 initial
     B.2    ctx_B       1   2021-10-28 14:00:46.916923+01   marker 6 initial
     B.3    ctx_B       2   2021-10-28 14:00:46.916923+01   marker 7 initial
     B.4    ctx_B       3   2021-10-28 14:00:46.916923+01   marker 8 initial

    Então, o novo registro está lá e nós "aumentamos" a INTEGERparte do key_name- ou seja, o PRIMARY KEYfoi modificado e a ordem foi preservada.

    Você também pode fazer isso - mais flexível porque a única coisa que o SQL não faz bem é a manipulação de strings - as strings são (talvez "were" seria melhor ...) consideradas "atômicas" e dividindo-as e reunindo-as novamente não é o forte do SQL ! Dito isto, nos últimos anos, houve grandes melhorias com bibliotecas de expressões regulares e similares.

    Assim, poderíamos, por exemplo:

    CREATE TABLE   keys_bis 
(
  key_alpha TEXT         NOT NULL, 

  key_num   INTEGER      NOT NULL,
  
  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,

  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  
  marker TEXT NOT NULL,  -- this is for tracing purposes

  -- It's up to you to ensure that the key_name field is valid!

  CONSTRAINT keys_bis_pk PRIMARY KEY (key_alpha, key_num)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ctxt_pos_bis_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);

    e preenchê-lo:

    INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
VALUES
('A.', 1, 'ctx_A', 0, 'marker 1 initial'), 
('A.', 2, 'ctx_A', 1, 'marker 2 initial'), 
('A.', 3, 'ctx_A', 2, 'marker 3 initial'), 
('A.', 4, 'ctx_A', 3, 'marker 4 initial'),
('B.', 1, 'ctx_B', 0, 'marker 5 initial'), 
('B.', 2, 'ctx_B', 1, 'marker 6 initial'), 
('B.', 3, 'ctx_B', 2, 'marker 7 initial'), 
('B.', 4, 'ctx_B', 3, 'marker 8 initial');

    E verifique com SELECT * FROM keys_bis;- resultado:

    key_alpha   key_num context pos                   created_at        marker
       A.         1   ctx_A   0 2021-10-28 14:31:04.126172+01   marker 1 initial
       A.         2   ctx_A   1 2021-10-28 14:31:04.126172+01   marker 2 initial
       A.         3   ctx_A   2 2021-10-28 14:31:04.126172+01   marker 3 initial
...
... snipped for brevity
...

    Em seguida, executamos a atualização:

    BEGIN TRANSACTION;
  UPDATE keys_bis 
    SET 
      key_num = key_num + 1,
      context = 'ctx_A',
      pos   = pos + 1
    WHERE key_alpha = 'A.';
  INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
  VALUES ('A.', 1, 'ctx_A_new', 1, 'marker_new');
COMMIT;

    Isso é executado com sucesso - e verificamos - SELECT * FROM keys_bis ORDER BY key_alpha, key_num;- resultado:

    key_alpha   key_num   context   pos                    created_at       marker
       A.         1 ctx_A_new   1   2021-10-28 14:42:39.161205+01   marker_new
       A.         2 ctx_A       1   2021-10-28 14:42:39.148996+01   marker 1 initial
       A.         3 ctx_A       2   2021-10-28 14:42:39.148996+01   marker 2 initial
       A.         4 ctx_A       3   2021-10-28 14:42:39.148996+01   marker 3 initial
       A.         5 ctx_A       4   2021-10-28 14:42:39.148996+01   marker 4 initial
       B.         1 ctx_B       0   2021-10-28 14:42:39.148996+01   marker 5 initial
       B.         2 ctx_B       1   2021-10-28 14:42:39.148996+01   marker 6 initial
       B.         3 ctx_B       2   2021-10-28 14:42:39.148996+01   marker 7 initial
       B.         4 ctx_B       3   2021-10-28 14:42:39.148996+01   marker 8 initial

    Assim, podemos ver que a INTEGERparte do PRIMARY KEYfoi incrementada e "aumentada" - podemos ver, por exemplo, que "marker 4 initial"agora tem 5como parte numérica do PRIMARY KEY.

    Mudança de esquema:

    Você realmente precisa revisar seu esquema - coloque uma INTEGERchave substituta. Atualizar partes de um PRIMARY KEYtem um "cheiro de código ruim" - você pode ver isso nas contorções que precisam ser feitas para atualizar o arquivo PK. Eu sugeriria fortemente que, mesmo que fosse mais PKfácil UPDATE, não é algo que você deveria fazer regularmente - se for uma integração única com outro sistema, tudo bem, mas no dia-a-dia - você precisa procurar outro solução!

    Eu sei que você pode estar em uma situação onde I have to interface with a legacy systemou I'm only the consultant and not allowed to make changes....

    Por que você não usa contexte poscomo o PK? Ambos já são NOT NULLe ambos juntos são UNIQUE- portanto, bons candidatos para um PK!

    Aqui estão alguns pedaços de código que podem ajudar se você quiser / puder alterar a tabela. Você pode criar uma nova coluna e UPDATEou INSERTem uma nova tabela usando algumas/todas as funções abaixo (a partir daqui e veja a parte inferior do violino ):

    --
-- A few ideas from https://stackoverflow.com/a/51193857/470530
--

WITH test AS
(
  SELECT 'AASDFSD.435434' AS v
  UNION ALL SELECT 'SDXVZ.343534'
  UNION ALL SELECT 'AEE#3434$%$^%^&^&^&^&AAL.12345'
)
SELECT
  v,
  RIGHT(v, LENGTH(v) - STRPOS(v, '.')),
  SUBSTRING(v FROM '^[A-Z]+[\.]'),  -- will pick AA., BBB., CCSXDEER., 
  SUBSTRING(v FROM '\d+$'),
  (select (regexp_matches(v, '[^A,]+', 'g'))[1] offset 0 limit 1) as c1
FROM
  test;

    Resultado:

                 v  right   substring   substring   c1
AASDFSD.435434  435434  AASDFSD.    435434  SDFSD.435434
SDXVZ.343534    343534  SDXVZ.  343534  SDXVZ.343534
AEE#3434$%$^%^&^&^&^&AAL.12345  12345       12345   EE#3434$%$^%^&^&^&^&

    É melhor evitar expressões regulares, se possível - elas consomem muitos recursos!

    Por que isso é um pesadelo!

    To perform an UPDATE in the middle of the table, you have to manually keep track of all of the previous UPDATEs so you know which parts of the PK to UPDATE - as follows:

    BEGIN TRANSACTION;
  UPDATE keys_bis 
    SET 
      key_num = key_num + 1,
      context = 'ctx_A',
      pos   = pos + 1
    WHERE (key_alpha = 'A.' AND pos >= 2);
  INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
  VALUES ('A.', 3, 'ctx_A_new_3', 3, 'marker_new_3');
COMMIT;

    See fiddle for result (at bottom).

    It will work, as long as you remember to set:

    WHERE (key_alpha = 'A.' AND pos >= 2);

    and

    VALUES ('A.', 3, 'ctx_A_new_3', 3, 'marker_new_3');

    The pos value in the UPDATE clause to 1 less than the new value of pos in the WHERE clause! A good candidate for a function therefore? You have a function in your own answer,but you're doing this manually here:

     WHERE key_name IN ('A.3', 'A.4');

    If you're going to use a function, then at least set it in the parameters to the function. What happens if you have 500 values (or 5,000 or...)?

    A simplification:

    I did a separate fiddle for this part - see here.

    I reread the question and noticed that it wasn't in fact the PK that required UPDATEing - it was the UNIQUE constraint - the principles however are very similar. For the purposes of a complete explanation of how I would go about this, and making use of an extremely useful feature of all of the major RDBMSs - that is GENERATED fields/columns! GENERATED is the SQL Standard name.

    These also go by the name COMPUTED, CALCULATED, PERSISTED, DERIVED or even VIRTUAL - although the VIRTUAL (best avoided) keyword is also (confusingly) used for the storage class of the column (STORED or VIRTUAL) - the first meaning that the value is actually stored on disk and the second meaning that it's calculated on the fly. As of the time of writing (28/10/2021), PostgreSQL doesn't support VIRTUAL columns.

    I created a new table as follows:

    CREATE TABLE   keys_ter 
(
  key_name   VARCHAR(128),
  context    VARCHAR(128) NOT NULL,
  pos   INTEGER      NOT NULL,  
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  marker TEXT NOT NULL,

  key_alpha TEXT GENERATED ALWAYS AS (LEFT(key_name, STRPOS(key_name, '.'))) STORED,

  key_num   INT  GENERATED ALWAYS AS (SPLIT_PART(key_name, '.', 2)::INT) STORED,

  CONSTRAINT keys_ter_pk PRIMARY KEY (key_name)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ter_ctxt_pos_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);

    Populate as for the original keys table - then SELECT * FROM keys_ter ORDER BY key_alpha, key_num; - result:

    key_name    context pos                   created_at    marker  key_alpha    
    key_num
     A.1      ctx_A   0 2021-10-28 17:02:17.48598+01    marker 1 initial    A.  1
     A.2      ctx_A   1 2021-10-28 17:02:17.48598+01    marker 2 initial    A.  2
     A.3      ctx_A   2 2021-10-28 17:02:17.48598+01    marker 3 initial    A.  3
...
... snipped for brevity
...

    We can see the GENERATED fields - this makes things slightly easier when UPDATING if we happen to know the INTEGER part of the PK as follows:

    BEGIN TRANSACTION;  -- INSERTING new record between pos at 2 new record - pos = 2
  UPDATE keys_ter
    SET 
      pos = pos + 1
  WHERE key_num > 2 AND context = 'ctx_A';              -- we know the key_num = 2!
  INSERT INTO keys_ter (key_name, context, pos, marker)
    VALUES('A.43', 'ctx_A', 2, 'marker_new_A.43');
COMMIT;

    Update successful - we check SELECT * FROM keys_ter ORDER BY context, pos; - result:

        key_name    context pos                   created_at    marker   
   key_alpha    key_num
         A.1      ctx_A   0 2021-10-28 17:02:17.48598+01    marker 1 initial    A.  1
         A.2      ctx_A   1 2021-10-28 17:02:17.48598+01    marker 2 initial    A.  2
        A.43      ctx_A   2 2021-10-28 17:02:17.499004+01   marker_new_A.43 A.  43
         A.3      ctx_A   3 2021-10-28 17:02:17.48598+01    marker 3 initial    A.  3
         A.4      ctx_A   4 2021-10-28 17:02:17.48598+01    marker 4 initial    A.  4
         B.1      ctx_B   0 2021-10-28 17:02:17.48598+01    marker 5 initial    B.  1
    ...
    ... snipped for brevity
    ...

    Isso pode ser "útil" e tornar a vida um pouco mais fácil sob certas circunstâncias - apenas um pensamento. À+ et +1 pour un question intéressante qui m'a fait réflechir!

    • 3

  2. descobri a solução

    aqui está um script sql que mostra como funciona

    DROP TABLE IF EXISTS keys;

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,

    context    VARCHAR(128) NOT NULL,

    position   INTEGER      NOT NULL,

    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position) DEFERRABLE
);

CREATE OR REPLACE FUNCTION glide_keys_position()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE keys
    SET position = position + 1
    WHERE
        context = NEW.context AND position >= NEW.position;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_glide_keys_position
BEFORE INSERT ON keys
FOR EACH ROW EXECUTE PROCEDURE glide_keys_position();


-- here a sample of data
INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

-- UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

INSERT INTO keys (key_name, context, position) VALUES ('A.10', 'ctx_A', 2);

BEGIN;
  SET CONSTRAINTS keys_context_position_key DEFERRED;

  UPDATE keys
  SET position = CASE key_name
    WHEN 'A.4' THEN (SELECT position FROM keys WHERE context='ctx_A' AND key_name = 'A.3')
    WHEN 'A.3' THEN (SELECT position FROM keys WHERE context='ctx_A' AND key_name = 'A.4')
  END
  WHERE key_name IN ('A.3', 'A.4');
COMMIT;

SELECT * FROM keys WHERE context='ctx_A' ORDER BY position;

    RESULTADO:

    DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 8
INSERT 0 1
BEGIN
SET CONSTRAINTS
UPDATE 2
COMMIT
 key_name | context | position |          created_at
----------+---------+----------+-------------------------------
 A.1      | ctx_A   |        0 | 2021-10-28 11:09:44.667153+00
 A.2      | ctx_A   |        1 | 2021-10-28 11:09:44.667153+00
 A.10     | ctx_A   |        2 | 2021-10-28 11:09:44.669278+00
 A.4      | ctx_A   |        3 | 2021-10-28 11:09:44.667153+00
 A.3      | ctx_A   |        4 | 2021-10-28 11:09:44.667153+00
(5 rows)

    ps: eu coloquei em um gist público do github

    • 1

relate perguntas