What is it

Universal Unique Identifier

Same as GUID (Global Unique IDentifier)

128-bit integer used fro data identification in system

UUID uniqueness is not zero, but very close to it

16 bytes represented as 32 hexadecismal digits (base-16), arranged in 5 groups

• Total 36 characters
  • 32 alphanumeric
  • 4 hyphens

UUID Type

UUID is a dedicated type in PSQL

# cast to uuid
::uuid

Convert Array of Texts to Array of UUIDs

# cast to array of uuids
string[]::uuid[]

# Illustration
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid)
array_remove(u.attributes, '')::uuid[]

&& Operator

What is it

Array will result in true if any one of the elements on the record overlaps

Use && operator to find out whether two array has been overlapped or not

• It will return true only if at least one element from each array match

SELECT * FROM table_name WHERE array['dog', 'cat'] && (value);

Will return records like:

{"dog", "cat", "fish"}
{"lizard", "dog", "parrot"}
{"bear", "bird", "cat"}

Will exclude records like:

{"elephant", "mouse"}

Except and unnest

Return only non-overlapping elements between the two arrays:

base_array = [12,3,5,7,8]
comparison_array = [3,7,8]

Write an operation to only return non-overlapping elements: [12,5]

SELECT array_agg(elements)
FROM (
  SELECT unnest(array[12,3,5,7,8])
  EXCEPT
  SELECT unnest(array[3,7,8])
) t (elements)

unnest

Expands an array to a set of rows.

unnest(ARRAY[1,2]) # 2 rows: 1, 2