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

Question

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)?

Duplicated in dba.StackExchange.com: [Default value for UUID column in Postgres](https://dba.stackexchange.com/q/122623/19079) — Basil Bourque, May 02 '17 at 06:08

score 23 · Accepted Answer · answered Sep 07 '16 at 20:07

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)

SQL Error [42883]: ERROR: function uuid_generate_v4() does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. — Enrico, Nov 02 '20 at 16:15

score 14 · Answer 2 · edited Apr 06 '20 at 18:44

14

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

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

edited Apr 06 '20 at 18:44

fatih

1,010
1
8
20

answered Apr 06 '20 at 13:52

Mitko Keckaroski

954
1
8
12

1

SQL Error [58P01]: ERROR: could not open extension control file "/usr/pgsql-12/share/extension/uuid-ossp.control": No such file or directory – Fatur Mar 27 '21 at 15:42

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

2 Answers2

Linked