default permissible datatype conversion matrix

Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually...

select cast(0x0 as uniqueidentifier);

...but I'm in postgres world now so I whipped out a sensible...

select cast('\x00'::bytea as uuid);

ERROR:  cannot cast type bytea to uuid
LINE 1: select cast('\x00'::bytea as uuid);
               ^

Damn! So I head over to the postgres docs on Type Conversion hoping to see a doc like this one to review which datatypes I can cast between by default without needing to create an explicit cast.

If such a chart does exist in the docs, it's well hidden. Google is likewise not super helpful in this regard.


Is there a good documentation reference for default permissible type casts in postgresql?

To be clear, I don't actually care about the uuid

You can get a list using the psql client with

\dCS

This will show all casts defined between system data types.

In addition, all types can be cast to and from text using the type input and output functions.

StackExchange Database Administrators Q#254252, answer score: 11