Thread: querying for specs?
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: 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 any help you can offer. -- - Martin J. Brown, Jr. - - mjbjr@beaudesign.com - Public PGP Key ID: 0xB29EDDCADB184F7B keyserver: http://certserver.pgp.com/
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/ > Terrorists can only take my life. Only my government can take my freedom.
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/ > Terrorists can only take my life. Only my government can take my freedom.