Create an unique integer id column for an existing string column I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this:

| ID_STRING |
| 'ABCDEFG' |
| 'HIJKLMN' |


Now I want to compute integer ids for each element and store them into an additional column. The result should look like this:

| ID_STRING | ID_INT
| 'ABCDEFG' |   1
| 'HIJKLMN' |   2
| 'ABCDEFG' |   1
| 'HIJKLMN' |   2


Is there an easy way to achieve this?
To add the new column use:

ALTER TABLE the_table ADD COLUMN id_int integer;


To populate the new column you need an UPDATE statement. 

I am assuming you have a primary key column named pk_column in your table. Obviously you need to replace that with the actual primary key column in your table.

update the_table
   set id_int = t.rn
from (
  select pk_column, 
         dense_rank() over (order by id_string) as rn
  from the_table
) t
where the_table.pk_column = t.pk_column;




If you really have a table without a primary key (why?), you can use the built-in ctid instead:

update the_table
   set id_int = t.rn
from (
  select ctid as id_, 
         dense_rank() over (order by id_string) as rn
  from the_table
) t
where the_table.ctid = t.id_;