PURCHASE

The Art of PostgreSQL, one article at a time

The Art of PostgreSQL blog introduces PostgreSQL concepts that are useful for application developers. The content of this blog is either extracted from the published book, or written in preparation for book material. Reading this blog, you can enjoy The Art of PostgreSQL content one piece at a time!

Remember to practice what you learn here and elsewhere!

2019-12-25

pgDay Paris 2019 presentation: How to write SQL queries?

The videos from pgDay Paris 2019 are online, and they look stunning! If you’ve been to the conference, you will be able to enjoy great memories, and if you’ve not been there, you can now enjoy all the fine content that was recorded there at the Youtube Channel for pgDay.Paris.

This year at pgday Paris I tried something new. My presentation was interactive, and I’ve been writing queries together with the audience, making mistakes, loosing keystrokes and having all the fun!

The idea behind looking like a fool writing queries in front of an audience was to show the process behind those elegant queries that we tend to share in one piece, on a slide where they shine, beautifully indented. We don’t get to those queries writing them like we would prose. It takes multiple iterations to get there. From something quite simple to something more and more sophisticated, until we have exactly the result set we are interested in.


2019-11-29

Black Friday & Cyber Monday

Today is Black Friday, then followed by a week end that leads directly to Cyber Monday. This is a period where you can find great deals online, with many discounts. I’ll make it short, because we all are busy, right? From today to Monday included my book The Art of PostgreSQL is available at a 50% discount with the coupon “BLACKFRIDAY2019”. Enjoy!

2019-09-27

Why Postgres?

That’s a very popular question to ask these days, it seems. The quick answer is easy and is the slogan of PostgreSQL, as seen on the community website for it: “PostgreSQL: The World’s Most Advanced Open Source Relational Database”. What does that mean for you, the developer?

In my recent article The Art of PostgreSQL: The Transcript, part I you will read why I think it’s interesting to use Postgres in your application’s stack. My conference talk addresses the main area where I think many people get it wrong:


2019-09-25

The Art of PostgreSQL: The Transcript, part III

This article is a transcript of the conference I gave at Postgres Open 2019, titled the same as the book: The Art of PostgreSQL. It’s availble as a video online at Youtube if you want to watch the slides and listen to it, and it even has a subtext!


2019-09-23

The Art of PostgreSQL: The Transcript, part II

This article is a transcript of the conference I gave at Postgres Open 2019, titled the same as the book: The Art of PostgreSQL. It’s availble as a video online at Youtube if you want to watch the slides and listen to it, and it even has a subtext!


2019-09-20

The Art of PostgreSQL: The Transcript, part I

This article is a transcript of the conference I gave at Postgres Open 2019, titled the same as the book: The Art of PostgreSQL. It’s availble as a video online at Youtube if you want to watch the slides and listen to it, and it even has a subtext!


2019-09-17

How to Learn SQL?

Here we are, another SQL query to write. You wish you knew how to write that mechanically, like you would a loop in your favorite programming language. Or at least have a pretty clear idea of a skeleton to tweak until it gives the result set you expect. So instead of working on your SQL query, you google How to write a SQL query? or maybe even How to learn SQL? Right. I feel you, I’ve been there too, even if quite some time ago…

So here my article where I teach you how to learn SQL.

I want to share with you how I did it, and how I continue to do it. There’s no magic secret sauce to it though, it’s all basic work. Again, we have to learn the main concepts and how they play together, then practice simple steps, and then build from there.


2019-09-07

The R in ORM

Ok, let’s face it, I like SQL. A lot. I think it’s a fine DSL given how powerful it is, and I respect its initial goal to attract non developers and try to build English sentences rather than code.

Also, I understand that manually hydrating your collection of objects in your backend developement language is not the best use of your time. And that building SQL as strings makes your code ugly. I get it.


2019-09-07

What is an SQL Aggregate?

In our previous articles we had a look at What is an SQL relation? and What is a SQL JOIN?. Today, I want to show you what is an aggregate in SQL.

You might have heard about Map/Reduce when Google made it famous, or maybe some years before, or maybe some years later. The general processing functions map and reduce where invented a very long time ago. The novelty from the advertising giant was in using them in a heavily distributed programming context.

With map you apply the same processing to every object in a collection. With reduce, you compute a result per collection of object. In SQL we know how to do that too, and we call that an aggregate.


2019-09-07

What is an SQL JOIN?

It took me quite some time before I could reason efficiently about SQL JOINs. And I must admit, the set theory diagrams never helped me really understand JOINs in SQL. So today, I want to help you understand JOINs in a different way, hoping to make the concept click at once for you too!