Thread: Switching from MySQL -- Missing DESCRIBE table, SHOW TABLES
Hello, I had employed MySQL for a couple of former projects. But now have decided to switch to PostgreSQL...not that version 8 also works on that, ahem...other OS...which I'm stuck with at work. But alas, two of the most useful commands appear to be missing: SHOW TABLES DESCRIBE table Inasmuch as my prototyping DB is on my NetBSD server at home while my data waiting to be 'based is at work...such that I have to connect via Perl/DBI and XML-RPC (not psql, alas). The IT dept here just says, "Use MS-Access", so no help there. While I'm in the innitial stage I need an informative way to blunder around and see what's what as I try different ways to build this thing. For that I had always relied on the two above from MySQL. I can't believe there is no way for PostgreSQL to tell me what the current DB's table structure is via simple SQL queries executed remotely. Surely there must be. But I can't seem to find out from the couple of books I have. All I dug up was some ultra-lame hack to get column names for an already known table name by doing a "WHERE 1 != 1" or some such so that no actual rows could be returned. Not very informative, that. Surely I've missed the point, somewhere. So enlighten me, please. What, pray tell, are the PostgreSQL-ish SQL queries one uses so as to explore a given DB's table structure? What is the PostgreSQL translation for "SHOW TABLES" and "DESCRIBE table"? Thanks in advance, Gan Starling Kalamazoo MI
On Sun, 2005-10-16 at 17:17 -0400, Gan Uesli Starling wrote: > But alas, two of the most useful commands appear to be missing: > > SHOW TABLES > DESCRIBE table ... > I can't believe there is no way for PostgreSQL to tell me what the > current DB's table structure is via simple SQL queries executed remotely. You can use the INFORMATION_SCHEMA (which is standard SQL), but in psql the simplest way is: \d+ Show all tables, with descriptions \d+ table Show the definition of the named table (or view), with column descriptions \? Describe briefly all the \ commands in psql The capabilities of psql are fully described in the manual and in psql's man page. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
I wrote: > But alas, two of the most useful commands appear to be missing: > > SHOW TABLES > DESCRIBE table > > Inasmuch as my prototyping DB is on my NetBSD server at home while my > data waiting to be 'based is at work...such that I have to connect via > Perl/DBI and XML-RPC (not psql, alas). Sorry to be over-verbose. The stress is on remote access via Perl/DBI and XML-RPC. Those are my ONLY available routes. I do NOT have psql at work...which is the problem. I have to submit ordinary SQL queries. In MySQL they had such queries, namely the two stated above. Nor can I SSh or any other tunneling protocol so as to even talk to psql on the NetBSD box from behind the corporate firewall. All I have to work with are ports 80 and 443. So I wrote an XML-RPC server/client pair to talk to my box through the firewall, via port 443 (SSL) on my Apache server and making use of Perl DBI. But it limits me to ordinary SQL queries. And for that there are no nifty \dt or any such like. On MySQL they had such ordinary SQL queries. Via any CGI script I could do 'SHOW TABLES' (the same as 'SELECT * FROM foobar' and cousins) and it would just work. Are there no such exploratory SQL queries for PostgreSQL that I can use without having psql? I even went to the source code for psql, as per the FAQ but those were so complicated as to be no use at all over Perl/DBI. Thanks again, Gan Starling Kalamazoo MI USQA
On Oct 17, 2005, at 8:31 , Gan Uesli Starling wrote: > I wrote: > >> But alas, two of the most useful commands appear to be missing: >> SHOW TABLES >> DESCRIBE table >> Inasmuch as my prototyping DB is on my NetBSD server at home while >> my data waiting to be 'based is at work...such that I have to >> connect via Perl/DBI and XML-RPC (not psql, alas). >> > > Sorry to be over-verbose. The stress is on remote access via Perl/DBI > and XML-RPC. Those are my ONLY available routes. I do NOT have psql at > work...which is the problem. > > I have to submit ordinary SQL queries. In MySQL they had such queries, > namely the two stated above. If you read Oliver's response again, you'll see that he gave you a method to do this, namely, using the SQL-standard Information Schema. For more information on using the Information Schema in PostgreSQL, see http://www.postgresql.org/docs/8.0/interactive/information-schema.html An advantage of using the Information Schema is that it is SQL- standard, and should be portable to databases that support this aspect of the standard, rather than vendor-specific commands such as DESCRIBE and SHOW. Hope this helps. Michael Glaesemann grzm myrealbox com
On Mon, 2005-10-17 at 08:56 +0900, Michael Glaesemann wrote: > On Oct 17, 2005, at 8:31 , Gan Uesli Starling wrote: ... > > Sorry to be over-verbose. The stress is on remote access via Perl/DBI > > and XML-RPC. Those are my ONLY available routes. I do NOT have psql at > > work...which is the problem. > > > > I have to submit ordinary SQL queries. In MySQL they had such queries, > > namely the two stated above. > > If you read Oliver's response again, you'll see that he gave you a > method to do this, namely, using the SQL-standard Information Schema. > For more information on using the Information Schema in PostgreSQL, see > http://www.postgresql.org/docs/8.0/interactive/information-schema.html In addition, you can get exactly the information returned by \d by entering the queries that psql submits. To find out what those are, start psql with the -E option. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html