PostgreSQL Tutorial

This PostgreSQL tutorial provides a structured path for learning how to install PostgreSQL, work with databases and tables, write SQL queries, manage JSON data, and perform common administration tasks with pgAdmin 4 and the psql command-line tool.

Start with installation and database basics, then move through table operations, query clauses, constraints, views, triggers, stored procedures, backup, restore, and monitoring. Each linked tutorial focuses on a specific PostgreSQL task so that you can learn one operation at a time.

Recommended PostgreSQL Learning Path

  1. Install PostgreSQL and connect through psql or pgAdmin 4.
  2. Create a database and understand how PostgreSQL organizes schemas, tables, and other objects.
  3. Create tables with suitable data types, primary keys, foreign keys, defaults, and indexes.
  4. Practice INSERT, SELECT, UPDATE, and DELETE statements.
  5. Learn filtering, sorting, grouping, joins, subqueries, transactions, and data integrity rules.
  6. Continue with views, triggers, stored procedures, JSONB, backup, restore, and performance maintenance.

PostgreSQL Practice Database Example

The following small table can be used while studying the query tutorials on this page. Run it in psql or the pgAdmin 4 Query Tool.

</>
Copy
CREATE TABLE employees (
    employee_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    joined_on DATE DEFAULT CURRENT_DATE
);

INSERT INTO employees (employee_name, department, salary)
VALUES
    ('Asha', 'Engineering', 72000.00),
    ('Ravi', 'Support', 48000.00),
    ('Meera', 'Engineering', 81000.00);

SELECT employee_id, employee_name, department, salary
FROM employees
ORDER BY employee_id;

Install PostgreSQL and Connect with psql

Begin by installing PostgreSQL on your operating system. After installation, learn how to connect to a server, choose a database, enter SQL commands, and inspect objects from the PostgreSQL Shell.

  1. PostgreSQL – Install on Mac
  2. PostgreSQL – Install on Windows
  3. PostgreSQL – psql Shell

Useful psql Commands for Beginners

</>
Copy
psql -U postgres
\l
\c database_name
\dt
\d table_name
\q

Commands beginning with a backslash are psql meta-commands. SQL statements, in contrast, normally end with a semicolon.

Create, Select, List, Back Up, and Drop PostgreSQL Databases

A PostgreSQL database contains schemas and database objects such as tables, views, sequences, functions, and procedures. These tutorials cover the main database-level operations and the distinction between connecting to a server and selecting a database.

  1. PostgreSQL – Create Database
  2. PostgreSQL – Select Database
  3. PostgreSQL – List Databases
  4. PostgreSQL – Delete or Drop Database
  5. PostgreSQL – Backup and Restore Databases

Before dropping a database, confirm that you are connected to a different database and that no required data remains only in the target database.

PostgreSQL Table Creation and Row Operations

A PostgreSQL table stores records as rows and attributes as columns. The tutorials below cover table creation, data insertion, selection, updates, deletion, column changes, and relational constraints.

  1. PostgreSQL – CREATE TABLE
  2. PostgreSQL – INSERT INTO Table
  3. PostgreSQL – Insert Multiple Rows into Table
  4. PostgreSQL – Insert Row if Not Exists in Table
  5. PostgreSQL – Insert JSON into Table
  6. PostgreSQL – Insert into Select from Another Table
  7. PostgreSQL – SELECT FROM Table
  8. PostgreSQL – Select Top 10 Rows from Table
  9. PostgreSQL – Select Query with Where Clause
  10. PostgreSQL – Select Distinct on One Column
  11. PostgreSQL – Select Distinct on Multiple Columns
  12. PostgreSQL – Select Count
  13. PostgreSQL – UPDATE Table
  14. PostgreSQL – DELETE Rows from Table
  15. PostgreSQL – Add a Column to Table
  16. PostgreSQL – Delete a Column from Table
  17. PostgreSQL – Make a Column PRIMARY KEY
  18. PostgreSQL – Foreign Key Constraints

Safe UPDATE and DELETE Practice

Use a WHERE condition when changing or removing selected rows. A statement without an appropriate condition can affect every row in the table. For important changes, first run a matching SELECT query and use a transaction when practical.

</>
Copy
BEGIN;

SELECT *
FROM employees
WHERE department = 'Support';

UPDATE employees
SET salary = salary + 2500
WHERE department = 'Support';

COMMIT;

PostgreSQL WHERE, LIMIT, ORDER BY, GROUP BY, and HAVING Clauses

Query clauses control which rows are returned, how results are sorted, how many rows are displayed, and how records are grouped for aggregate calculations.

  1. PostgreSQL – WHERE Clause
  2. PostgreSQL – LIMIT Clause
  3. PostgreSQL – ORDER BY Clause
  4. PostgreSQL – GROUP BY Clause
  5. PostgreSQL – HAVING Clause

