While I generally prefer PostgreSQL to MySql, this is one area where MySql did a better job.

There isn't a general purpose DESCRIBE or SHOW TABLES in Pg's sql syntax if you aren't using the command line psql tool. ( try \dt and \d tablename if you are).

You can peruse the source for psql (look at describe.c), or use the -E option to psql to dump it's internal SQL commands it uses for \dt and \d.

For the lazier folks, here's postgres running \dt and \d on a table called fees, along with the SQL commands that \d and \dt use behind the scenes

test=# \dt
********* QUERY *********
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as "Own
+er"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'

ORDER BY "Name"
*************************

         List of relations
      Name       | Type  |  Owner
-----------------+-------+----------
 billing         | table | postgres
 fees            | table | postgres
 flgtatt         | table | postgres
 <I truncated this here>

test=# \d fees
********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='fees'
*************************

********* QUERY *********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, 
+a.atthasde
f, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'fees'
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'fees' AND c.oid = i.indrelid AND i.indexrelid = c2.
+oid
ORDER BY c2.relname
*************************
                 Table "fees"
 Attribute |         Type          | Modifier
-----------+-----------------------+----------
 type      | character varying(10) | not null
 amount    | numeric(8,2)          |
 effdate   | date                  |
Index: fees_pkey

[download]

In reply to Re: DBI - MySQL vs. PgSQL by kschwab
in thread DBI - MySQL vs. PgSQL by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.