5

I'm looking for a few ways to find a CREATE TYPE statement for an existing user defined type in a postgres (9.3.3) db.

So far I have tried 3 ways and seem to have failed on each: 1) An SQL Command? pg_catalog.get_pg_typdef does not seem to be implemented at this time. 2) A pg_dump sift? How does one dump a specific type? I don't see a type flag? I've tried "-s" for the schema only but I'd like to specify just the one type. 3) PgAdmin GUI The pgadmin gui is great! I'm running 1.18.1 And it seems to recognize that my user defined types exist (as dependencies of other known objects) but it does not show the create type SQL that I am looking for.

How do you overflowers find type definitions? I feel like I'm close with any of these. Thanks much!

PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit

Improve this question
8
  • Are you looking for a CREATE TYPE statement like the CREATE TABLE statements we can all see in pgAdmin? I'd try to dump the schema with pg_dump, and then search for the type's name: grep -i "your-type-name" your-dump-file. Is it a composite type, an enumerated type, a range type, or a base type? Do you know how it was created? Commented Jan 6, 2015 at 1:36
  • Hi Mike! Thanks for the quick reply... I can see it as a row reflected in the pg_catalog.pg_type table. (It has a typtype of "d") I think that means a domain? I was able to sift through the pg_dump of the public schema. Commented Jan 6, 2015 at 1:45
  • Why do you need the SQL code? What are you actually trying to do? Commented Jan 6, 2015 at 3:31
  • Yes, that's a domain. You use CREATE DOMAIN to create domains. You can find them in pgAdminIII by drilling down to a schema. (Most often the public schema. No guarantees.) Domains are one of the entries under each schema. Commented Jan 6, 2015 at 3:43
  • Unfortunately PostgreSQL's server-side dump facilities are bad-to-nonexistent. There are functions for a few things, like pg_get_viewdef, but most functionality is in pg_dump, and not easily re-used or accessed on fine granularity. Commented Jan 6, 2015 at 4:57
Related questions
1
How to find out the availability of datatype in Postgres
0
How to display all the types that i've created using "CREATE TYPE" in postgresql?
7
PostgreSQL: find information about user defined types
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.