WHERE filters rows before grouping, while HAVING filters grouped results after aggregate calculations.

</>
Copy
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
WHERE salary IS NOT NULL
GROUP BY department
HAVING COUNT(*) >= 1
ORDER BY average_salary DESC
LIMIT 10;

PostgreSQL CREATE TABLE Patterns for Keys, Dates, Defaults, and Indexes

These focused tutorials show common table definitions, including conditional creation, generated identifiers, primary and foreign keys, date columns, default timestamps, indexes, and tables created from query results.

  1. PostgreSQL – Create Table if Not Exists
  2. PostgreSQL – Create Table with Autoincrement id
  3. PostgreSQL – Create Table with Primary Key
  4. PostgreSQL – Create Table with Primary Key Autoincrement
  5. PostgreSQL – Create Table with Foreign Key
  6. PostgreSQL – Create Table with Multiple Foreign Key
  7. PostgreSQL – Create Table with Date Column
  8. PostgreSQL – Create Table with Default Timestamp
  9. PostgreSQL – Create Table with Index
  10. PostgreSQL – Create Table from Another Table

Choose column data types and constraints before loading production data. A primary key identifies each row, while a foreign key enforces a relationship with a key in another table.

PostgreSQL JSON and JSONB Tutorials

PostgreSQL can store structured JSON documents and query values inside them. The json type preserves the supplied JSON text, while jsonb stores a decomposed binary representation that supports efficient processing and indexing for many workloads.

  1. PostgreSQL – JSON Datatype
  2. PostgreSQL – JSONB Datatype
  3. PostgreSQL – JSON vs JSONB
  4. PostgreSQL – Select JSON column as Text
  5. PostgreSQL – JSON Functions
  6. PostgreSQL – JSON Array to Rows
</>
Copy
CREATE TABLE orders (
    order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_details JSONB NOT NULL
);

INSERT INTO orders (order_details)
VALUES ('{"customer":"Asha","items":[{"sku":"P100","quantity":2}]}');

SELECT order_details->>'customer' AS customer_name
FROM orders;

PostgreSQL Views, Triggers, Procedures, Partitioning, Arrays, and Search

After learning core SQL and table design, continue with reusable query objects, server-side logic, data partitioning, array values, and text-search features.

  1. PostgreSQL – Views
  2. PostgreSQL – Triggers
  3. PostgreSQL – Stored Procedures
  4. PostgreSQL – Partitioning Tables
  5. PostgreSQL – JSON and JSONB Data Types
  6. PostgreSQL – Working with Arrays
  7. PostgreSQL – Full-Text Search

Additional PostgreSQL Topics to Study

  • Joins, subqueries, common table expressions, and set operations
  • Transactions, savepoints, isolation levels, and locking
  • Roles, privileges, schemas, and object ownership
  • Indexes and query-plan inspection with EXPLAIN
  • Routine maintenance with VACUUM, ANALYZE, and REINDEX

pgAdmin 4 Tutorials

pgAdmin 4 provides a graphical interface for PostgreSQL administration. It can be used to register servers, browse database objects, run SQL, manage tables and roles, perform backups and restores, inspect activity, and complete maintenance tasks.

Manage PostgreSQL Databases in pgAdmin 4

  1. How to create a database in pgAdmin 4
  2. How to delete a database in pgAdmin 4
  3. How to rename a database in pgAdmin 4
  4. How to connect to a database server in pgAdmin 4
  5. How to disconnect from a database server in pgAdmin 4
  6. How to back up a database in pgAdmin 4
  7. How to restore a database in pgAdmin 4
  8. How to view database properties in pgAdmin 4
  9. How to set database connection limits in pgAdmin 4

Create and Modify PostgreSQL Tables in pgAdmin 4

  1. How to create a table in pgAdmin 4
  2. How to delete a table in pgAdmin 4
  3. How to rename a table in pgAdmin 4
  4. How to modify table structure in pgAdmin 4
  5. How to add new columns to a table in pgAdmin 4
  6. How to delete columns from a table in pgAdmin 4
  7. How to create constraints on a table in pgAdmin 4
  8. How to export a table in pgAdmin 4
  9. How to import data into a table in pgAdmin 4
  10. How to truncate a table in pgAdmin 4
  11. How to analyze table dependencies in pgAdmin 4

Run SQL Queries and Scripts in pgAdmin 4

  1. How to write SQL queries in the query tool in pgAdmin 4
  2. How to execute a query in pgAdmin 4
  3. How to save SQL queries in pgAdmin 4
  4. How to format SQL queries in pgAdmin 4
  5. How to debug a query in pgAdmin 4
  6. How to use the Query History in pgAdmin 4

