PostgreSQL Tutorials

How to solve common and tricky PostgreSQL issues covering Go, Python, Java, Dockers, Kubernetes, Django, and other technologies

Recent Tutorials

Why more and more enterprises are choosing PostgreSQL as their go-to database!

Thom BrownFebruary 7, 2020

This article notes the rise in popularity of PostgreSQL among enterprise businesses and discusses some of the advantages PostgreSQL offers over other database systems.

How to sort rows using ORDER BY and SELECT in PostgreSQL: 6 Examples

Arun GavhaneFebruary 7, 2020

This article reviews five ways to sort table data using the ORDER BY clause together with the SELECT statement in PostgreSQL.

How to modify data in PostgreSQL using INSERT, UPDATE, UPDATE JOINS, DELETE and UPSERT

Arun GavhaneFebruary 7, 2020

This article reviews how to use the basic data manipulation language (DML) types INSERT, UPDATE, UPDATE JOINS, DELETE, and UPSERT to modify data in tables.

Benchmarking PostgreSQL on an AWS m5.metal instance

Kuntal GhoshJanuary 24, 2020

This article discusses the results of benchmark tests made using pgbench to test the performance of different versions of PostgreSQL on an instance of AWS m5.metal.

Using Foreign Data Wrappers to access remote PostgreSQL and Oracle databases

Piyush SharmaJanuary 14, 2020

This article explains how to use Foreign Data Wrappers to access data in remote PostgreSQL and Oracle databases.

PostgreSQL and Golang Tutorial

Richard YenJanuary 9, 2020

How to use PostgreSQL with Golang: This article covers how to use PostgreSQL together with the programming language Golang (Go). After showing how to get started with Go’s Object Relational Mapper, it offers an example of setting up a PostgreSQL database using Go.

10 Examples of PostgreSQL Stored Procedures

Ranjeet DhumalJanuary 8, 2020

A stored procedure is a set of structured queries and statements such as control statements and declarations. Here are ten examples of stored procedures that can be useful in different situations.

How to query PostgreSQL data using the SELECT statement in Java

Ranjeet DhumalJanuary 2, 2020

This article provides instructions for querying data using the PostgreSQL SELECT statement in Java.

How to use tables and column aliases when building a PostgreSQL query

Prabhat SahuJanuary 2, 2020

This article explains how to use table and column aliases in PostgreSQL to create more meaningful queries.

Using PostgreSQL with Toad Edge

Amit SharmaJanuary 2, 2020

This article reviews how to begin using Toad Edge by Quest for environments with multiple databases. It walks through the steps of connecting Toad Edge to a PostgreSQL database.

How to use Java to create a table in PostgreSQL

Ranjeet DhumalDecember 30, 2019

This article shares a sample Java program that can be used to create a table in PostgreSQL.

How to use PostgreSQL with Django

Richard YenDecember 30, 2019

This article covers how to use PostgreSQL together with the Python web application framework Django. After walking through the Django installation process it shows how to get started creating a simple Django application.

Examples of using GREATEST and LEAST functions in PostgreSQL

Tushar AhujaDecember 30, 2019

This article discusses the conditional expressions GREATEST and LEAST in PostgreSQL and gives some examples of their usage.

How to use block structure to write anonymous blocks and divide larger blocks into logical subblocks

Tushar AhujaDecember 30, 2019

This article discusses block structure in PostgreSQL, how to write an anonymous block, and how to divide a larger block into logical subblocks.

How to use logical operators in PostgreSQL

Tushar AhujaDecember 20, 2019

This article looks at logical operators in PostgreSQL and how to use them. The logical operators in PostgreSQL are AND, OR, and NOT.

10 Tools every developer should have when working with PostgreSQL

Tushar AhujaDecember 20, 2019

This article lists 10 open source tools to help developers working with PostgreSQL.

How to alter, rename, drop, and copy databases in PostgreSQL

Hrishikesh GoreDecember 20, 2019

This article covers the ALTER DATABASE, RENAME DATABASE, and DROP DATABASE statements and using the CREATE DATABASE WITH TEMPLATE statement to copy a database.

