Jump to content

Database modeling!


Recommended Posts

Hello, I am trying to do some database modeling of a Postgres db. I would like to create a script that can scour a given database and list all of the tables and their respective columns. In MySQL they have a "SHOW TABLES" command, but I have yet to find such a thing for Postgres.


In the long run I would like to be able to visually model a db by running a php script. It may require using some other language with PHP to render it properly on the page. The visual model would include a visual representation of relations between tables. I have seen software for this but I can't afford to buy any and I am running on Mac OS X, so most applications for this won't work for my machine anyways.


I can always try to take a dump file of the db, but I would prefer a way to dynamically update the model on the fly to represent the current db.


Any ideas, suggestions, scripts I haven't found yet?




Link to comment
Share on other sites

There's a bunch of system catalogs which provide this.  These change a little with each major version of postgres.  You can find a list here


Of particular interest:

SELECT * FROM pg_tables WHERE schemaname = 'public';
SELECT * FROM pg_database;
SELECT * FROM pg_indexes WHERE schemaname = 'public';
SELECT * FROM pg_views WHERE schemaname = 'public';


There's also the pg_class table, which is global table containing just about everything.  Those tables I listed above are actually views into pg_class joined with some auxiliary tables.

Link to comment
Share on other sites

Fantastic, that is exactly the kind of thing I am looking for. Eventually I will have to find a way to do this for other databases too because I want to build a class that can model multiple types of SQL databases. 


Currently I am working with a Postgres db, so I will write it for that db first. The hard part for me will be graphically rendering it and adding the ability for users to customize it for testing theoretical changes to the db. Looks like I am going to learn AJAX and probably a few other technologies.


Thanks for the strong start.

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.