psql and pgAdmin

PostgreSQL ships with two primary tools for interacting with the database: psql, a powerful command-line interface, and pgAdmin, a full-featured web-based graphical UI. Knowing both makes you more productive.

The psql Command-Line Interface

psql is a terminal-based front-end to PostgreSQL. You can type SQL queries interactively, run script files, and use a rich set of backslash meta-commands.

# Connect to a specific database as a specific user
psql -h localhost -U postgres -d mydb

# Run a SQL file directly
psql -U postgres -d mydb -f schema.sql

# Execute a single query from the shell
psql -U postgres -d mydb -c "SELECT version();"

Once inside psql you will see a prompt like mydb=# (superuser) or mydb=> (regular user).

Essential psql Meta-Commands

Meta-commands begin with a backslash and are not sent to the server as SQL.

Command	Description
\l	List all databases
\c dbname	Connect to a different database
\dt	List tables in the current schema
\d tablename	Describe a table's columns and types
\du	List roles (users)
\i file.sql	Execute commands from a file
\e	Open the last query in your text editor
\timing	Toggle query execution time display
\q	Quit psql

Creating and Switching Databases

-- Create a new database
CREATE DATABASE myapp;

-- Switch to it inside psql
c myapp

pgAdmin 4

pgAdmin is a graphical administration and development tool for PostgreSQL. You can download it from pgadmin.org or use the version bundled with the Windows installer.

Key features include:

• Query Tool — a rich SQL editor with syntax highlighting and auto-complete
• Object Browser — navigate databases, schemas, tables, views, and functions in a tree
• Dashboard — view active sessions, transactions per second, and server metrics
• ER Diagrams — visualise relationships between tables
• Backup/Restore — manage database dumps through a GUI

To connect pgAdmin to your local server, right-click Servers in the browser panel, choose Register > Server, and fill in the connection details (host: localhost, port: 5432, username: postgres, password: the one you set during installation).

Choosing Between psql and pgAdmin