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?

  • Could you please show an example of two tables, showing the mentioned IDs?
    – dezso
    Dec 13, 2013 at 17:10
  • 1
    And add the query you tried, even if it's not working. And, as always, your version of Postgres. Dec 13, 2013 at 19:26

1 Answer 1


If I understand correctly, 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, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n
WHERE  t.ctid = n.ctid;

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

SQL Fiddle.
db<>fiddle here

  • 4
    An additional (though imaginary) +1 for the poor man's surrogate key :)
    – dezso
    Dec 13, 2013 at 20:18
  • Excellent solution. I hate myself, because I should have thought of it myself. What is the complexity of the update statement in case no indexes exist on the ctid? Is a similar approach possible in case there's no primary key in the table?
    – arthur
    Dec 18, 2013 at 10:06
  • @arthur: Thanks for providing a fiddle. Consider the updated fiddle. I linked to yours here to preserve the link. Indices are not going to help. Since the whole table is updated, all of it has to be read anyway. Indexes maintenance is just going to add to the cost. As for primary key: My query is designed to work independent of any pk as I tried to make clear. If you have a pk, you can use it instead of the ctid. Dec 18, 2013 at 10:13
  • @ErwinBrandstetter: how does the update work if the whole table needs to be scanned and one field needs to be updated? Would the Postgres (or any smart DBMS) throw all the rows away and insert the new ones, or really bomb the table looking for physical records and updating the correspoindings bits there? My experience is that Postgres is relatively unwise in update queries (despite the genetic query optimizer is doing a pretty decent job), therefore I tend to generate two (delete/insert) queries instead of one update one.
    – arthur
    Dec 18, 2013 at 12:46
  • @arthur: Due to the MVCC model, every update writes a new row version. Dec 18, 2013 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.