#289 — January 23, 2019

Read on the Web

Postgres Weekly

An Overview of Postgres' JSON Capabilities — Native JSON support landed in Postgres 9.2 almost six years ago and brought some of the benefits of document databases to the Postgres world. Things have progressed, though, and this blog provides an overview of what’s possible.

Venkata Nagothi

How We Solved a Storage Problem in Postgres Without Adding a Single Byte of Storage — A short story about a storage-heavy query in an ETL task and the ‘silver bullet’ that solved the issue.

Haki Benita

Monitoring Your PostgreSQL Database with Telegraf and InfluxDB — This tutorial will specifically cover the process of setting up Telegraf and InfluxDB to monitor PostgreSQL.

InfluxData sponsor

pg_permission: Inspecting Your Security and Permissions — pg_permission is a tool that displays the security settings of your database to give you a fast overview of what is going on and what permissions have been granted.

Hans-Jürgen Schönig

PGLoader: Migrate to Postgres in a Single Command — A COPY-based data loading tool that supports both parallelism and can load in data that may be partially erroneous but without stopping (as \copy might).

Dimitri Fontaine

Postgres Logical Replication Gotchas — Things to watch out for when using logical replication.

RapidLoop

The Internals of PostgreSQL: An Introduction — A bit of a golden oldie but one that’s been doing the rounds on social media again recently and which has received some updates in the past year.

Hironobu Suzuki

Deploying and Managing PostgreSQL 11 — Learn how the new ClusterControl 1.7.1 delivers more advanced monitoring & management features for PostgreSQL.

Severalnines sponsor

PostgreSQL to Microsoft SQL Server Using TDS Foreign Data Wrapper — Got some data stored in SQL Server that you want to query from Postgres? It’s possible with a foreign data wrapper, and here’s how.

Luca Ferrari

Postico: A Modern Postgres Client for macOS — It’s a commercial product (with a trial) but looks elegant and Mac-like.

Egger Apps

supported by

💡 Tip of the Week

Use an exclusion constraint to prevent overlapping date ranges
by Brendan Carney

Let’s say we’re building a product that schedules which location an employee should be at during a given range of time. Since an employee can’t be in two places at once, we’ll want a way to make sure that none of our date ranges overlap - let's use an exclusion constraint!

(You'll have to enable the btree_gist extension if you haven't already: CREATE EXTENSION btree_gist;)

Our table:

CREATE TABLE employee_locations (
  name varchar,
  location varchar,
  start_date date,
  end_date date
);

And the constraint:

ALTER TABLE employee_locations
  ADD CONSTRAINT unique_location_date_range
    EXCLUDE USING gist (
      name WITH =,
      daterange(start_date, end_date, '[]') WITH &&
    );

Now, let's insert a record to say Brendan should be at the office for the month of January:

INSERT INTO employee_locations
  (name, location, start_date, end_date)
VALUES
  ('Brendan', 'Office', '2019-01-01', '2019-01-31');

If we now try to put Brendan in a different location at any overlapping time, the constraint won't allow it:

INSERT INTO employee_locations
  (name, location, start_date, end_date)
VALUES
  ('Brendan', 'Store', '2019-01-15', '2019-02-15');

ERROR: conflicting key value violates exclusion constraint "unique_location_date_range"

This week's tip is sponsored by strongDM, the best way to secure access to your servers/databases. Schedule a 10 min demo.