PostgreSQL, delete a row with renumbering column values

Question

Here and here are some similar questions to this but not so same that I can apply them to my solution.

I need to delete a row and renumber rows (higher from deleted row) in document with single query. I get an answer here which is not in single query and for that it is not suitable for simple usage.

Here is my example table with all rows of all documents.
Working document will be 'brkalk' with number 2.
For example we will delete row 'brred' 3.

DROP TABLE IF EXISTS kalksad1;

CREATE TABLE kalksad1(
kalk_id     int PRIMARY KEY,
brkalk      integer, 
brred       integer, 
description text);

INSERT INTO kalksad1 VALUES
(12, 2, 5, 'text index 12 doc 2 row 5'),
(26, 2, 1, 'text index 26 doc 2 row 1'),
(30, 2, 2, 'text index 30 doc 2 row 2'),
(32, 4, 1, 'text index 32 doc 4 row 1'),
(36, 1, 1, 'text index 36 doc 1 row 1'),
(37, 1, 2, 'text index 37 doc 1 row 2'),
(38, 5, 1, 'text index 38 doc 5 row 1'),
(39, 5, 2, 'text index 39 doc 5 row 2'),
(42, 2, 3, 'text index 42 doc 2 row 3'),
(43, 2, 4, 'text index 43 doc 2 row 4'),
(46, 3, 1, 'text index 46 doc 3 row 1'),
(47, 3, 2, 'text index 47 doc 3 row 2');

Of course I can easily delete a row with SQL:

DELETE FROM kalksad1 WHERE brkalk=2 AND brred=3

But for good functionality of my program it is needed that there will not be "holes" in row numbering inside one document.

After such deleting I have rows in column 'brred' numbered as 1, 2, 4, 5.

I would like to get query which will renumber rows 4 to 3 and 5 to 4 (actually renumber values in column 'brred') immediately after deletion of row 3 and if possible in single SQL command so I can apply it to all my documents and make function for such deleting.

Since more complicated things like swap and renumber was possible I thing that one may be possible too.

Please if someone can develop described query.

It's impossible to do what you want in a single SQL statement. But it's probably possible in a single transaction or in a stored procedure. *Usually*, though, this kind of requirement suggests you need to redesign your tables. (Also, do you need a unique constraint on {brkalk, brred}?) — Mike Sherrill 'Cat Recall', Nov 28 '13 at 11:48
Hi Mike, I don't know if I say well "single SQL statement" but I give examples which we solved before and which was funtional (swap and renumber) so I suppose "single transaction" will be OK. Unfortuantely, now I can't redesign tables. Care on unique constraint is not needed since client program do that. Just to renumber column 'brred' in document 'brkalk'. — Wine Too, Nov 28 '13 at 11:53

Craig Ringer · Accepted Answer · 2013-11-28T12:51:51.697

You could possibly do this with a single statement if you're prepared to use PostgreSQL extensions like writable common table expressions to nest multiple statements.

In fact, a CTE isn't even required. http://sqlfiddle.com/#!15/6f9c0/11 . Though you can cram the whole lot into a single (horribly ugly) statement with something like:

WITH deleted AS (
    DELETE FROM kalksad1 WHERE brkalk=2 AND brred=3
    RETURNING kalk_id, brkalk
)
UPDATE kalksad1
SET brred = k.brred_new
FROM
(
  SELECT
      k2.kalk_id, 
      row_number() OVER (PARTITION BY k2.brkalk ORDER BY k2.brred) AS brred_new
  FROM (
    SELECT k3.kalk_id, k3.brkalk, k3.brred
    FROM kalksad1 k3
    INNER JOIN deleted ON (k3.brkalk = deleted.brkalk)
    FOR UPDATE
  ) k2
) k
WHERE kalksad1.kalk_id = k.kalk_id
RETURNING kalksad1.kalk_id, kalksad1.brkalk, kalksad1.brred;

It's terribly inefficient to do this, as a look at the query plan will tell you. Appropriate indexes will help but it'll still involve multiple table scans.

Doing it all in one statement also won't solve all the concurrency issues, though the FOR UPDATE subquery should prevent a concurrent DELETE. It's safest to LOCK TABLE kalksad1 IN EXCLUSIVE MODE before running the statement to prevent concurrent insert/update/delete.

A design that requires this is generally considered fairly broken. You should generally be relying on ordering only within the tables, and generating a numbered sequence on demand with something like:

SELECT x, row_number() OVER (ORDER BY x) FROM my_table;

rather than expecting the physical ordering to be consistent. If you must have such an ordering you should do it with a materialized view, so you generate a new side-table with the data rather than updating the original data in-place.

Ugly? I wouldn't say that! One of the nicest and fully functional queries I ever seen. All in one solution. I will keep in mind locking. Thank you very much. — Wine Too, Nov 28 '13 at 14:32

score 2 · Answer 2 · 2013-11-28T12:31:42.293

2

If you really need to renumber those items, you can use something like this:

with renumber as (
  select kalk_id
         brred, 
         row_number() over (partitioin by brkalk order by brred) as rn
  from kalksad1
)
update kalksad1
  set brred = r.rn
from renumber r
where kalksad1.kalk_id = r.kalk_id;

As Craig has pointed out, there is no need to do this in a single statement. Just put the delete and the above into a single transaction.

edited Nov 28 '13 at 12:31

answered Nov 28 '13 at 12:21

Huh. Exact same solution except for subquery vs CTE phrasing. – Craig Ringer Nov 28 '13 at 12:25
@CraigRinger: when I wrote that, you did not have the UPDATE in your answer - otherwise I wouldn't have. – Nov 28 '13 at 12:32
Yep, and yours wasn't posted yet when I was editing. It's like that sometimes. – Craig Ringer Nov 28 '13 at 12:38
Thank you very much "no_name". That promises, I need some time to apply your solution in NET environment then I will refer on situation. – Wine Too Nov 28 '13 at 12:46

PostgreSQL, delete a row with renumbering column values

2 Answers2