Show table structure and list of tables in PostgreSQL

Question

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 initial 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"?

Answer 1

SHOW TABLES and DESCRIBE TABLE are MySQL-specific admin commands, and nothing to do with standard SQL.

You want the:

\d

and

\d+ tablename

commands from psql.

These are implemented client-side. I find this odd myself, and would love to move them server-side as built-in SQL commands one day.

Other clients provide other ways to browse the structure - for example, PgAdmin-III.

If you want a portable way to get table structure in code, you should use the information_schema views, which are SQL-standard. See information_schema. They're available in MySQL, PostgreSQL, Ms-SQL, and most other DBs. The downside is that they're fiddlier to use, so they aren't convenient for quick access when you're just browsing a DB structure.

Answer 2

As per the Documentation

SELECT
    table_schema || '.' || table_name as show_tables
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

for more convenience make it as a function

create or replace function show_tables() returns SETOF text as $$
SELECT
    table_schema || '.' || table_name as show_tables
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');
$$
language sql; 

So we can get the tables using

select show_tables()

---

For the table description

 select column_name, data_type, character_maximum_length
 from INFORMATION_SCHEMA.COLUMNS where table_name ='table_name';

as a Function

create or replace function describe_table(tbl_name text) returns table(column_name   
varchar, data_type varchar,character_maximum_length int) as $$
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = $1;
$$
language 'sql';

select  *  from describe_table('a_table_name');