Resequencing a column with identifier in Postgresql

Question

The following code works and creates a temporary table with a sequence number which is restarted for every new name:

with results as (select row_number() over (partition by name order BY name) as mytid,name from telephn_table)
select * from results order by name

My objective however is to insert the new sequence number permanently into the telephone table. How do I transfer the new sequence number from the results table to the telephone table? I have come across the following for MySql but was not able to convert it to Postgresql.

MySQL: Add sequence column based on another field

Can anyone help?

Herminahermine

Answer 1

If memory serves, row_number() returns the number within its own partition. In other words, row_number() over (partition by name order BY name) would return 1 for each row except duplicates. You likely want rank() over (order by name) instead.

---

After a long discussion:

update telephn_table
set sid = rows.new_sid
from (select pkey,
             row_number() over (partition BY name) as new_sid,
             name
      from telephn_table
      ) as rows
where rows.pkey = telephn_table.pkey;

Disremember

@Denis Thank you for the answer but unfortunately it did not address my question which was "How do I transfer the new sequence number from the results table to the telephone table?". As I stated in my OP the code I listed works to my satisfaction, I just don't know how to copy/transfer the seq no. into the telephone table from the results table. – Herminahermine

Oh, sorry. I thought you knew how to do that part... Just update the table: update yourtable set tid = rows.tid from (select ...) as rows where rows.id = yourtable.id; – Disremember

@Denis Thanks again. If I understood you correctly (and please keep in mind I am a postgresql beginner) this is what I came up with: – Herminahermine

update telephn_table set sid = rows.mytid from (with results as (select rank() over (partition by name order BY name) as mytid,name from telephn_table) select * from results order by name) as rows where rows.name = telephn_table.name; – Herminahermine

This results in the sid column having 1 in all rows. Note that changing from row_number() to rank() gave identical results. What am I doing wrong? – Herminahermine

update telephn_table set sid = rows.mytid from (with results as (select rank() over (order BY name) as mytid,name from telephn_table) select * from results order by name) as rows where rows.tid = telephn_table.sid; – Herminahermine

Probably more like this: update telephn_table set sid = rows.new_sid from (select sid, rank() over (order BY name) as new_sid, name from telephn_table) as rows where rows.sid = telephn_table.sid;. Btw: you really shouldn't be bothering to re-number your primary key. Doing so defeats the whole point in using a surrogate key in the first place. – Disremember

@Denis. Thanks Denis, looks better but not there yet. The following table tells you story: RESULT SHOULD BE "AW";180;1 "AW";180;1 "AY";179;2 "AY";179;1 "AZ";178;3 "AZ";178;1 "BA";290;4 "BA";290;1 "BA";362;4 "BA";362;2 "BB";77;6 "BB";77;1 "BB";174;6 "BB";174;2 "BC";385;8 "BC";385;1 "BC";357;8 "BC";357;2 "BC";56;8 "BC";56;3 "BD";56;11 "BD";56;1 "BD";140;11 "BD";140;2 "BE";357;13 "BE";357;1 "BE";385;13 "BE";385;2 "BE";423;13 "BE";423;3 – Herminahermine

Sorry Denis, the table formatting is awful. Should have two columns headed RESULTS and SHOULD BE. Essentially I am looking for a sequential numbering scheme within name. Each new name should start again from 1. Two entries for the same name should be numbered 1 and 2, and so forth. – Herminahermine

In that case, (partition by name) was correct, as was using row_number() instead of rank(). But my above suggested query is incorrect: you need a primary key, else you won't be able to update your table properly! And then adjust the update query to join on rows.pkey = telephn_table.pkey. Also, no offense but it would help if you read the postgresql tutorial before asking any further questions: postgresql.org/docs/9.0/static/tutorial.html. SO is not a place to crowd-source consulting work. – Disremember

Thank you Denis for your help and advice and absolutely no offence taken – Herminahermine

Answer 2

THIS WORKS! (See my OP link to a previous MySql link. In Postgresql it works without need for a temporary table)

alter table telephn_table add column tid integer default 0; UPDATE telephn_table set tid=(SELECT count(*)+1 from telephn_table t where t.sid < telephn_table.sid and telephn_table.name=t.name)

Herminahermine