Convert UUID to IDs in PostgreSQL – Universal Method

performancepostgresqluuid

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.

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

Can someone provide some wisdom/suggestions here?

Thanks!

Best Answer

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

Related Solutions

PostgreSQL – How to Index a UUID

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it.

There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical.

(Hash indexes in PostgreSQL prior to version 10 were not crash-safe and were really a historical relic that tended to perform no better than a b-tree anyway. Avoid them. On PostgreSQL 10 they've been made crash-safe and had some performance improvements made so you may wish to consider them.)

If for some reason you could not use the uuid type, you would generally create a b-tree on the text representation or, preferably, a bytea representation of the uuid.

Postgresql – Convert a lookup value to foreign key when a query is run

There are various ways, depending on the use case. For your example:

INSERT INTO public.people(name, states_id)
VALUES ('John', (SELECT states_id from public.states WHERE name = 'CA'));

Or:

INSERT INTO public.people(name, states_id)
SELECT 'John', states_id
FROM   public.states
WHERE  name = 'CA';

Assuming public.states.name to be unique - there should be a constraint in the table definition.
And a column public.people.states_id, obviously.

Best Answer

Related Solutions

PostgreSQL – How to Index a UUID

Postgresql – Convert a lookup value to foreign key when a query is run

Related Question