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

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

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.

PostgreSQL PostGIS Extension

In this lab, you will learn how to install and use the PostGIS extension in a PostgreSQL database. PostGIS enables PostgreSQL to store and query spatial data. You'll create a database, install PostGIS, verify the installation, create and query spatial data, test functionality, and remove the extension.

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

In this lab, we 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

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

In this lab, you will learn how to implement full-text search in PostgreSQL using `TSVECTOR`. You'll create a `TSVECTOR` index on an `articles` table, update the `search_vector` column, and set up a trigger to automatically update the `search_vector` column.

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

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

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

Find Marketing Employees in PostgreSQL

In this challenge, you'll assist the HR department by extracting a list of Marketing employees from a PostgreSQL database. You'll need to write a SQL query to select the `id` and `name` of employees in the 'Marketing' department, and save the query results to a file for verification.

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 `pg_restore`, and backing up specific tables.

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

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

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

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

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

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.

Prev
1
2
Next