Using the UUID data type with its 256 chars seems like overkill, while assigning an auto-increment int4 seems less robust.

Is there a more robust way to assign a record id without using uuid or an auto-increment int4?

  • What do you mean when you say "robust". I mean strictly speaking from byte size the answer is correct, and it's a damn good attempt. If so, I would highly suggest editing the title to read "Is there a primary key between int4 and uuid?" that's the only thing the answer addresses. – Evan Carroll Mar 25 '18 at 21:48
  • 1
    Thanks, in retrospect ( this post is more than 2 years old) I think I was trying to understand if there was a way to create an id that was unique locally but also could be unique at a wider level as well. I know better now where I would just use the SERIAL auto-increment id and use the table's column name to guide me – GPP Mar 25 '18 at 23:55

UUID has a fixed size of 16 bytes:

=> select typlen from pg_type where oid = 'uuid'::regtype;

With int4, values have to be between -2147483648 and 2147483647.

If that's too narrow, consider the 8-byte int8, which goes from -9223372036854775808 to 9223372036854775807. Postgres sequences (CREATE SEQUENCE...) use that range.

BIGSERIAL is equivalent to declaring an int8 column tied to a sequence.

  • Verite, thanks! I guess I was asking if there was a recommended approach as well? Are int fields fine, or is there another varchar id field that's smaller than uuid similar to Salesforce's record Ids. – GPP Oct 29 '16 at 16:53

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