I would like to use UUIDs has my primary key, and I am generating them using the built-in gen_random_uuid() expression for DEFAULT.

However, I don't know what column type to use for UUIDs. When I use uuid or UUID, I get the following error:

PostgreSQL said: column "id" cannot be cast automatically to type uuid Hint: You might need to specify "USING id::uuid".

Is there a native UUID column type? Should I just be using varchar(255)?

Thanks.

CodePudding user response：

PostgreSQL supports a UUID data type 'out of the box' indeed: https://www.postgresql.org/docs/current/datatype-uuid.html

CodePudding user response：

Here's an example of how you might define a table with a UUID primary key using the uuid data type: CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, email TEXT NOT NULL ); In this example, we are creating a table called users with three columns: id, name, and email. The id column is of type uuid and is the primary key of the table. The DEFAULT clause specifies that the gen_random_uuid() expression should be used to generate a new UUID value for the id column if no value is provided when inserting a new row.

If you are getting an error when trying to use the uuid data type, it is possible that you are using a version of PostgreSQL that does not support this data type. In this case, you can use the text data type to store UUID values as text strings.

To cast a text string to a UUID value in PostgreSQL, you can use the ::uuid operator. For example:

SELECT '2c27c970-33ab-11eb-adc1-0242ac120002'::uuid;

This will return the UUID value represented by the text string. You can use this operator in your INSERT and SELECT statements to cast text strings to UUID values as needed.

Keep in mind that using the text data type for UUID values may be less efficient than using the uuid data type, as the uuid data type is specifically designed for storing and manipulating UUID values.