How to 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:
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:
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:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4