Search code examples
postgresqluuid

Postgres - How to add new column uuid

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

Solution

  • 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.