replace unique, noninteger varchar with unique integer in postgres

0
votes

I have a postgres table 'labs' with two columns (col1 varchar, col2 varchar).

col1   |  col2
1.2.3  |   a
1.2.3  |   b
3.4.4  |   c
3.4.4  |   d
2.6.9  |   e
2.6.9  |   f

I would like to replace col1 with an integer like this

col1   |  col2
1  |   a
1  |   b
2  |   c
2  |   d
3  |   e
3  |   f

Does anyone know an efficient statement to do this?

1
sqlpostgresqlcasting

1 Answers

0
votes

You can do this in a select query using:

select dense_rank() over (order by col1) as col1, col2

This doesn't produce exactly the results you want (it would be 1, 3, 2). SQL tables represent unordered sets. Your data doesn't have enough information to assign the second value a value of "2" rather than "3".

You can also incorporate this into an update, but that wouldn't be recommended because the current value is a string and the result you want is an integer.