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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics