Postgres - How to add new column uuid

Question

I need to add new column on my table would be uuid data type, here my code:

ALTER TABLE core.example add COLUMN newcolumn SET DATA TYPE UUID USING (uuid_generate_v4())

but show me this error:

ERROR:  type modifier is not allowed for type "uuid"
LINE 1: ALTER TABLE core.example add COLUMN newsi UUID  (uuid_genera...

I dont want to alter a column, would be to create a new column on my table. Any idea how to make this?

Regards

Update for 2025… In Postgres 18+, call uuidv7for a Version 7 UUID value that enables efficient indexing. See my Answer on related Question. — Basil Bourque
– Basil Bourque, Commented Oct 11 at 6:53

THX1138 · Accepted Answer · 2022-06-15 23:55:53Z

16

When adding a new column you don't use SET DATA TYPE. Your statement should look like:

ALTER TABLE core.example ADD COLUMN newcolumn UUID DEFAULT (uuid_generate_v4());

The DEFAULT clause will immediately fill the column with UUIDs.

Alternatively if you you just want to fill the column with initial data, you can drop the DEFAULT clause afterward:

ALTER TABLE core.example ALTER COLUMN newcolumn DROP DEFAULT;

Note that if you are using Postgres 13 and newer it is generally preferrable to use gen_random_uuid() since that method is built-in and does not rely on the uuid-ossp extension.

answered Jun 15, 2022 at 23:55

THX1138

1,83418 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - How to add new column uuid

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related