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?

Denis de Bernardy · Accepted Answer · 2011-05-18 16:36:53Z

3

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;

edited May 18, 2011 at 16:36

answered May 18, 2011 at 1:42

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

george Over a year ago

@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.

Denis de Bernardy Over a year ago

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;

george Over a year ago

@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:

george Over a year ago

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;

george Over a year ago

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?

|

george · Accepted Answer · 2011-05-18 21:53:20Z

0

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)

answered May 18, 2011 at 21:53

george

1

Collectives™ on Stack Overflow

Resequencing a column with identifier in Postgresql

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related