w3resource

How to Show Tables in PostgreSQL


Show Tables in PostgreSQL with Examples

In PostgreSQL, there are several ways to display all tables within a database. Here’s a comprehensive guide on how to list tables using SQL commands and the psql command-line interface.

1. Using \dt Command in psql

The \dt command is used in PostgreSQL's interactive terminal, psql, to display tables within the connected database.

Syntax:

\dt

Example Code:

-- Connect to the psql command line
\c database_name 

-- List all tables
\dt

Explanation:

  • \c database_name: Connect to the desired database (replace database_name with the actual name of the database).
  • \dt: Lists all tables within the connected database, including details like schema, name, type, and owner.

2. Using SQL Query on information_schema

PostgreSQL stores metadata about database objects in the information_schema tables. You can use a SQL query to get a list of all tables.

Syntax:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

Example Code:

-- Retrieve table names from the 'public' schema
SELECT table_name              -- Select the 'table_name' column
FROM information_schema.tables -- From the 'tables' information schema
WHERE table_schema = 'public'; -- Where schema is 'public'

Explanation:

  • table_name: Specifies the column that holds the names of tables.
  • information_schema.tables: A schema containing metadata about tables in the database.
  • WHERE table_schema = 'public': Filters to show only tables within the public schema (change this to target different schemas if necessary).

3. Using pg_catalog.pg_tables

Another way to list tables is by querying the pg_catalog.pg_tables system catalog.

Syntax:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

Example Code:

-- Retrieve table names from 'public' schema
SELECT tablename               -- Select the 'tablename' column
FROM pg_catalog.pg_tables      -- From 'pg_tables' catalog
WHERE schemaname = 'public';   -- Where schema is 'public'

Explanation:

  • tablename: Column containing table names.
  • pg_catalog.pg_tables: System catalog containing information about tables.
  • WHERE schemaname = 'public': Restricts the output to the public schema.

All PostgreSQL Questions, Answers, and Code Snippets Collection.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/how-to-show-tables-in-postgresql.php