what are the best practices in Postgres for globally converting UUIDs into IDs. I'm currently using both UUIDs and IDs in my schema. The intent is for UUIDs to be exposed to the public (for URLs et.) and IDs for internal systems.

I've looked at the following methods but am unsure of the best practices.

  1. a custom function that does a UUID/ID lookup on the target table
  2. using a BEFORE trigger that does the conversion and keeps all postgres functions clean

Can someone provide some wisdom/suggestions here?


  • Why do you think you need two different identifiers (three, if you count natural keys) for your entities? – mustaccio Oct 12 '19 at 13:33
  • The original motivation was captured by the post shared by Shaunak link. My intended application is to deploy to the cloud and may have SQL instances in various regions in the future. The auto-incrementing Bigint will likely be insufficiently normalized hence the motivation to implement UUIDs. Per the article, I'll be converting my IDs into Bigint/int8 and continue to use UUIDs – Jason Oct 12 '19 at 23:47

For exposing it to public, we kept ID field. Its user friendly to see an ID vs UUID in URL. We replaced ID field from primary key and introduced UUID primary key. ID and UUID both exists for different reasons. For searchabilty, showing in URL, we still continue showing ID field.

This suggests something similar: https://stackoverflow.com/questions/52414414/best-practices-on-primary-key-auto-increment-and-uuid-in-rdbms-and-sql-databas

Some additional reading: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439

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.