querying for specs?

On Mon, 17 Dec 2001 mjbjr@beaudesign.com wrote:

I'm new to sql and to postgres. I've spent quite a bit of time trying to
determine answers to the following questions, but have been unsuccessful.

Say you work at a small company, and the person who was db admin dies
suddenly. How do you:

I'm going to assume you can still get into the server at all.

1) determine what db's postgres knows about?

If you mean database names,
select datname from pg_database;
should do what you want.

Since you would need to know a database to connect to before giving the
query I'd suggest "template1" because it's pretty much guaranteed to
exist.

2) how do you determine what tables exist, if any, in a given db?

Probably easiest to connect to the database with psql and use:
\d
(see \? for more things you can do from psql)

3) how do you determine the structure/specification (not the data) of a table?

Depending on what you want, you might be able to use \d <table>
from psql, but that doesn't give everything. pg_dump -s -t <table> <db>
may be more what you're looking for.

On Mon, Dec 17, 2001 at 09:30:31PM -0800, mjbjr@beaudesign.com wrote:

I'm new to sql and to postgres. I've spent quite a bit of time trying to
determine answers to the following questions, but have been unsuccessful.

Say you work at a small company, and the person who was db admin dies
suddenly. How do you:

Question: Did you read the little help message that pops up when you start
psql?

1) determine what db's postgres knows about?

psql -l

2) how do you determine what tables exist, if any, in a given db?

\d

3) how do you determine the structure/specification (not the data) of a table?

\d [tablename]

You can use pg_dump -s to extract all the grotty details if oyu like.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

On Tue, Dec 18, 2001 at 05:31:55PM +1100, Martijn van Oosterhout wrote:

On Mon, Dec 17, 2001 at 09:30:31PM -0800, mjbjr@beaudesign.com wrote:

I'm new to sql and to postgres. I've spent quite a bit of time trying to
determine answers to the following questions, but have been unsuccessful.

Say you work at a small company, and the person who was db admin dies
suddenly. How do you:

Question: Did you read the little help message that pops up when you start
psql?

Good point. I have, actually, though that was sometime ago, before these
questions came to mind. I have been searching 'sql' docs as of late.

For the most part, the slash commands will give me the answers I need.

Can you get the answers I'm looking for using 'sql' commands that would work
with any "standard" sql db engine?

1) determine what db's postgres knows about?

2) how do you determine what tables exist, if any, in a given db?

3) how do you determine the structure/specification (not the data) of a table?

Thank you for your help.

--
- Martin J. Brown, Jr. -
- mjbjr@beaudesign.com -

Public PGP Key ID: 0xB29EDDCADB184F7B keyserver: http://certserver.pgp.com/

On Mon, Dec 17, 2001 at 11:51:44PM -0800, mjbjr@beaudesign.com wrote:

On Tue, Dec 18, 2001 at 05:31:55PM +1100, Martijn van Oosterhout wrote:

Question: Did you read the little help message that pops up when you start
psql?

Good point. I have, actually, though that was sometime ago, before these
questions came to mind. I have been searching 'sql' docs as of late.

For the most part, the slash commands will give me the answers I need.

Can you get the answers I'm looking for using 'sql' commands that would work
with any "standard" sql db engine?

Heh. I'm afraid that discovering the structure of a database was not
something standardised by the SQL standard. So every database does it
differently. I don't think there exist two databases that do it the same
way.

That said, it may be possible to discover various details using ODBC. After
all, Access does figure out a list of tables and how they're structured when
connecting remotely to a database. But I know of no actual tool to do it.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/