On 30 September 2015 at 15:37, Thomas Kellerer <spam_eater@gmx.net> wrote:
Hello,
DatabaseMetaData.getTypeInfo() returns not only data types, but also all table types.
Strictly speaking this is correct as the row type for a table is indeed a "data type" in Postgres, but I think the information returned is way too much from a practical point of view.
This could be reduced at least a bit by not only excluding pg_toast, but also pg_catalog and information_schema types:
SELECT t.typname,t.oid FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) WHERE n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema')
(instead of WHERE n.nspname != 'pg_toast' in AbstractJdbc2DatabaseMetaData.java getTypeInfo())
What I find even more confusing (from a JDBC point of view) is that each table type is reported twice:
Once with the table's name and once with an underscore prefixing the table name, e.g. foo and _foo.
These entries map to pg_type rows with typtype = 'c' and 'b' respectively.
I wonder if it would make sense to filter out the "real" tables from this result. And if it does, if there is an efficient way of doing that and not removing real composite types from the result at the same time.
