PostgreSQL 101: Listing Tables with Commands and Queries

Understanding the Terms:

• Database: A database is a collection of information organized into a specific structure. In PostgreSQL, data is stored in tables.
• PostgreSQL: PostgreSQL is an open-source relational database management system (RDBMS) used to store and manage data.
• Command: In the context of PostgreSQL, a command is an instruction given to the database through the psql interface to perform an action.

Showing Tables in PostgreSQL:

1. Connecting to the Database:

   • Open a terminal window.
   • Use the psql command followed by the database name to connect. For example, psql my_database would connect to the database named "my_database". You might need to provide additional options like username and password depending on your setup.

2. Viewing Tables:

   There are two main ways to view tables in PostgreSQL:

   • Using the \dt command: This is a shortcut command in psql specifically for viewing tables. Once connected to your database, simply type \dt and press Enter. This will display a list of all tables in the current database.

   • Using the SELECT statement: PostgreSQL provides a way to query information about the database schema, including tables. You can use the following SQL statement:

     SELECT table_name
     FROM information_schema.tables
     WHERE table_type = 'BASE TABLE'
     

     This query retrieves the table_name from the information_schema.tables system table. The WHERE clause filters the results to only include base tables (excluding views or other database objects).

Choosing the Right Method:

• The \dt command is simpler and faster for getting a quick list of tables.
• The SELECT statement offers more flexibility. You can modify it to filter tables based on specific criteria or retrieve additional information about the tables like creation time or owner.

Additional Notes:

• By default, \dt shows all objects in the database, not just tables. You can use \dt+ for a more verbose output that includes additional details about the tables.
• Remember to exit psql when you're finished by typing \q.

Using \dt command:

psql my_database  # Connect to the database named "my_database"

\dt                 # List all tables in the current database

This will display a list of tables, including their names and possibly some additional information depending on your psql configuration.

Using SELECT statement:

psql my_database  # Connect to the database named "my_database"

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

This code snippet will show only the table names from the information_schema.tables system view, excluding views and other database objects.

Remember:

• Replace my_database with your actual database name.
• You can modify the SELECT statement to retrieve other information about the tables. For example:

SELECT table_name, table_type
FROM information_schema.tables;

1. Using the pg_catalog.pg_tables system table:

This method utilizes a built-in PostgreSQL system table named pg_catalog.pg_tables. It stores information about all database objects, including tables. Here's an example:

psql my_database  # Connect to the database named "my_database"

SELECT table_name
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';  -- Modify 'public' for a specific schema

This query retrieves the table_name from pg_catalog.pg_tables. The WHERE clause filters results to include only tables from the public schema (default schema). You can modify the schema name to target a specific one.

Note: This method is functionally similar to using information_schema.tables, but the table names might differ slightly.

1. Using pgAdmin (or other GUI tools):

If you prefer a graphical interface, PostgreSQL offers pgAdmin, a web-based administration tool. You can connect to your database through pgAdmin and navigate the schema tree to view tables. Here's a general outline:

• Install pgAdmin (if not already installed).
• Start pgAdmin and connect to your PostgreSQL server.
• In the navigation pane, expand the server and desired database.
• You'll see a list of schemas and tables within the database.