How to import and export data using CSV files in PostgreSQL

Amit SharmaDecember 20, 2019

This article explains how to import data from a CSV file into PostgreSQL and how to export it back from PostgreSQL to CSV. It includes an introduction to the CSV file format and some examples of its usage.

How to use PostgreSQL with Laravel

Richard YenDecember 19, 2019

This article covers how to use PostgreSQL together with the PHP framework Laravel to deploy web applications. After walking through the Laravel installation process it shows how to get started and create tables.

How to declare variables in PL/pgSQL stored procedures

Rajkumar RaghuwanshiDecember 19, 2019

This article covers how stored procedures can make use of variables to be more functional and useful. After defining PL/pgSQL, stored procedures, and variables, it provides examples of how variables can be used.

How to run hierarchical queries in Oracle and PostgreSQL

Tushar AhujaDecember 19, 2019

This article introduces hierarchical queries and shows the differences in their usage between Oracle and PostgreSQL.

How to develop a user-defined function in PostgreSQL stored procedures

Tushar AhujaDecember 19, 2019

This article covers how to create user-defined functions using PL/pgSQL procedural language in PostgreSQL. It introduces user-defined functions and gives examples of their use in different scenarios: PL/pgSQL; User-defined functions and procedures; CREATE FUNCTION statement syntax; and Examples of user-defined functions.

How to use limit and offset in PostgreSQL

Ranjeet DhumalDecember 18, 2019

This article covers LIMIT and OFFSET keywords in PostgreSQL. It provides definitions for both as well as 5 examples of how they can be used and tips and tricks.

How to work with PostgreSQL transactions

Tushar AhujaDecember 18, 2019

This article covers PostgreSQL transactions. It defines the required keywords within the transaction syntax, explains the safeguards transactions provide in case of error and other benefits of transactions, then lists their parameters.

How to create, modify, delete, and manage views in PostgreSQL

Jackie ChiuDecember 17, 2019

This article discusses VIEW in PostgreSQL. It describes how a VIEW functions as a shortcut if calling the same query multiple times, then defines the following commands: CREATE VIEW, ALTER VIEW, and DROP VIEW.

Comprehensive guide on how to tune database parameters and configuration in PostgreSQL 

Swapnil SuryawanshiDecember 17, 2019

This article discusses parameters that can be used to configure performance in PostgreSQL. After introducing ways of measuring database speed, it reviews the parameters and ways to optimize performance with them.

How to Use Grouping Sets, Cube, and Rollup in PostgreSQL

Tushar AhujaDecember 17, 2019

This article discusses grouping sets, cubes, and rollups in PostgreSQL.

How to raise errors and report messages within stored procedures and functions

Rajkumar RaghuwanshiDecember 13, 2019

This article discusses the RAISE command for reporting errors, warnings, and other report messages within stored procedures and functions in PostgreSQL. Levels of error messages are covered along with settings for specifying their display to the client or log.

An Overview of PostgreSQL Indexes

Ranjeet DhumalDecember 13, 2019

This article describes indexes in PostgreSQL and how they can help retrieve data faster.

Why you should use Docker Compose

Shaktisikha SahooDecember 13, 2019

This article explains the benefits of using Docker Compose for creating multiple container applications.

Postgres 13 - logical_decoding_work_mem and how it saves your server from going out of memory.

Kuntal GhoshDecember 9, 2019

This article describes a problem in PostgreSQL where logical replication can cause a server to run out of memory. It explains the cause of the problem, demonstrates the severity of the problem with an example, then provides a solution introduced in PostgreSQL 13, the logical_decoding_work_mem parameter.

PostgreSQL 13 New Feature: DROP DATABASE Forcefully

Vigneshwaran ChandrashekarDecember 9, 2019

This article describes a new feature in PostgreSQL that allows the DROP DATABASE command to be executed even if active sessions are connected to the database.

How to combine multiple queries into a single result set using UNION, INTERSECT, and EXCEPT

Thom BrownDecember 6, 2019

This article discusses methods for comparing and combining multiple queries into a single result set in PostgreSQL.

