PostgreSQL "_uuid" data type

Question

I am looking at some table DDL and I noticed we have two different data types.

• uuid
• _uuid (yes, it's literally "_uuid")

I have never seen this before, what is the difference between them?

Answer 1

That's an array of uuid you normally see as uuid[]. You can take a look at pg_type: _db<>fiddle

select oid,typname,typcategory,typelem,typarray,typsubscript,typinput,typoutput
from pg_type 
where typname ~ 'uuid';

oid	typname	typcategory	typelem	typarray	typsubscript	typinput	typoutput
2950	uuid	U	0	2951	-	uuid_in	uuid_out
2951	_uuid	A	2950	0	array_subscript_handler	array_in	array_out

• typcategory=A means it's an Array type:

  typcategory char typcategory is an arbitrary classification of data types that is used by the parser to determine which implicit casts should be “preferred”. See Table 51.65.

  Code	Category
  A	Array types

• uuid points at _uuid as its array version, the other one points back at it as its element:

  typelem oid (references pg_type.oid) If typelem is not zero then it identifies another row in pg_type, defining the type yielded by subscripting.
  typarray oid (references pg_type.oid) If typarray is not zero then it identifies another row in pg_type, which is the “true” array type having this type as element

The entry for CREATE TYPE offers some details about the underscore idea:

Whenever a user-defined type is created, PostgreSQL automatically creates an associated array type, whose name consists of the element type's name prepended with an underscore, and truncated if necessary to keep it less than NAMEDATALEN bytes long. (If the name so generated collides with an existing type name, the process is repeated until a non-colliding name is found.) This implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out. Furthermore, this type is what the system uses for constructs such as ARRAY[] over the user-defined type. The array type tracks any changes in its element type's owner or schema, and is dropped if the element type is.

There's more where that came from: when you create a range type, you automatically also get a multirange version, and they both get an array version. Four types for the price of one:

create type ttzrange as range(subtype=timetz);
select oid,typname,typcategory,typelem,typarray,typsubscript,typinput,typoutput
from pg_type 
where typname ~ 'ttz';

oid	typname	typcategory	typelem	typarray	typsubscript	typinput	typoutput
16387	ttzrange	R	0	16384	-	range_in	range_out
16385	ttzmultirange	R	0	16386	-	multirange_in	multirange_out
16384	_ttzrange	A	16387	0	array_subscript_handler	array_in	array_out
16386	_ttzmultirange	A	16385	0	array_subscript_handler	array_in	array_out

Object identifier types and type aliases also deserve a mention:

select '_int4'::regtype as a
      ,'int[]'::regtype as b
      ,'int4[]'::regtype as c
      ,'integer[]'::regtype as d;

a	b	c	d
integer[]	integer[]	integer[]	integer[]