Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 5 down vote accepted

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.

-> SQLfiddle demo.

share|improve this answer
3  
An additional (though imaginary) +1 for the poor man's surrogate key :) –  dezso Dec 13 '13 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 '13 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. –  Erwin Brandstetter Dec 18 '13 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 '13 at 12:46
    
@arthur: Due to the MVCC model, every update writes a new row version. –  Erwin Brandstetter Dec 18 '13 at 12:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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