How to connect to the Postgres database using Eclipse and Netbeans

Shivam DhapatkarDecember 6, 2019

This article explains how to connect to a Postgres database using the Eclipse and Netbeans IDEs. It first defines what an Integrated Development Environment (IDE) is, then walks through the steps for connecting to both IDEs.

Everything you need to know about Postgres stored procedures and functions 

Thom BrownDecember 6, 2019

This article reviews the differences between stored procedures and functions in Postgres and the types of functionality they provide.

Using SELECT to query data from a single table and multiple tables 

Hrishikesh GoreDecember 6, 2019

This article covers the SELECT statement in PostgreSQL and how to use it to query data from single and multiple tables

How to create a PostgreSQL database and users using psql and pgAdmin

Amit SharmaDecember 5, 2019

While working with PostgreSQL the two basic requirements is to create a database and set up a few users. This will help us in eliminating the need for reinstallation, if we mess up the default set of databases or users that already exist, while trying to learn and build our understanding.

How to Install Postgres on Ubuntu

Pooja RathodDecember 5, 2019

This article covers how to install PostgreSQL on Ubuntu Server for Linux.

How to select distinct values from query results in PostgreSQL

Tushar AhujaDecember 4, 2019

In this post, we are going to see how to select distinct values from SQL queries/statements. One of the easiest ways to select distinct values is using the DISTINCT keyword.

Logical Replication in PostgreSQL Explained

Kuntal GhoshDecember 4, 2019

This post discusses logical replication in PostgreSQL. It reviews the differences between physical or binary replication and logical or transactional replication.

How Bulkload performance is affected by table partitioning in PostgreSQL

Beena EmersonDecember 4, 2019

This article covers how benchmark tests can be used to demonstrate the effect of table partitioning on performance.

How to use COALESCE in PostgreSQL

Tushar AhujaDecember 3, 2019

In this post, we are going to understand what the COALESCE function is and how to use it in PostgreSQL.

Working with Postgres Audit Triggers

Tushar AhujaDecember 2, 2019

In this post, we are going to look at Audit triggers and how we can use them in PostgreSQL databases.

How to Create a Constant Table in PostgreSQL

Tushar AhujaDecember 2, 2019

In this post, we are going to look at what a constant table in PostgreSQL is and how we can use it.

How to Use Event Triggers in PostgreSQL

Richard YenNovember 22, 2019

One of the missing features in PostgreSQL’s implementation of triggers was that DDL could not be detected very reliably. With the concept of event triggers introduced in v. 9.3, this is now possible.

Installation of PostgreSQL on Mac OS

Ankit ShuklaNovember 20, 2019

Installation of PostgreSQL is easy on Mac OS and in the below blog, we will try to capture all the steps which are necessary to do so.

How to create and refresh data for materialized views in PostgreSQL

Richard YenNovember 20, 2019

Most relational database systems provide the functionality to create a VIEW, which basically acts like a shortcut or macro.

How to benchmark partition table performance

Beena EmersonNovember 19, 2019

With the addition of declarative partitioning in PostgreSQL 10, it only made sense to extend the existing pgbench benchmarking module to create partitioned tables. A recent commit of patch by Fabien Coelho in PostgreSQL 13 has made this possible.

Everything you need to know about PostgreSQL triggers

Ranjeet DhumalNovember 18, 2019

A “trigger” is defined as any event that sets a course of action in a motion. In PostgreSQL, if you want to take action on specific database events, such as INSERT, UPDATE, DELETE, or TRUNCATE, then trigger functionality can be useful as it will invoke the required function on defined events.

How to Use Postgres Unions

Richard YenNovember 18, 2019

Whether we remember it or not, almost all of us learned set theory in elementary school. We drew Venn diagrams with overlapping circles and learned to articulate what the overlap means, making use of a strange set of operators to formulate equations around them. With SQL, there are occasions where we might want to use these concepts from set theory, whether it’s to concatenate two data sets or to extract information about two sets’ relationships. For this, PostgreSQL provides syntax for set operations: UNION, INTERSECT, and EXCEPT. In this article, we will focus on the UNION operator.