The PostgreSQL SHOW TABLES
statement is used to list all the tables in a specific database. This statement is essential for retrieving an overview of the tables present in the current database.
\dt [pattern]
The \dt
command is used in the psql command-line interface to list tables. The optional pattern
can be used to filter the table names.
Let's look at some examples of PostgreSQL SHOW TABLES
statement queries:
\dt
This command lists all the tables in the current database. It displays the schema name, table name, type, and owner.
\dt employee*
This command lists all the tables in the current database that start with employee
. The pattern can include wildcard characters.
\dt public.*
This command lists all the tables in the public
schema of the current database.
Let's go through a complete example that includes creating tables, inserting data, and then listing the tables.
This step involves creating new tables named employees
and departments
to store employee and department data.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
In this example, we create two tables: employees
and departments
.
This step involves inserting some sample data into the employees
and departments
tables.
INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');
INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');
INSERT INTO departments (department_name)
VALUES ('Human Resources');
INSERT INTO departments (department_name)
VALUES ('Finance');
Here, we insert data into the employees
and departments
tables.
This step involves listing all the tables in the current database.
\dt
This command displays all the tables in the current database, showing their schema, name, type, and owner.
The PostgreSQL SHOW TABLES
statement is a fundamental tool for listing tables in a database. Understanding how to use the \dt
command and its syntax is essential for effective database management and navigation in PostgreSQL.