Posts about Postgres Tutorials

If you haven't already read my colleague Steve Pousty's blog post on handling composite primary keys in a simple web application using Django and PostgreSQL, be sure to check it out. This post is going to be the first in a two-part series about adding a user registration system to the same app. In this first post, I'm going to talk specifically about setting up a "self-registration" form for our Dungeon and Dragons

Even if you're relatively new to SQL, chances are that you know the INSERT command is used to add new data to a table. For those of you newer to Postgres such as myself, you may not be familiar with a couple of neat tricks you could do with inserts. This post is a refresher on INSERT and also introduces the RETURNING and ON CONFLICT clauses if you haven't used them yet, commonly known as upsert.

Let's start with the basics, we usually see INSERT in two forms. The first explicitly provides the values for each row you want to add, like so:

INSERT INTO event (name, event_dt, mode)
VALUES ('Appointment', tstzrange('2019-12-14 12:00:00 EST', '2019-12-14 12:30:00 EST', '[)'), 'In-person'),
('Workshop', tstzrange('2020-01-11 10:00:00 PST', '2020-01-11 12:00:00 PST', '[)'), 'Virtual');

Today’s blog post is going to be a nice little adventure of learning how to use composite primary keys in a PostgreSQL many-to-many relationship table while building a Django application. Along the way we will talk about some basics of Django and some workarounds you need to use. Let’s dig in and get started.

Here on the developer relations team at Crunchy Data

At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive.

It was "Why have composite types? Why would you use them?". I threw in an answer a few others did as well, but collectively we didn't have anything definitive but all these seemed like valid cases.

But first, what are composite types?

This post is the second in a two-part series -- read the first here: Going Back to Basics with PostgreSQL Data Types.

In my last post, I shared some interesting (and at times surprising) things that I learned while digging into data types in PostgreSQL. Data types like numeric, integer, date, and char/varchar exist in every other relational database system since the need to work with such data is pretty much a given. The implementation may vary somewhat between systems, but generally there are standard ways you’ll want to process and analyze these types of data (e.g. perform mathematical calculations, find the length of a character string, cast from one type to another, etc).

In Postgres, we have a few more data types that may not be as well known even for experienced developers. Let’s take a quick look at arrays, enum, and range types.

Array type

When I first started to learn how to code, I was introduced to the concept of data types: a 6 is not the same as "6", because the former is numeric (typically an integer type, with some variations in terminology based on the language) and the latter a string; "true" is not necessarily the same as true, because true can be a Boolean value in some languages.

Underneath the code, these pieces of data are really just a combination of ones and zeros, but declaring their types allows them to play a particular role. A value’s data type tells the computer when an expression like 6+6 should return a 12, or 66! That said, it wasn’t until I started learning a little bit more about enterprise databases

Let's say you needed to document all of the tables in your PostgreSQL database. You wanted the output of the \d psql meta-command for all of the tables so you could put it in a shared documentation area. However, there were a lot of tables and you did not want to have to type all the commands that you needed. Before I explain how I can help, we will need to set up a simple database and provide some background on the \d

Many applications today record data from sensors, devices, tracking information, and other things that share a common attribute: a timestamp that is always increasing. This timestamp is very valuable, as it serves as the basis for types of lookups, analytical queries, and more.

PostgreSQL 9.5 introduced a feature called block range indexes

Common table expressions, aka CTEs, aka WITH queries, are not only the gateway to writing recursive SQL queries

The PostgreSQL 11 release is nearly here (maybe in the next couple of weeks?!), and while a lot of the focus will be on the improvements to the overall performance of the system (and rightly so!), it's important to notice some features that when used appropriately, will provide noticeable performance improvements to your applications.

One example of such feature is the introduction of "covering indexes" for B-tree indexes. A covering index