PostgreSQL psql display table commands

As developers, it is quite common to deal with SQL databases and PostgresSQL(or Postgres) is one of the most popular databases. This article is to just showcase the most commonly used commands to access and operate on databases and display tables within a PostgreSQL instance. We will be using psql, a terminal-based front-end to PostgreSQL that allows to dispatch query interactively to PostgreSQL, and see the query results

I normally run a containerized instance of PostgreSQL for development purpose. Visit the [official docker Postgres repo](https://hub.docker.com/_/postgres) to get quick started on a Postgres instance.

Now that you have a PostgreSQL instance, exec into the container using the simple command, for example

docker exec -it <container-id> /bin/sh

# Connect to database

To connect to database, use the psql command

psql -d <database_name> -U <user_name> -W

It will prompt for password and once provided, it will let you into the psql prompt

psql (13.1)
Type "help" for help.
<database_name> =>

#Show tables

If you were using MySQL or SQLServer you will be quite familiar with SHOW TABLES to list all the available tables. Unfortunately Postgres does not support that and it provides alternatives to list tables. In postgres the command to display tables is \dt

Sample:

# Show more table information

\dt+

Sample:

# Show all tables

pg catalog can be queried for all tables in the database including the internal tables under schemas like ( `information_schema` and pg_catalog)

select * from pg_catalog.pg_tables;

Now we can filter out system schemas to see the ones you defined

select * from pg_catalog.pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

Alternate format

select * from pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

Have fun on your PostgreSQL journey!