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.

Find Marketing Employees in PostgreSQL

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.

Create Your Own Recipe Database

Create Your Own Recipe Database

In this challenge, you'll embark on a culinary journey by creating your own recipe database using PostgreSQL. As a budding food blogger, you'll set up a dedicated database named `recipe_db` to manage your growing collection of recipes.

Manage Employee Data in PostgreSQL

Manage Employee Data in PostgreSQL

In this challenge, you'll step into the role of a database administrator and manage employee data within a PostgreSQL database. The task involves using PostgreSQL commands to update employee records in the `employees` table to reflect changes in the organization.

Verify PostgreSQL Connection Details

Verify PostgreSQL Connection Details

In this challenge, verify PostgreSQL server operational status after a critical alert. Confirm the server accepts connections and connect to the 'postgres' database using `pg_isready` and `psql` commands as the `postgres` user. Ensure the PostgreSQL service is functioning correctly.

Create Employee Table in PostgreSQL

Create Employee Table in PostgreSQL

In this challenge, you will design and create a `employee_contacts` table in PostgreSQL to store employee contact details, ensuring data integrity through appropriate data types and constraints. You'll be working within a pre-configured environment where an `employees` table already exists.

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.

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.

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.

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 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 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.

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.

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 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 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 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 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 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.

Prev
1
2
Next