Display or show tables in a PostgreSQL/PgSQL database | PostgreSQL PgSQL | Tech-Recipes

Display or show tables in a PostgreSQL/PgSQL database

Contributed by Rex

December 16, 2003

Tagged: PostgreSQL PgSQL

From the PostgreSQL psql monitor, use the following command to view the tables in the current database:

\dt

Previous recipe | Next recipe |

Viewing 3 Comments

none 3 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

Is there any equivalent form for this in SQL?

reply edit reblog flag

Anonymous 3 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

This works for me:

select pg_class.relname, pg_attribute.attname, pg_type.typname from pg_class, pg_attribute, pg_type where pg_class.relname = 'YOURTABLENAME' and pg_class.oid = pg_attribute.attrelid and pg_type.oid = pg_attribute.atttypid having attnum > 0

Ivan

reply edit reblog flag

Anonymous 3 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

For those coming from MySQL:
SHOW TABLES = d
SHOW DATABASES = l
SHOW COLUMNS = d table

However the * commands only work in psql and not via other interfaces, such as queries via PHP. Similar data can be retrieved with the following SQL commands:

So in the psql console type
l to see the databases,
d ....

SHOW TABLES (d) = SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

SHOW DATABASES (l) = SELECT datname FROM pg_database;

SHOW COLUMNS FROM table (d table) = SELECT column_name FROM information_schema.columns WHERE table_name ='table';

Check the user comment on this postgre doc. page
http://www.postgresql.org/docs/8.0/interactive/...

<ul id="quote"><h6>none wrote:</h6>Is there any equivalent form for this in SQL?</ul>

reply edit reblog flag

blog comments powered by Disqus