Re: types

Mark Hage schrieb:
> Hi,
>
> I'm new to postgresql, but do have some experience in MySQL.
>
> I wanted to create a new enum type and used the command:
> CREATE TYPE gender AS ENUM ('male', 'female', 'unknown')
>
> Which returned success.
> When I create a new table I can assign the gender type to a column.
>
> But is it possible to see (and manage) the types I created?
> I use pgAdmin III.

Hi,

first of all you should also use psql - the command line interface for
PostgreSQL. There you have short cut commands. To see the types in the
actual database, type

postgres=# \dT[S+]

which gives you an output of all the types available. Alternatively you
could built a statement like SELECT * FROM pg_catalog.pg_type because
the info about types is stored here. But the short cut is making it
better and fireing this statement:

SELECT n.nspname as "Schema",
   pg_catalog.format_type(t.oid, NULL) AS "Name",
   pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.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 pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;

Altering types - check this out:

postgres=# \h ALTER TYPE
Command:     ALTER TYPE
Description: change the definition of a type
Syntax:
ALTER TYPE name RENAME TO new_name
ALTER TYPE name OWNER TO new_owner
ALTER TYPE name SET SCHEMA new_schema

In pgAdmin you will find the types in the objectbrowser -> your database
-> cataloge -> PostgreSQL (pg_catalog) -> pg_type. Klick on the table
and let all the entries show ...

Cheers

Andy