PostgreSQL Tutorials

PostgreSQL provides a well-structured learning path for relational database management. Our tutorials cover SQL queries, database design, and optimization techniques, suitable for both beginners and intermediate developers. With free labs and practical examples, you'll gain hands-on experience in database operations. Our interactive database playground allows you to practice SQL queries and see instant results.

PostgreSQL Database Maintenance

PostgreSQL Database Maintenance

In this lab, we will explore essential PostgreSQL database maintenance tasks. The goal is to understand how to monitor database object sizes, perform routine maintenance, and troubleshoot potential issues. We will run ANALYZE and VACUUM, list active connections, and check server logs.

PostgreSQL Relationships and Joins

PostgreSQL Relationships and Joins

In this lab, you will explore PostgreSQL relationships and joins. You'll create tables with foreign key constraints, insert data ensuring referential integrity, query data using INNER JOIN, and compare LEFT, RIGHT, and FULL OUTER JOIN results to understand data relationships.

Using the PostGIS Extension in PostgreSQL

Using the PostGIS Extension in PostgreSQL

In this lab, you will learn how to use the PostGIS extension to add spatial capabilities to a PostgreSQL database. You will create a dedicated spatial database, enable the PostGIS extension, create a table with geographic data, and perform spatial queries to analyze the data.

PostgreSQL PgBouncer Connection Pooling

PostgreSQL PgBouncer Connection Pooling

In this lab, you will learn how to configure and use PgBouncer for connection pooling with PostgreSQL. You'll configure PgBouncer, connect through it, simulate multiple client connections, and monitor pooling performance. Improve database performance with efficient connection management.

PostgreSQL JSON/JSONB Advanced Operations

PostgreSQL JSON/JSONB Advanced Operations

In this lab, you will explore advanced operations on PostgreSQL JSON/JSONB data types. The lab focuses on enhancing query performance and data manipulation within JSONB columns. Learn to index JSONB fields, query nested structures, update elements, and aggregate JSON data.

PostgreSQL Index Optimization

PostgreSQL Index Optimization

In this lab, you will learn how to optimize PostgreSQL database performance through indexing. You'll create a sample table, analyze query plans, build multi-column indexes, and remove unused indexes. This hands-on experience provides practical skills in PostgreSQL index management.

PostgreSQL Full-Text Search

PostgreSQL Full-Text Search

In this lab, you will learn how to implement full-text search in PostgreSQL. You'll create a table, populate it with data, add a tsvector column for searching, create a GIN index to optimize queries, perform various types of text searches, and set up a trigger to automatically keep the search data up-to-date.

PostgreSQL Event Trigger Setup

PostgreSQL Event Trigger Setup

In this lab, you will learn how to set up a PostgreSQL event trigger. The goal is to create a function that logs database events, specifically table creation, to a dedicated log table. The lab guides you through creating an event trigger function, setting up a DDL event trigger, testing the trigger, and dropping it.

PostgreSQL Error Handling

PostgreSQL Error Handling

In this lab, you will explore error handling techniques in PostgreSQL using PL/pgSQL. The lab focuses on raising notices within functions, catching exceptions, logging errors to a table, and testing error handling logic.

PostgreSQL Date and Time Operations

PostgreSQL Date and Time Operations

In this lab, you will explore PostgreSQL's date and time operations, focusing on handling time zones and performing calculations. You'll create a table, insert timestamps with time zone information, use EXTRACT and INTERVAL functions, query data across time zones, and compute time differences.

PostgreSQL Backup and Restore

PostgreSQL Backup and Restore

In this lab, you will learn how to back up and restore PostgreSQL databases. The lab covers essential techniques for safeguarding your data, including exporting a database using `pg_dump`, restoring a database using `psql`, and backing up specific tables.

PostgreSQL Advanced Query Writing

PostgreSQL Advanced Query Writing

In this lab, you will enhance your PostgreSQL query writing skills by exploring advanced techniques. You'll learn subqueries, CTEs, window functions like ROW_NUMBER, and GROUP BY/HAVING clauses to extract meaningful insights from your datasets.

PostgreSQL Advanced Data Types

PostgreSQL Advanced Data Types

In this lab, you will explore PostgreSQL's advanced data types, focusing on JSON/JSONB, arrays, and UUIDs. You'll learn how to store, query, and manipulate data within these types. The lab covers storing and querying JSON/JSONB, creating array columns, and using UUIDs as identifiers.

PostgreSQL Table Creation and Data Types

PostgreSQL Table Creation and Data Types

In this lab, we will explore PostgreSQL table creation and data types. We'll connect to PostgreSQL using `psql`, create tables with primary keys, and add constraints like `NOT NULL` and `UNIQUE`. We will then inspect the table structure and insert data to demonstrate data types.

Installation and Initial Setup of PostgreSQL

Installation and Initial Setup of PostgreSQL

In this lab, we will install and perform the initial setup of PostgreSQL on the LabEx VM. The goal is to get PostgreSQL up and running and verify its basic functionality. We will explore default databases and check server status and basic configuration.

Database Management Basics with PostgreSQL

Database Management Basics with PostgreSQL

In this lab, you will learn the fundamentals of database management using PostgreSQL. The lab covers creating, listing, connecting to, and dropping databases. Explore basic database administration tasks and view database metadata in PostgreSQL.

Data Filtering and Simple Queries in PostgreSQL

Data Filtering and Simple Queries in PostgreSQL

In this lab, you will learn how to perform data filtering and simple queries in PostgreSQL. You'll use WHERE clause to filter data, LIKE for pattern matching, ORDER BY for sorting, and LIMIT and OFFSET to control result set size. Connect to a PostgreSQL database and create a sample table.

Basic Data Operations in PostgreSQL

Basic Data Operations in PostgreSQL

In this lab, we will explore basic data operations in PostgreSQL. We'll learn to insert data, query with SELECT, update records, and delete records within a PostgreSQL database using SQL commands. This provides a hands-on introduction to essential data management techniques.

Prev
1
2
Next