72

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

Improve this question
1
  • 1
    There are some cross-DBMS tools that will do exactly that for you. Check out the Postgres Wiki: wiki.postgresql.org/wiki/… Commented Sep 3, 2014 at 11:01

2 Answers 2

Reset to default
148

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

by the way are there proper type on sql like $this->db->query("DESCRIBE accounts"); something like this on a query version it is okay on the command but rather when you code on it.
@user560756 That makes absolutely no sense, but if you mean "how do I show the table structure from Perl DBI or some other client" the answer is "use the information_schema", which is the standard way. See information_schema.tables, information_schema.columns, etc. This works in MySQL, PostgreSQL, MS-SQL, etc etc etc.
I also want to add that it should be better "tablename" or "Tablename" because if your table has uppercase letters it will not find it otherwise. Also the error will be: Did not find any relation named "Tablename" - as you typed it, preserving the case. Thus very hard to figure out what went wrong.
55

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');
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.