PostgreSQL SHOW TABLES Statement

PostgreSQL SHOW TABLES Statement

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.

---

Syntax

\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.

---

Example PostgreSQL SHOW TABLES Statement Queries

Let's look at some examples of PostgreSQL SHOW TABLES statement queries:

1. Basic SHOW TABLES Example

\dt

This command lists all the tables in the current database. It displays the schema name, table name, type, and owner.

2. SHOW TABLES with a Pattern

\dt employee*

This command lists all the tables in the current database that start with employee. The pattern can include wildcard characters.

3. SHOW TABLES in a Specific Schema

\dt public.*

This command lists all the tables in the public schema of the current database.

---

Full Example

Let's go through a complete example that includes creating tables, inserting data, and then listing the tables.

Step 1: Creating 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.

Step 2: Inserting Data into the Tables

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.

Step 3: Listing the 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.

Conclusion

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.