Kat Batuigas
Kat Batuigas
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 ( DnD ) players, using Django's built-in authentication (which includes built...
Read MoreKat Batuigas
Kat Batuigas
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...
Read MoreSteve Pousty
Steve Pousty
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 , we have started building a demo application that manages Dungeon and Dragons (D&D) players, characters, dungeon masters, and c...
Read MoreCraig Kerstiens
Craig Kerstiens
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. Composite types ) are a custom type that i...
Read MoreKat Batuigas
Kat Batuigas
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...
Read MoreKat Batuigas
Kat Batuigas
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 pa...
Read MoreMark Lane
Mark Lane
Let's say you needed to document all of the tables in your PostgreSQL database. You wanted the output of the 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 psql meta-command. Open a connection to a Postgres instance with the psql clien...
Read MoreJonathan S. Katz
Jonathan S. Katz
UPDATE : BRIN is still the specialist for correlated, append-mostly data. Later releases added and Bloom opclasses, parallel BRIN builds, and async I/O that can change the BRIN-vs-parallel-seq-scan bake-off—re-test after upgrade. See Postgres 19: How Our Advice Has Changed Since We Wrote It . 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...
Read MoreJonathan S. Katz
Jonathan S. Katz
Common table expressions , aka CTEs , aka WITH queries , are not only the gateway to writing recursive SQL queries , but also help developers write maintainable SQL. WITH query clauses can help developers who are more comfortable writing in imperative languages to feel more comfortable writing SQL, as well as help reduce writing redundant code by reusing a particular common table expressions multiple times in a query. A new patch , scheduled to be a part of PostgreSQL 12 major release late...
Read MoreJonathan S. Katz
Jonathan S. Katz
UPDATE : is still how you build a covering index without polluting the sort key, and vacuum still matters for true index-only scans. Postgres 18’s B-tree skip scan can also cover more queries with one well-designed composite index—check that before adding another companion index. See Postgres 19: How Our Advice Has Changed Since We Wrote It . 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 overal...
Read More