Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to list custom types using Postgres information_schema

Tags:

sql

postgresql

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

like image 236
Collin Peters Avatar asked Sep 07 '10 16:09

Collin Peters


People also ask

Does Postgres have information_schema?

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.

What is information_schema in PostgreSQL?

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.

How to list user-defined types in PostgreSQL information schema?

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.

How to list all schemas in PostgreSQL?

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

What is the datatype list in PostgreSQL?

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.

How to list all databases in PostgreSQL using PSQL?

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”.


1 Answers

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'); 
like image 158
Collin Peters Avatar answered Sep 23 '22 11:09

Collin Peters