Published on November 06, 2024By DeveloperBreeze

To view tables and the number of records in each table in a PostgreSQL database, you can use the following methods.


Method 1: Using SQL Queries in `psql`

  1. Connect to PostgreSQL:

Start by connecting to your database using:

   psql -U your_username -d your_database_name
  1. List Tables:

To list all tables in the current schema, use:

   \dt
  1. View Record Counts for Each Table:

Run the following SQL query to see the list of tables along with their row counts:

   SELECT relname AS table_name,
          n_live_tup AS row_count
   FROM pg_stat_user_tables
   ORDER BY table_name;

This query fetches the table names and live row estimates from PostgreSQL’s statistics catalog, pg_stat_user_tables.


Method 2: Using a Database Client (e.g., pgAdmin)

  1. Open pgAdmin and connect to your PostgreSQL server.
  2. Navigate to the Database: In the left sidebar, expand your database and go to Schemas > Tables.
  3. View Table and Row Count:
  • Right-click on each table, select View/Edit Data > All Rows to see the records.
  • Or, run the above SQL query in the Query Tool to see all tables with row counts.

These methods will help you view tables and get a record count for each in your PostgreSQL database.

Comments

Please log in to leave a comment.