IS_UUID

Return true if the input string is a valid UUID.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

IS_UUID(str)

Description

## Overview Returns TRUE if the input string is a valid UUID, FALSE otherwise, and NULL for a NULL input. Use this function to validate incoming identifier columns before casting them to UUID, to filter quarantine data, or to guard a downstream join on a UUID primary key. ## Validation rules - Accepts 32 hexadecimal digits optionally broken into the standard 8-4-4-4-12 groups by hyphens. - Case insensitive; uppercase, lowercase, and mixed-case digits are all valid. - No brace or URN prefixes. `{...}` and `urn:uuid:` forms are rejected. - Leading and trailing whitespace is rejected; trim first if needed. - Does not verify the UUID version or variant bits; a string that looks like a UUID but has invalid version nibbles is still accepted. ## Behavior - Returns NULL for NULL input; otherwise always TRUE or FALSE. - Deterministic and side effect free. - Length must be exactly 32 digits (without hyphens) or 36 characters (with hyphens in the standard positions). ## Compatibility - Matches the common PG-compat IS_UUID behavior. Hyphens are optional but, when present, must be at positions 8, 13, 18, and 23.

Parameters

Name	Type	Description
`str`		Specifies the string to validate as a UUID. The standard 8-4-4-4-12 hyphenated form and a plain 32-digit form are both accepted.

Examples

-- Canonical hyphenated UUID
SELECT IS_UUID('550e8400-e29b-41d4-a716-446655440000');  -- true

-- Unhyphenated 32-digit form
SELECT IS_UUID('550e8400e29b41d4a716446655440000');  -- true

-- Uppercase digits (case insensitive)
SELECT IS_UUID('550E8400-E29B-41D4-A716-446655440000');  -- true

-- Not a UUID
SELECT IS_UUID('not-a-uuid');  -- false

-- Filter unparseable external identifiers
SELECT record_id FROM crm.stage.external WHERE NOT IS_UUID(record_id);

-- Validate the output of UUID generation (round-trip sanity check)
SELECT IS_UUID(UUID()) AS round_trip;  -- true

Pitfalls

Hyphenation is validated strictly when present: hyphens must be at positions 8, 13, 18, and 23. Mixed formats such as `550e-8400e29b...` are rejected.
Version and variant bits are not checked. A string of 32 hex digits passes even if it was not produced by a proper UUID generator. Use the type-cast `CAST(... AS UUID)` if you need stricter validation.
Leading and trailing whitespace or enclosing braces cause validation to fail. Normalize with TRIM and strip braces before calling IS_UUID.
A NULL input returns NULL, not FALSE. `WHERE NOT IS_UUID(col)` filters out rows where `col` is NULL; add an explicit `col IS NULL` check if you want to include them.