Numbering rows consecutively for a number of tables

Question

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,cid ascending and update column xid with incremented values starting with 0. What's the best way to achieve that?

My current solution involves:

• selecting table T
• openining a cursor over the ordered tuples of a table
• assigning the autoincrement value to xid
• inserting the tuple into a temporal table T_temp
• deleting all records from T
• inserting all records from T_temp into T

Since the tables have different schemas, I programmed half of the code in PL/pgSQL and half of the code with bash scripting.

Question 1: Any comments how to have it program that in pure PL/pgSQL?

Question 2: Any comments how to implement that more elegantly?

Answer 1

You want to restart the numbering with 0 for every table.
Use the window function row_number() in an UPDATE:

UPDATE tbl t
SET    xid = n.xid
FROM  (SELECT ctid, tableoid, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n
WHERE  t.ctid = n.ctid
AND    t.tableoid = n.tableoid;

Using ctid as poor man's surrogate for a primary key, since you neglected to disclose your table definition. See:

• Is the system column "ctid" legitimate for identifying rows to delete?

fiddle
_{Old sqlfiddle}