Manually increment values in database

Question

I have a table structure

id | col1 | col2
 1 |  1   | val1
 2 |  2   | val2
 3 |  3   | val3 --
 4 |  4   | val4
 5 |  5   | val1
 6 |  6   | val6

I want to perform a delete action to delete one of the row(say id=3) so my data will become like:

id | col1 | col2
 1 |  1   | val1
 2 |  2   | val2
 4 |  4   | val4
 5 |  5   | val1
 6 |  6   | val6

But I want it to be like

id | col1 | col2
 1 |  1   | val1
 2 |  2   | val2
 4 |  3   | val4
 5 |  4   | val1
 6 |  5   | val6

I want my col1 to be always in sequence irrespective of other columns. Any suggestions on how to go about it. Can it be done in one update query only. I am using POSTGRESQL as my database and using C# for coding. I have around 1000 of rows in the database. Please help me out here.

not familiar with postgresql, but in general update table set col1 = col1 - 1 where col1 > @col1_value_deleted should work. — Giedrius
– Giedrius, Commented Jun 6, 2013 at 11:48
I don't know what you're trying to do, but, you can create a temporary table and insert values in a row through of select. Try to thinking about each delete you execute run a update to correct the sequence? Now your database has 1000 rows, but, thinking in 10,000, 1,000,0000. It's crazy... — Vinicius Lima
– Vinicius Lima, Commented Jun 6, 2013 at 11:53
The whole point of a table is to keep sets of values together, usually because they represent a single entity of sorts. Do you need to store the value of col1? If you don't, consider using a view (or simple query) that calculates the continuous sequence of values for col1 on-the-fly. — Mels
– Mels, Commented Jun 6, 2013 at 12:07

Clodoaldo Neto · Accepted Answer · 2013-06-06 11:52:54Z

2

If it is a scheduled operation, say once per day, then you can keep that ordered column. If not get rid of it and do it in the query:

select
    id,
    row_number() over(order by id) col1,
    col2
from t

answered Jun 6, 2013 at 11:52

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis de Bernardy · Accepted Answer · 2013-06-06 12:07:12Z

In case it's an option, you could do this automatically with a trigger. Something like:

create or replace function maintain_sort_idx() returns trigger as $$
begin
  update yourtable
  set col1 = col1 - 1
  where col1 > old.col1;
  return null;
end;
$$ language plpgsql;

create trigger maintain_sort_idx after delete on yourtable
for each row execute procedure maintain_sort_idx();

Note that it's not bullet proof, e.g. it may or may not work as expected if you delete several rows at the same time. To make it bullet proof, you'd want to amend the update query so it sets col1 = row_number() over (...) where col1 <> row_number() over (...) with whichever appropriate criteria you're using to maintain your sort column. (Your example data would suggest id, but I'm assuming that you've a separate column such as parent_id which is more relevant.)

small_duck · Accepted Answer · 2013-06-06 13:25:52Z

0

Do you really need that col1 in your table? If not (that is, you keep your ordering with the id column, for example), then you can use the rank() function in your queries to return a sequential ordering calculated on the fly. With table:

id | col2
 1 | val1
 2 | val2
 4 | val4
 5 | val5
 6 | val6

This query:

select id, rank() over (order by id), col2 from test

returns

id | col1 | col2
 1 |  1   | val1
 2 |  2   | val2
 4 |  3   | val4
 5 |  4   | val5
 6 |  5   | val6

This could save you a lot of hassle in maintaining that column, and could provide much better performances in certain cases, such as concurrent writes as fewer rows are being impacted.

answered Jun 6, 2013 at 13:25

small_duck

3,10421 silver badges28 bronze badges

Collectives™ on Stack Overflow

Manually increment values in database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related