PostgreSQL Tools — psql & pgAdmin

PostgreSQL gives you two powerful ways to work with your database — psql, a fast and precise command-line tool, and pgAdmin, a full visual interface in your browser. Professional developers use both. psql is the fastest way to run queries and manage databases from the terminal. pgAdmin makes exploring structure, visualising data, and navigating large databases much easier. This lesson gives you a thorough working knowledge of both so you can use whichever fits the moment.

psql — The Command-Line Interface

psql is installed automatically with PostgreSQL. It is a terminal-based client that connects directly to your database and lets you type SQL queries and get results instantly. There is no loading time, no GUI overhead — just you and the database. Once you are comfortable with psql, you will find it faster than any graphical tool for most everyday tasks.

To connect, open your terminal and use the following format. The -U flag specifies the user, -d specifies the database, and -h specifies the host (use localhost for your own machine).

-- Basic connection — connects as postgres user to the default database
psql -U postgres

-- Connect to a specific database directly
psql -U postgres -d mystore

-- Connect to a remote server
psql -U postgres -h 192.168.1.100 -d mystore

-- Connect with port specified (useful if not using default 5432)
psql -U postgres -h localhost -p 5432 -d mystore

Navigating Inside psql

Once you are connected, psql gives you a prompt where you type SQL or special backslash commands. SQL commands end with a semicolon — psql waits for the semicolon before executing. Backslash commands execute immediately without a semicolon.

-- List all databases on this server
\l

-- Connect to a different database
\c dataplexa_store

-- List all tables in the current database
\dt

-- Show the structure of a specific table
\d customers

-- List all users and their roles
\du

-- Show current connection info
\conninfo

Running SQL in psql

Any SQL you type at the psql prompt is sent to PostgreSQL when you press Enter after the semicolon. Multi-line queries work naturally — psql keeps waiting until it sees the semicolon. This makes writing complex queries comfortable even in the terminal.

-- Single line query
SELECT * FROM customers;

-- Multi-line query — psql waits for the semicolon
SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York'
ORDER BY last_name;

-- Run a .sql file directly from psql
\i /path/to/dataplexa-store-dataset.sql

Useful psql Settings and Tricks

psql has several settings that make your experience much more comfortable. These are especially helpful when working with wide result tables or timing your queries.

-- Turn on expanded display — shows each column on its own line
-- Very useful for tables with many columns
\x

-- Toggle query execution timing on/off
\timing

-- Set the number of rows to display per page (0 = no paging)
\pset pager off

-- Clear the screen
\! clear    -- on Mac/Linux
\! cls      -- on Windows

-- Show history of commands you have run
\s

-- Save query output to a file
\o output.txt
SELECT * FROM customers;
\o     -- stop saving to file

Full psql Command Reference

pgAdmin — The Visual Interface

pgAdmin is a browser-based graphical tool that gives you a full visual view of your PostgreSQL server. It is perfect for exploring database structure, browsing table data, managing users, and running queries with a proper editor that has syntax highlighting and results displayed in a clean grid.

When you open pgAdmin for the first time, set a master password to secure it. Then you will see the main dashboard with a server tree on the left side. Everything you need is accessible from this tree — databases, schemas, tables, views, functions, and users are all visible as expandable nodes.

Connecting to Your Server in pgAdmin

If PostgreSQL is not already registered in pgAdmin, right-click Servers in the left panel and choose Register → Server. In the dialog that opens, give your connection a name on the General tab — for example "Local PostgreSQL". Switch to the Connection tab and fill in these details: Host as localhost, Port as 5432, Maintenance database as postgres, Username as postgres, and your password. Click Save and you are connected.

Running Queries in pgAdmin

In the left tree, expand your server, then expand Databases, then click on the database you want to work with. Go to the top menu and click Tools → Query Tool. A query editor opens. Type your SQL in the top panel and click the Play button (or press F5) to run it. Results appear in the Data Output panel below.

-- Type this in the pgAdmin Query Tool and press F5 to run
SELECT first_name, last_name, city
FROM customers
ORDER BY city;

Key pgAdmin Features Worth Knowing

Object browser — the left-side tree lets you click through every database, schema, table, index, and view. Right-clicking any object gives you options to view data, check properties, create new objects, or drop them.

Table data viewer — right-click any table and choose View/Edit Data → All Rows to see its full contents in a spreadsheet-style grid. You can even edit individual cells directly in this view.

EXPLAIN Visualizer — run any query with Explain (F7) or Explain Analyze (Shift+F7) and pgAdmin displays the query execution plan as a visual diagram with cost estimates. This is far easier to read than the plain text EXPLAIN output in psql.

Dashboard — click on your server name in the tree to see the pgAdmin Dashboard, which shows live graphs of active connections, transactions per second, and block I/O. Useful for monitoring a running database.

Backup and Restore — right-click any database and choose Backup or Restore to create and load .sql or .dump files without writing any commands. You will use this when loading the course dataset.

psql vs pgAdmin — When to Use Each

Next up: Creating your first real database and understanding schemas.