1

I have a table that requires storing my item in a specific position, and the user can "move" the items' positions to "make room" for the new item.

Here is what the table looks like

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);

I would like to be able to insert a key at position 1 or move the position of a (UNIQUE) key, and that automatically increments the position integer by 1 for every value greater than the new INSERT.

Here is what I have tried so far

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

ERROR: duplicate key value violates unique constraint "keys_context_position_key" DETAIL: Key (context, "position")=(ctx_A, 3) already exists.

But it's not working.

EDIT

I'm using a Docker image postgres:12.7

I found out that using UNIQUE (context, position) DEFERRABLE allow me to exec

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

But somthing like

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;

is still not working!

0

2 Answers 2

3

First, I will answer the question as asked, then suggest a couple of improvements as I see it and then suggest that you completely revise your schema! All the code below is available on the fiddle here. I have also included a new separate fiddle for a "simplified" framework with no PRIMARY KEY update - it makes use of GENERATED columns as a possible simplification strategy - see here.

Question as asked:

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
);

A couple of points to note:

  • I changed the column named "position" to "pos" because the word 'position' is a keyword for PostgreSQL.

  • I put in a column called "marker" as a dummy column so I could keep track of my modifications - you may, of course, remove it - but it helps to have a readily identifiable field for testing.

I then populated it as follows:

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');

Then, I ran:

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;

and then run (check) SELECT * FROM keys ORDER BY key_name;.

Result:

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

So, the new record is there and we have "bumped up" the INTEGER part of the key_name - i.e. the PRIMARY KEY has been modified and order has been preserved.

You could also do this - more flexible because the one thing SQL doesn't do well is string manipulation - strings are (maybe "were" would be better...) considered as "atomic" and splitting them up and putting them back together is not SQL's forté! Having said that, in recent years, there have been vast improvements with regular expression libraries and the like.

So, we could, for example:

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
);

and populate it:

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');

And check with SELECT * FROM keys_bis; - result:

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
...

Then we run the update:

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;

This runs successfully - and we check - SELECT * FROM keys_bis ORDER BY key_alpha, key_num; - result:

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

So, we can see that the INTEGER part of the PRIMARY KEY has been incremented and "bumped up" - we can see, for example, that "marker 4 initial" now has 5 as the numeric part of the PRIMARY KEY.

Schema change:

You really need to revise your schema - put in an INTEGER surrogate key. Updating parts of a PRIMARY KEY has a "bad code smell" about it - you can see this in the contortions that have to be gone through to update the PK. I would strongly suggest that even if the PK were easier to UPDATE, it's not something you should be doing on a regular basis - if it's a one-off integrating with another system, fine, but day-to-day - you need to search for another solution!

I know that you may be in a situation where I have to interface with a legacy system or I'm only the consultant and not allowed to make changes....

Why don't you use context and pos as the PK? Both are already NOT NULL and both together are UNIQUE - therefore good candidates for a PK!

Here are a couple of pieces of code that may help if you want to/can change the table. You can either create a new column and UPDATE or INSERT into a new table using some/all of the functions below (from here and see bottom of fiddle):

--
-- 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;

Result:

             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$%$^%^&^&^&^&

You're better off avoiding regular expressions if possible - they are resource intensive!

Why this is a nightmare!

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
    ...

This can be "handy" and make life a bit easier under certain circumstances - just a thought. À+ et +1 pour un question intéressante qui m'a fait réflechir!

3
  • Hello, thanks you for this amazing answer, i've learned a lot ! this is a small piece of code to explain my issue, keys are the parent table of keys_values linked with a key_name and the keys aren't A.1, A.2 ... but full words separated by dot, i used A.1 for explain and simple purpose
    – albttx
    Oct 28, 2021 at 14:15
  • @albttx - welcome to the forum! I've added some code at the bottom (and corrected a typo - see edits). Your method uses WHERE key_name IN ('A.3', 'A.4'); where you list the values above the value you're inserting! What happens if you've 50 more (or 500 or 5,000...)? You should pass the value in as a parameter and do 1 less than that in the UPDATE - pos >= 2 for an INSERT at pos = 3. HTH!
    – Vérace
    Oct 28, 2021 at 14:36
  • @albttx - I added a couple of bits at the end using GENERATED columns for this - you might be interested?
    – Vérace
    Oct 28, 2021 at 16:33
1

I found out the solution

here is a sql script that show how it's work

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;

OUTPUT:

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: i put it in a public github gist

Not the answer you're looking for? Browse other questions tagged or ask your own question.