How can I update all rows in Postgresql from 1 to N, where N number of rows?

Question

Find this example https://stackoverflow.com/a/13629639/947111 but it's for sqlserver while I need it for Postgresql.

For example my table has the following structure:

id|title|slot_id
----------------
1 |    1|     1
2 |    2|     1
3 |    3|     1
4 |    1|     2
5 |    2|     2

When I delete row from the middle of set (set defined by slot_id), for example 1,2,3 where slot_id = 1 and row with title = 2 was removed I need to perform renaming so it won't 1,3 but 1,2

https://stackoverflow.com/help/how-to-ask – Dec 27 '16 at 13:57 — , Dec 27 '16 at 13:57

score 5 · Accepted Answer · answered Dec 27 '16 at 13:57

You haven't provide sufficient information about your table structure, so you need to adjust the following query yourself to your table and column names:

update the_table
  set the_column_to_update = t.rn
from (
  select the_primary_key_column, 
         row_number() over (order by the_primary_key_column) as rn
  from x
) t 
where t.the_primary_key_column = x.the_primary_key_column;

score 0 · Answer 2 · edited May 23 '17 at 11:53

The issue in "Update int column in table with unique incrementing values" is referring to adding an automatically incremented id in a table after having defined the table.

In SQL Server this is called an IDENTITY column while in PostgreSQL it is called a SERIAL column.

You can find a similar solution (if that is what you need) in:
Adding 'serial' to existing column in Postgres.

That is if you want to update a column with a value starting from 1 up to N to serve as a unique id for each row of your table.

If you define the table from the beginning as such then you can set one column as SERIAL as in the following article: PostgreSQL Autoincrement

How can I update all rows in Postgresql from 1 to N, where N number of rows?

2 Answers2