PostgreSQL: How to store UUID value?

Question

I am trying to store UUID value into my table using PostgreSQL 9.3 version.

Example:

create table test
(
   uno UUID,
   name text,
   address text
);

insert into test values(1,'abc','xyz');

Note: How to store integer value into UUID type?

Answer 1

The whole point of UUIDs is that they are automatically generated because the algorithm used virtually guarantees that they are unique in your table, your database, or even across databases. UUIDs are stored as 16-byte datums so you really only want to use them when one or more of the following holds true:

1. You need to store data indefinitely, forever, always.
2. When you have a highly distributed system of data generation (e.g. INSERTS in a single "system" which is distributed over multiple machines each having their own local database) where central ID generation is not feasible (e.g. a mobile data collection system with limited connectivity, later uploading to a central server).
3. Certain scenarios in load-balancing, replication, etc.

If one of these cases applies to you, then you are best off using the UUID as a primary key and have it generated automagically:

CREATE TABLE test (
   uno     uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
   name    text,
   address text
);

Like this you never have to worry about the UUIDs themselves, it is all done behind the scenes. Your INSERT statement would become:

INSERT INTO test (name, address) VALUES ('abc','xyz') RETURNING uno;

With the RETURNING clause obviously optional if you want to use that to reference related data.

Answer 2

It's not allowed to simply cast an integer into a UUID type. Generally, UUIDs are generated either internally in Postgres (see http://www.postgresql.org/docs/9.3/static/uuid-ossp.html for more detail on ways to do this) or via a client program.

If you just want unique IDs for your table itself, but don't actually need UUIDs (those are geared toward universal uniqueness, across tables and servers, etc.), you can use the serial type, which creates an implicit sequence for you which automatically increments when you INSERT into a table.