LIMIT, OFFSET, ORDER BY and Pagination in PostgreSQL

LIMIT and OFFSET are particularly useful clauses in PostgreSQL. LIMIT, as it’s name suggests, limits the number of rows returned in a SELECT query. For instance, if the limit count is 4, maximum of four rows are returned, or less than 4, if the query itself yield less number of rows.   Consider the following […]

Full Story »

LIKE and ILIKE for Pattern Matching in PostgreSQL

LIKE and ILIKE are used for pattern matching in PostgreSQL. LIKE is the SQL standard while ILIKE is a useful extension made by PostgreSQL. To begin with, we will create a tiny table with few random string values. Two of the important selectors in pattern matching with LIKE/ILIKE are the percentage sign(%) and underscore(_). % sign […]

Full Story »

PostgreSQL GROUP BY and Aggregate Functions – Explained

In this post we will discuss about the usage of GROUP BY clause and Aggregate Functions in PostgreSQL. PostgreSQL is having a large number of useful aggregate functions. We will see a few of the most important functions, skipping the common ones like sum(), avg() etc. Consider the following table: Quick Navigation General forrm of GROUP BY GROUP BY […]

Full Story »

PostgreSQL SELECT Query Basics – Tutorial

In this post we will discuss about the usage of SELECT query in PostgreSQL. SELECT Query form the very basic of the Structured Query Language(SQL) standard. Over the years PostgreSQL has added a handful of advanced features to the SELECT Query. Though, SELECT in the purest form works perfectly in PostgreSQL. If you want to learn […]

Full Story »

How to Install PostgreSQL 9.4 in Ubuntu / Linux Mint

This post aims at documenting the complete installation and basic configuration of PostgreSQL 9.4 on an Ubuntu or Linux Mint OS in layman’s terms. This is tested in Ubuntu 14.04.3 LTS and Linux Mint 17.2 Rafaela and should work on other releases also. How to install PostgreSQL 9.4 Server and PGAdmin3 on a clean Ubuntu […]

Full Story »

Install & Configure PgBouncer | Pooling in PostgreSQL #1

This is the first post among the series of posts which demonstrates various tools to set up database pooling in a PostgreSQL database server. In this post we will learn how to install, configure and connect to a database pool using the popular tool – PgBouncer. Table of Contents What is Database Pooling & Why […]

Full Story »

PostgreSQL 9 High Availability Cookbook [Review]

The PostgreSQL 9 High Availability Cookbook written by Shaun M. Thomas, unlike any other PostgreSQL references which concentrate on Postgres programming, focus on the scalability and high availability of a PostgreSQL server. The various recipes available in this book from hardware selection through setting up database pools and cluster control reminds you of a complete […]

Full Story »

How to Install PostgreSQL 9.2 + PostGIS 2.0 in Ubuntu

Though PostgreSQL 9.3 has released, there are people who still uses PostgreSQL 9.2 in combination with PostGIS 2.0. It is fairly easy to install and set-up PostgreSQL 9.2 in Ubuntu. It can be done in no time using any package manager like aptitude or synaptic. But the PostGIS 2.0 version available in apt repository has […]

Full Story »

IP Address Concepts & Postgres Network Address Data types

Table of Contents Introduction What is IP Address? IPv4 Address Representation IPv6 Address Representation Supernet, Subnet & Sub-subnet Host Address Private v/s Public IP Address space Reserved Addresses IP Address Data Types in PostgreSQL Difference Between CIDR and INET Data Types IP Address Functions in PostgreSQL Introduction PostgreSQL, in addition of being a great database […]

Full Story »

How to: Create a Heatmap Raster in PostGIS and Render in GeoServer

In this post, we are going to explore a little of the the raster processing capabilities of the powerful PostgreSQL extension – PostGIS. We will be doing the following steps in this tutorial: Create a PostGIS raster and populate it’s pixel values. Export the raster data as a file in TIFF format Convert the TIFF […]

Full Story »
Page 1 of 212»

Find someting

Stay Connected

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other subscribers