-1

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?

  • 1
    That value isn't unique. – a_horse_with_no_name Jul 12 at 7:28
  • @a_horse_with_no_name: the value itself inside the column should not be unique. May ID_INT is better explained as integer representation of ID_STRING. – nali Jul 12 at 7:31
3

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_;
  • There is no primary key inside of this column. – nali Jul 12 at 7:33
  • 2
    @nali: you mean you have a table without a primary key? That is a really bad idea. How do you update a single row in that table? – a_horse_with_no_name Jul 12 at 7:34
  • 1
    @nali: how do you retrieve a single row without a unique key? – a_horse_with_no_name Jul 12 at 7:59
  • 1
    Of course that will run a while it will update all rows and and it needs to sort all rows in order to do the counting. – a_horse_with_no_name Jul 12 at 8:47
  • 1
    @nali: then it's the update that is slow, plus the comparison on ctid is known to be slow as well. – a_horse_with_no_name Jul 12 at 8:51
2

Your requirement is a little difficult to understand. It seems you want a unique ID value per unique string value, but not unique across the entire data set, i.e. if you have ABCDEF multiple times in the data set, the integer value will be the same across them.

If so, you can use the DENSE_RANK() function to produce an incrementing integer id grouped based on the non-unique strings. Example below:

CREATE TABLE DataTable (NonUniqueString VARCHAR(25))

INSERT INTO DataTable
VALUES ('ABCDEF'), ('GHIJKL'), ('ABCDEF'), ('GHIJKL'), ('ABCDEF')

SELECT NonUniqueString,
    DENSE_RANK() OVER (ORDER BY NonUniqueString) AS "Group"
FROM DataTable

Results:

NonUniqueString     Group
-------------------------
ABCDEF              1
ABCDEF              1
ABCDEF              1
GHIJKL              2
GHIJKL              2

NOTE: The example was from MS SQL Server but the DENSE_RANK() function should behave the same in PostgreSQL and uses the same syntax.

  • It looks good and creates the ID's I've expected. But how to add the additional column into the same database? I've tried ALTER TABLE mytbl ADD COLUMN id_num INTEGER; INSERT INTO mytbl ( id_num ) SELECT DENSE_RANK() OVER (ORDER BY id_string ) id_num FROM tracks; – nali Jul 12 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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