Menu
I am trying to find the equivalent SQL of \dT using the information_schema and can't seem to find anything. Does such a thing exist?
Example: If I add the following custom type enum, how can I see it in the information_schema?
CREATE TYPE communication.channels AS ENUM ('text_message', 'email', 'phone_call', 'broadcast'); NOTE: I do have the exact SQL used by \dT (retrieved by turning up the logging) but I am looking specifically for a cleaner implementation using the information_schema
236
PostgreSQL provides an information_schema schema that contains views that return information about Postgre objects. If the user has the appropriate access, the user can also query tables or views in the pg_catalog schema to get information about Postgres objects.
The information schema is a built-in schema that's common to every PostgreSQL database. You can run SQL queries against tables in the information_schema to fetch schema metadata for a database. For example, the following query fetches the names of all user-defined tables in a database: SELECT. table_name.
Other user-defined types would normally be in the view user_defined_types, but that's not implemented. So at the moment, you can't use the information schema to list user-defined types in PostgreSQL. Show activity on this post. I use a view to show my enum names.
1 Using SQL Syntax There are two ways in which you can use the SQL Synthax to list all schemas from PostgreSQL. ... 2 Using psql In psql all schemas can be listed by executing the next command: /dn You can find a cheat sheet of the important commands in psql here 3 Using DbSchema
Just like the list in pgAdmin3 where you define the datatype for a new column in a table. Show activity on this post. "data types" in PostgreSQL actually includes primitive (built-in) types, types added by extensions, user-defined composite types, domains, and table rowtypes.
Now, we will see how we can list databases using the psql command.\list, or \l can be used. Open your PostgreSQL command prompt and then type SQL to get its command prompt. No type \list and press enter. These are the output that you will see “Three default databases of PostgreSQL”.
For reference, here is the SQL from \dT (pgAdmin uses the same or similar)
SELECT n.nspname as schema, t.typname as type FROM pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid) AND n.nspname NOT IN ('pg_catalog', 'information_schema'); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With