469,090 Members | 1,222 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

HOWTO: Get a table or database definition

I guess it would be great if Pgsql had a way to find a database
definition via a system stored procedure like other database platforms
have.

There are two ways I've found so far:

SELECT
attname as "name", typname as "type", atttypmod - 4 as "size",
relhaspkey as "is_primary_key", *
FROM
pg_class AS a
LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
where a.relname = 'names' and b.attstattarget = -1
order by attnum;

....yields great results for a table called 'names'

The other way is:

pg_dump -h localhost -p 5432 -U root -s -C test | grep -i "CREATE" -A
500000 | grep -v "\-\-" | grep -v "\\connect" | grep -v "SET " | tr -s
"\n"

....shows me a result for host 'localhost', port '5432', user 'root',
database 'test'
Nov 23 '05 #1
2 3322
One other option, which I had forgotten for a long time, was:

\d <object name>

....which can describe many things, although this doesn't give you the
CREATE syntax like a pg_dump can do. Please also note that a pg_dump
can dump output to the screen if you don't specify a file, so if
you're only outputting the schema with "-s -C", it doesn't really
impact a live production database much at all.
Nov 23 '05 #2
Google Mike wrote:
I guess it would be great if Pgsql had a way to find a database
definition via a system stored procedure like other database platforms
have.


There are a few:

nexcerpt=# \df pg_get*def
List of functions
Result data type | Schema | Name | Argument data
types
------------------+------------+----------------------+-----------------------
text | pg_catalog | pg_get_constraintdef | oid
text | pg_catalog | pg_get_constraintdef | oid, boolean
text | pg_catalog | pg_get_indexdef | oid
text | pg_catalog | pg_get_indexdef | oid, integer,
boolean
text | pg_catalog | pg_get_ruledef | oid
text | pg_catalog | pg_get_ruledef | oid, boolean
text | pg_catalog | pg_get_triggerdef | oid
text | pg_catalog | pg_get_viewdef | oid
text | pg_catalog | pg_get_viewdef | oid, boolean
text | pg_catalog | pg_get_viewdef | text
text | pg_catalog | pg_get_viewdef | text, boolean
(11 rows)
It looks like 'pg_get_tabledef' isn't one of them, though.

--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jb***@qtm.net
Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dylan Nicholson | last post: by
61 posts views Thread by Toby Austin | last post: by
3 posts views Thread by Plumer | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.