I am looking at some table DDL and I noticed we have two different data types.
I have never seen this before, what is the difference between them?
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
(referencespg_type.oid
)
Iftypelem
is not zero then it identifies another row inpg_type
, defining the type yielded by subscripting.typarray oid
(referencespg_type.oid
)
Iftyparray
is not zero then it identifies another row inpg_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 functionsarray_in
andarray_out
. Furthermore, this type is what the system uses for constructs such asARRAY[]
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[] |