I have scoured the web and PGSQL docs for information on what, to me, is a hidden data type, but have come up short. I am working on an existing database that has functions with return types with preceding double underscores (__some_type). In PGAdmin3, you can optionally display types in your tree view, there I see some recognizable types that are used in various functions and what not, but nowhere do I see these other types. I learned, through PyCharm Full edition, I can connect to a PostgreSQL DB and see all of the types I mention. As well as in the console with "\dT", but not in PGAdmin itself.
Question: What is the deal with types that are preceded with one or two underscores? (__some_type).
Followup: I did find an unverified source describe a convention that restricts users from creating types that start with an underscore, but have not confirmed this from PG sources.
Here is the link to that: https://momjian.us/main/writings/pgsql/aw_pgsql_book/node223.html
And the line I mention is all the way towards the bottom under "NOTES":
Type names cannot begin with the underscore character ("_") and can only be 31 characters long. This is because Postgres silently creates an array type for each base type with a name consisting of the base type's name prepended with an underscore.
Continued searching results: I have found references to what I speak of, but it does not go into detail about the behavior of such types, or what happens if users violate this 'restriction'.
PG Source from 6.3 docs:
As discussed earlier, Postgres fully supports arrays of base types. Additionally, Postgres supports arrays of user-defined types as well. When you define a type, Postgres automatically provides support for arrays of that type. For historical reasons, the array type has the same name as the user-defined type with the underscore character _ prepended. Composite types do not need any function defined on them, since the system already understands what they look like inside.
Restrictions
Type names cannot begin with the underscore character ("_") and can only be 15 characters long. This is because Postgres silently creates an array type for each base type with a name consisting of the base type's name prepended with an underscore.
The restriction on underscores in type names no longer applies. Judging by the docs, it was dropped between 8.2 and 8.3.
The fact that these types don't show up in pgAdmin3 looks like a bug. As you can see from the source, it filters out any type with a leading underscore. It's trying to suppress the auto-generated array types, but the query predates release 8.3 (back when this was still a reliable approach), and was never updated to use the new pg_type.typarray column.
Not sure if this issue is present in pgAdmin4. If you want to stick with pgAdmin3, BigSQL are still maintaining a fork, so they're probably your best bet if you want to see this fixed.