#321 — September 4, 2019

Read on the Web

Postgres Weekly

pspg: A Unix Pager Designed for Postgres Tables — If you use psql you probably use less as your pager, but it doesn’t directly support tabular data. This does! The latest master branch version also supports sorting by column.

Pavel Stehule

How to Compute How Many Working Hours in a Date Range with PL/pgSQL — How many working hours are there in a range of dates? A perfect, easily digested example of a situation where PL/pgSQL can be very handy!

Luca Ferrari

Because Your Data Is Your Business — PGX provides complete PostgreSQL support, from emergency services through daily operational support, to strategic planning. We’re here for your data ops.

PostgreSQL Experts, Inc. sponsor

How PostgreSQL 12 Can Improve Your Performance — “Just upgrade and take advantage of performance improvements,” says the author. Postgres 12 boosts CTE queries, has JIT compilation for queries by default, and indexing is also improved.

Jonathan S. Katz

Why You Need plpgsql_check if You Write Procedures in PL/pgSQL — PL/pgSQL lets you write and execute some pretty advanced logic directly within Postgres and SQL queries and plpgsql_check can help you with performance and SQL injection problems.

Pavel Stěhule

Running PostgreSQL on Google Cloud Platform (GCP) — A look at the features Google’s platform offers for Postgres users.

Viorel Tabara

How to Set Up Postgres 12 Beta 3 for Testing on Amazon RDS — AWS offers beta versions of Postgres for testing in a ‘preview environment’ in their us-east-2 region.

Jignesh Shah

IT Monitoring in the Era of Containers: Tapping into eBPF Observability

InfluxData sponsor

Postgres Connection Strings and psql — Did you know there are three different ways to establish a connection to the same database? A key/value approach, the popular ‘URL’ approach, and using command line options with psql.

Dimitri Fontaine

Setting Up Remote Backup and Restore with pgBackRest

Granthana Biswas

Retroactively Adding Binary I/O to a Type in Postgres

Johann 'Myrkraverk' Oskarsson

PERIODs and SYSTEM VERSIONING for Postgres — Periods are a SQL:2016 feature that are a bit like Postgres’s range types and this extension enables some basic support in Postgres ahead of an official implementation.

Vik Fearing

supported by Percona

💡 Tip of the Week

A basic example of using DISTINCT ON

Last week, our tip was about SELECT DISTINCT, a way to easily select unique values from a column without using GROUP BY. This week, we take DISTINCT a step further!

Where SELECT DISTINCT selects unique values from a column, SELECT DISTINCT ON selects the first rows that can distinctly match a variety of conditions. This is hard to explain in writing alone, so let's try the simplest of examples.

Let's create a table to store temperatures in different locations. In the real world, you'd probably have a time-related column too, but we'll skip it for brevity:

CREATE TABLE temperatures
  (location TEXT,
   temp INT);
   
INSERT INTO temperatures
  (location, temp)
  VALUES
  ('Springfield', 71),
  ('Springfield', 74),
  ('Springfield', 64),
  ('Springfield', 56),
  ('Miami', 84),
  ('Miami', 77),
  ('Miami', 91);

Now, let's say we want to only get the highest temperature recorded in each location. SELECT DISTINCT ON to the rescue:

SELECT DISTINCT ON(location) *
  FROM temperatures
  ORDER BY location, temp DESC;
  
    location | temp
--------------------
       Miami | 91
 Springfield | 74

If we hadn't used DISTINCT ON we would have got all of the locations and temperatures with the temperatures in descending order. DISTINCT ON(location), however, stripped the results down to the first matching result for each location, netting us the highest temperature for each location.

This Tip of the Week is sponsored by Percona. Learn how to upgrade or migrate your PostgreSQL database through a lowest possible downtime with Percona.

🗓 Upcoming Events