PostgreSQL pgAdmin 4

🖥️ What is pgAdmin 4?

pgAdmin 4 is the leading open-source graphical management tool for PostgreSQL. It provides an intuitive interface for database administration, query execution, and visual data exploration without command-line complexity.

Key Features:

Visual database browser
SQL query editor with syntax highlighting
Table designer and data viewer
Backup and restore tools

pgAdmin 4 Features

🌳

Object Browser

Navigate databases, tables, and schemas visually in a tree structure

✏️

Query Tool

Write and execute SQL queries with auto-completion and formatting

📊

Data Viewer

View, edit, and filter table data in spreadsheet-like interface

💾

Backup/Restore

Create backups and restore databases with simple wizards

🔹 Launching pgAdmin 4

pgAdmin 4 runs as a web application in your browser. After installation, you can access it locally or configure it for remote access to manage databases.

Starting pgAdmin 4:

Windows: Start Menu → pgAdmin 4
macOS: Applications → pgAdmin 4
Linux: Run pgadmin4 command
Browser: Opens at http://localhost:5050 or similar


# Launch pgAdmin 4 from command line (Linux)
pgadmin4

# Or access via browser
# http://127.0.0.1:5050

🔹 Connecting to a Server

Before managing databases, you need to register your PostgreSQL server in pgAdmin. This creates a connection profile that stores your server details for easy access.

Connection Steps:

Right-click "Servers" in the browser tree
Select "Register" → "Server"
General Tab: Enter server name (e.g., "Local PostgreSQL")
Connection Tab: Enter connection details
Click "Save" to connect


Connection Settings:
--------------------
Host: localhost
Port: 5432
Maintenance database: postgres
Username: postgres
Password: [your password]
Save password: ✓ (optional)

🔹 Creating a Database

pgAdmin makes database creation visual and straightforward. You can set all properties through dialog boxes instead of writing SQL commands manually.

Steps to Create Database:

Expand your server in the browser tree
Right-click "Databases"
Select "Create" → "Database"
Enter database name (e.g., "mystore")
Choose owner (usually postgres)
Click "Save"


-- pgAdmin generates this SQL behind the scenes:
CREATE DATABASE mystore
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

🔹 Using the Query Tool

The Query Tool is where you write and execute SQL commands. It provides syntax highlighting, auto-completion, and displays results in an easy-to-read grid format.

Opening Query Tool:

Select your database in the browser
Click "Tools" → "Query Tool" (or press F5)
Write your SQL in the editor
Click ▶ (Execute) or press F5 to run


-- Example: Create and query a table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2)
);

INSERT INTO products (name, price) VALUES
    ('Laptop', 999.99),
    ('Mouse', 29.99),
    ('Keyboard', 79.99);

SELECT * FROM products;

Query Result:

id	name	price
1	Laptop	999.99
2	Mouse	29.99

🔹 Viewing and Editing Data

pgAdmin lets you view and edit table data directly without writing SQL. This spreadsheet-like interface is perfect for quick data updates and exploration.

View/Edit Table Data:

Navigate to your table in the browser tree
Right-click the table name
Select "View/Edit Data" → "All Rows"
Double-click cells to edit values
Click ✓ to save changes

🔸 Filtering Data


-- Use the filter icon in data viewer
-- Or right-click → "View/Edit Data" → "Filtered Rows"

-- Example filter:
price > 50.00

🔹 Creating Tables Visually

Design tables using pgAdmin's graphical interface. Define columns, data types, and constraints through dialog boxes without writing CREATE TABLE statements.

Table Creation Steps:

Expand database → Schemas → public
Right-click "Tables" → "Create" → "Table"
General Tab: Enter table name
Columns Tab: Add columns with data types
Constraints Tab: Add primary keys, foreign keys
Click "Save"


-- pgAdmin generates SQL like this:
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔹 Backup and Restore

Protect your data by creating backups regularly. pgAdmin provides wizards that make backup and restore operations simple and reliable for databases of any size.

🔸 Creating a Backup

Right-click your database
Select "Backup"
Choose filename and location
Select format (Custom recommended)
Click "Backup"

🔸 Restoring a Backup

Create a new empty database
Right-click the database
Select "Restore"
Choose your backup file
Click "Restore"


# pgAdmin uses pg_dump and pg_restore behind the scenes
# Backup command:
pg_dump -U postgres -F c mystore > mystore_backup.dump

# Restore command:
pg_restore -U postgres -d mystore_new mystore_backup.dump

🔹 Useful pgAdmin Tips

Master these shortcuts and features to work more efficiently in pgAdmin. These tips help you navigate faster and perform common tasks with fewer clicks.

Keyboard Shortcuts:

F5: Execute query / Open Query Tool
F7: Format SQL (beautify)
F8: Execute query to CSV file
Ctrl + Space: Auto-complete
Ctrl + Shift + C: Comment/uncomment

Pro Tips:

Use "Explain" button to analyze query performance
Save frequently used queries as "Macros"
Export query results to CSV, JSON, or Excel
Use "ERD Tool" to visualize table relationships
Enable "Auto-commit" for immediate changes