How do I add a constraint on the UUID type of Postgres to only allow UUID v4?

Question

I have a lot of tables in a Postgres 14 database that use the native UUID type. Is there a database-wide setting/constraint to limit this type to only allow UUID v4?

A potential solution

I've come across CREATE DOMAIN. I can extend the UUID type, set a CONSTRAINT to CHECK the version bit to be equal to 4. After that, I'd have to include a migration with a lot of ALTER TABLES.

Is there any other way of doing this to an existing database?

score 2 · Answer 1 · answered Jul 17 '23 at 05:14

-- Step 1: Create the uuid_v4 domain
CREATE DOMAIN uuid_v4 AS uuid
    CONSTRAINT uuid_v4_check CHECK (uuid::text ~ '^........-....-4...');

-- Step 2: Find all tables with UUID columns
DO $$DECLARE
    table_rec RECORD;
BEGIN
    FOR table_rec IN (SELECT table_schema, table_name, column_name
                      FROM information_schema.columns
                      WHERE data_type = 'uuid'
                      AND table_schema NOT LIKE 'pg_%'
                      AND table_schema != 'information_schema') -- Exclude system tables
    LOOP
        EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE uuid_v4',
                       table_rec.table_schema, table_rec.table_name, table_rec.column_name);
    END LOOP;
END$$;

This script will create the uuid_v4 domain and then iterate through all tables in the database to find UUID columns. For each UUID column, it will perform an ALTER TABLE statement to set the data type to uuid_v4.

How do I add a constraint on the UUID type of Postgres to only allow UUID v4?

A potential solution

1 Answers1