Thread: Is it possible to query the database for the values of an enumerated type in 8.3.3?
Is it possible to query the database for the values of an enumerated type in 8.3.3?
From
"Nagle, Gail A \(US SSA\)"
Date:
I have successfully created a user defined enumerated type using the CREATE TYPE some_type_name AS ENUM command using Postgresql 8.3.3.
Is it possible to query the database (e.g. from psql or from the pgAdmin III Query tool) for the enumerated values in the newly added user defined type?
I did not find any direct documentation on this either in the manual or on the web, but I may not know the correct search term(s) to use. Oracle has a DESCRIBE command that does this type of thing for DB objects. Perhaps the values need to be found indirectly by view some system table?
Thank you!
Re: Is it possible to query the database for the values of an enumerated type in 8.3.3?
From
Tom Lane
Date:
"Nagle, Gail A \(US SSA\)" <gail.nagle@baesystems.com> writes:
> Is it possible to query the database (e.g. from psql or from the pgAdmin
> III Query tool) for the enumerated values in the newly added user
> defined type?
regression=# create type myenum as enum ('red','green','blue');
CREATE TYPE
regression=# select enumlabel from pg_enum where enumtypid = 'myenum'::regtype order by oid;
enumlabel
-----------
red
green
blue
(3 rows)
http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html
regards, tom lane
Re: Is it possible to query the database for the values of an enumerated type in 8.3.3?
From
"Nagle, Gail A \(US SSA\)"
Date:
Hello Tom,
Works like a charm. Enum is a better search string than enumerate(d)!
Thank you and much obliged,
Gail
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, December 18, 2008 5:33 AM
To: Nagle, Gail A (US SSA)
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Is it possible to query the database for the
values of an enumerated type in 8.3.3?
"Nagle, Gail A \(US SSA\)" <gail.nagle@baesystems.com> writes:
> Is it possible to query the database (e.g. from psql or from the
pgAdmin
> III Query tool) for the enumerated values in the newly added user
> defined type?
regression=# create type myenum as enum ('red','green','blue');
CREATE TYPE
regression=# select enumlabel from pg_enum where enumtypid =
'myenum'::regtype order by oid;
enumlabel
-----------
red
green
blue
(3 rows)
http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html
regards, tom lane
Re: Is it possible to query the database for the values of an enumerated type in 8.3.3?
From
"Nagle, Gail A \(US SSA\)"
Date:
Hello Larsen, Thank you! This displays the values as an array of strings. Enum is a better search string in the manual than enumerate(d) or enumeration. Best regards, -Gail -----Original Message----- From: Anders Larsen [mailto:Anders.Larsen@framestore.com] Sent: Thursday, December 18, 2008 3:01 AM To: Nagle, Gail A (US SSA) Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Is it possible to query the database for the values of an enumerated type in 8.3.3? Nagle, Gail A (US SSA) wrote: > > Is it possible to query the database (e.g. from psql or from the pgAdmin > III Query tool) for the enumerated values in the newly added user > defined type? > Try this query: select enum_range(null::some_type_name); More information can be found here: http://www.postgresql.org/docs/8.3/static/functions-enum.html