I have a number of tables, all containing attributes
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
- openining a cursor over the ordered tuples of a table
- assigning the autoincrement value to
- inserting the tuple into a temporal table
- deleting all records from
- inserting all records from
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?
If I understand correctly, you want to restart the numbering with 0 for every table.
Use the window function
row_number() in an
UPDATE tbl t SET xid = n.xid FROM (SELECT ctid, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n WHERE t.ctid = n.ctid;
ctid as poor man’s surrogate for a primary key, since you neglected to disclose your table definition.