Skip to main content
This data type stores Universally Unique Identifiers (UUID) as defined by RFC 9562, ISO/IEC 9834-8:2005, and related standards. It is 128-bit in size that is generated by an algorithm chosen to make it very unlikely that the same value will be generated more than once using the same algorithm. Regatta supports generating values using the UUID version 4 algorithm. Alternatively, UUID values can be generated outside Regatta using any algorithm and provided as values in INSERT and UPDATE commands. The data type UUID can be used to store any valid UUID value, regardless of the origin and the UUID version. A UUID value returned from queries is a sequence of lower-case hexadecimal digits, in several groups separated by hyphens: a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits. For example: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 Regatta also accepts the following forms for input:
  • Use of upper-case digits (A-F) or a mix of upper-case and lower-case digits.
  • Surrounding the value with curly braces ( {value} )
  • Omitting some or all of the hyphens, or adding a hyphen after any group of four digits. (In essence, hyphens are treated as “decoration” and are not part of the actual value.)
However, output is always in the standard form of 5 digit groups separated by hyphens, as specified above.

​
Type Conversion Between UUID and String Types

If UUIDs are passed to string functions and operators, they must be explicitly converted to strings. Assignment of UUIDs to a string column implicitly converts the UUID to string. If strings are passed to UUID functions or assigned to UUID columns, they must be explicitly converted to UUIDs. When passing a string and a UUID to a comparison operator (e.g., ‘<’), explicit cast is required on either of the parameters to inform Regatta according to which type the comparison should be performed. A UUID literal is also a string literal, and therefore it can be passed to string functions and assigned to string columns. When two UUID literals are passed to a comparison operator, they are compared as two string literals (because UUID literals and string literals are indistinguishable). If any of them is explicitly cast to UUID, the other is implicitly converted to UUID and a UUID comparison is performed. (It should be noted that extra hyphens - if exist in literals - don’t impact UUID comparisons but do impact string comparisons.) Examples: 
CREATE TABLE t (uuid_col uuid, varchar_col varchar);

-- UUID literals are indistinguishable from string literals
INSERT INTO t VALUES (‘019535d9-3df7-79fb-b466-fa907fa17f9e’, ‘019535d9-3df7-79fb-b466-fa907fa17f9e’); 

-- No implicit conversion on assignment from string to UUID
UPDATE t SET uuid_col = varchar_col; -- ERROR
UPDATE t SET uuid_col = varchar_col::uuid; -- OK

-- Implicit conversion on assignment from UUID to string
UPDATE t SET varchar_col = uuid_col; -- OK

-- No implicit conversion when passing UUID to a string function
SELECT uuid_extract_version(varchar_col) FROM t; -- ERROR
SELECT uuid_extract_version(varchar_col::uuid) FROM t; -- OK

-- No implicit conversion when passing a string to a UUID function
SELECT upper(uuid_col) FROM t; -- ERROR
SELECT upper(uuid_col::varchar) FROM t; -- OK

-- comparison between string and UUID requires explicit cast
SELECT 1 FROM t WHERE uuid_col = varchar_col; -- ERROR
SELECT 1 FROM t WHERE uuid_col::varchar = varchar_col; -- OK
SELECT 1 FROM t WHERE uuid_col = varchar_col::uuid; -- OK

-- comparison between literals:
SELECT '2feebc99-9c0b-4ef8-bf6c-6bb4de380ae5' = '2FEEBC99-9C0B-4EF8-BF6C-6BB4DE380AE5'; -- returns false
SELECT '2feebc99-9c0b-4ef8-bf6c-6bb4de380ae5' = '2FEEBC99-9C0B-4EF8-BF6C-6BB4DE380AE5'::uuid; -- returns true