Re: Switching from MySQL -- Missing DESCRIBE table, SHOW

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(at)lfix(dot)co(dot)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(at)lfix(dot)co(dot)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