CODEWITHSUNDEEP
postgresqlprimary-keydefaultuuid
user6447029
user6447029

Reputation:

How do I set a default value for a UUID primary key column in Postgres?

I'm using PostgreSQL. I'm trying to create a primary key column taht is a UUID, so I ran this statement

ALTER TABLE my_object_times ADD PRIMARY KEY (id) DEFAULT uuid_generate_v4();

but I get the error

PG::SyntaxError: ERROR:  syntax error at or near "DEFAULT"

What is the proper way to write the above statement (I'm doing alter because I'm changing an existing primary key column)?

Upvotes: 17

Views: 29745

Answers (2)

Mitko Keckaroski
Mitko Keckaroski

Reputation: 1040

In order to use uuid_generate_v4(), you first need to execute:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Upvotes: 16

redneb
redneb

Reputation: 23850

If the column id already exists in the table and you want to modify it by making it the primary key and adding a default value, you can do it in 2 steps:

ALTER TABLE my_object_times ADD PRIMARY KEY (id);
ALTER TABLE my_object_times ALTER COLUMN id SET DEFAULT uuid_generate_v4();

If the column doesn't exist at all, then you can create it with all the attributes you want, by simply doing:

ALTER TABLE my_object_times ADD id uuid PRIMARY KEY DEFAULT uuid_generate_v4();

(I cannot test this right now but it should work)

Upvotes: 24

Related Questions