Manage PostgreSQL Schemas and Privileges in pgAdmin 4

  1. How to create a schema in pgAdmin 4
  2. How to delete a schema in pgAdmin 4
  3. How to rename a schema in pgAdmin 4
  4. How to manage schema privileges in pgAdmin 4

Manage PostgreSQL Users and Roles in pgAdmin 4

  1. How to create a user in pgAdmin 4
  2. How to delete a user in pgAdmin 4
  3. How to modify user roles in pgAdmin 4
  4. How to assign privileges to a user in pgAdmin 4
  5. How to revoke privileges from a user in pgAdmin 4

Create and Maintain PostgreSQL Indexes in pgAdmin 4

  1. How to create an index in pgAdmin 4
  2. How to delete an index in pgAdmin 4
  3. How to view index properties in pgAdmin 4
  4. How to rebuild an index in pgAdmin 4

Manage PostgreSQL Views and Functions in pgAdmin 4

  1. How to create a view in pgAdmin 4
  2. How to delete a view in pgAdmin 4
  3. How to modify a view in pgAdmin 4
  4. How to create a function in pgAdmin 4
  5. How to delete a function in pgAdmin 4
  6. How to debug a function in pgAdmin 4

Manage PostgreSQL Triggers in pgAdmin 4

  1. How to create a trigger in pgAdmin 4
  2. How to delete a trigger in pgAdmin 4
  3. How to enable or disable triggers in pgAdmin 4
  4. How to view trigger properties in pgAdmin 4

Back Up and Restore PostgreSQL with pgAdmin 4

  1. How to back up a database in pgAdmin 4
  2. How to restore a database in pgAdmin 4
  3. How to schedule backups in pgAdmin 4
  4. How to use pg_dump with pgAdmin 4

A backup is only useful when it can be restored. Test restore procedures in a suitable non-production environment and record the server version, backup format, ownership requirements, and required extensions.

Monitor and Maintain PostgreSQL in pgAdmin 4

  1. How to analyze a database in pgAdmin 4
  2. How to vacuum a database in pgAdmin 4
  3. How to reindex a database in pgAdmin 4
  4. How to monitor database activity in pgAdmin 4
  5. How to view server logs in pgAdmin 4
  6. How to manage replication in pgAdmin 4

Import and Export PostgreSQL Data in pgAdmin 4

  1. How to import data from a CSV file in pgAdmin 4
  2. How to export data to a CSV file in pgAdmin 4
  3. How to export a database schema in pgAdmin 4
  4. How to export a table to JSON in pgAdmin 4

Configure pgAdmin 4 Servers, Dashboards, and Interface Options

  1. How to use dashboards in pgAdmin 4
  2. How to customize the pgAdmin 4 interface
  3. How to configure notifications in pgAdmin 4
  4. How to create a server group in pgAdmin 4
  5. How to configure a server connection in pgAdmin 4
  6. How to reset the master password in pgAdmin 4
  7. How to use the built-in debugger in pgAdmin 4
  8. How to enable auto-refresh in pgAdmin 4
  9. How to compare schemas in pgAdmin 4
  10. How to generate ER diagrams in pgAdmin 4

PostgreSQL Tutorial FAQs

Should I learn PostgreSQL with psql or pgAdmin 4?

Learn both. psql helps you understand commands, scripts, and server responses directly, while pgAdmin 4 is useful for browsing objects and completing administrative tasks through a graphical interface.

What should I learn before PostgreSQL joins and subqueries?

Be comfortable with tables, keys, data types, INSERT, SELECT, WHERE, ORDER BY, aggregate functions, and GROUP BY. Primary-key and foreign-key concepts are especially useful before learning joins.

What is the difference between a PostgreSQL database and schema?

A database is a top-level container to which a client connects. A schema is a namespace inside a database that groups objects such as tables, views, and functions.

When should I use JSONB in PostgreSQL?

Use JSONB when part of the data is naturally document-shaped and you need to query or index fields within that document. Keep frequently filtered, joined, or constrained attributes in regular relational columns when that structure is clearer.

How can I avoid accidental PostgreSQL data changes?

Check the target rows with SELECT, use precise WHERE conditions, work inside transactions for sensitive changes, restrict privileges, and maintain tested backups.

PostgreSQL Tutorial Editorial QA Checklist

  • Confirm every SQL example uses PostgreSQL-compatible syntax and terminates statements correctly.
  • Verify destructive examples such as DROP, DELETE, and TRUNCATE include an appropriate safety note.
  • Check that database, schema, table, role, and server terminology is used consistently.
  • Test internal PostgreSQL and pgAdmin 4 links and retain unlinked items only when they represent planned tutorials.
  • Validate code examples in both psql and the pgAdmin 4 Query Tool where the tutorial claims both are supported.
  • Review examples for clear object names, realistic constraints, and no embedded passwords or production credentials.