title: SQL: String column to unique integer?
body: <p>I have a table <code>place2022</code> which has a very long <code>CHAR</code> column</p>
  <pre><code>    timestamp    |                                         user_id                                          | pixel_color | coordinate 
  -----------------+------------------------------------------------------------------------------------------+-------------+------------
   17:38:20.021+00 | p0sXpmkcmg1KLiCdK5e4xKdudb1f8cjscGs35082sKpGBfQIw92nZ7yGvWbQ/ggB1+kkRBaYu1zy6n16yL/yjA== | #FF4500     | 371,488
   17:38:20.024+00 | Ctar52ln5JEpXT+tVVc8BtQwm1tPjRwPZmPvuamzsZDlFDkeo3+ItUW89J1rXDDeho6A4zCob1MKmJrzYAjipg== | #51E9F4     | 457,493
   17:38:20.025+00 | rNMF5wpFYT2RAItySLf9IcFZwOhczQhkRhmTD4gv0K78DpieXrVUw8T/MBAZjj2BIS8h5exPISQ4vlyzLzad5w== | #000000     | 65,986
   17:38:20.025+00 | u0a7l8hHVvncqYmav27EARAE6ciLtpUTPXMI33lDrUmtj5Ei3ixlfRuG28KUvs7r5LpeiE/iOKPALVjkILhrYg== | #3690EA     | 73,961
  </code></pre>
  <p>The <code>user_id</code>s are already hashes, so all I really care about here is having <em>some</em> sort of <code>id</code> column which is 1-1 with the <code>user_id</code>.</p>
  <p>I've counted the number of unique <code>user_id</code>s, which is <code>10381163</code>, which fits into 24 bits. Therefore, I can compress the <code>id</code> field down to a 32-bit integer using the obvious scheme of &quot;Assign 1 to the first new user_id you see, 2 to the second new user_id you see&quot;, etc. I don't even care that the user_id's are mapped in the order that they're seen: I just need them to be mapped in an invertible manner to 32-bit ints <em>somehow</em>. I'd also like to persist this mapping somewhere so that, if I want to, I can go backwards.</p>
  <p>What would be the best way to achieve this? I imagine that we could create a new table (<code>create table place2022_user_ids as select distinct(user_id) from place2022;</code>?) and then reverse-lookup the <code>user_id</code> column in that table, but I don't know quite how to formulate the queries and also make sure that I'm not doing something ridiculously slow.</p>
  <p>I am using postgresql, if it matters.</p>