sqlpostgresqlddl

PostgreSQL "_uuid" data type


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?


Solution

  • 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

    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[]