Clubhouse is now FREE with all core features, for up to 10 users! Learnmore
Data

Off the beaten path: Less well-known features of PostgreSQL

Peter Bex

The PostgreSQL database engine has many well known cool features. It also has features that might surprise you because you simply never heard of them. Or perhaps the ORM you're using does not expose them. Let's dip our toes into two of those, shall we?

Custom data types

You can create so-called "composite data types in the database by using CREATE TYPE. After having created a new type, you can use it in column definitions. You can even create columns which are arrays of that type!

Let's say we're selling items in predefined colors and want to treat these as first-class values. First, we'd create a type for it:

CREATE TYPE rgb_color AS ( red float, green float, blue float );

Then, we can make and populate a table containing our widgets:

CREATE TABLE widgets ( id SERIAL, name text, color rgb_color );
INSERT INTO widgets (name, color) VALUES ('sports car', ROW(1.0, 0.0, 0.0)), ('sleek smartphone', ROW(0.0, 0.0, 0.0)), ('bridal dress', ROW(1.0, 1.0, 1.0));

As you can see, you specify a composite type's value with the ROW constructor. If you think about it, normal rows from tables really are composite types already! We're simply nesting this idea here to put an entire "row" inside a column value.

Querying is simple too. Let's pick everything that has some red in it:

SELECT * FROM widgets WHERE (color).red >= 0.5;

The syntax is a little bit awkward but required so that the parser knows "color" is not a table name. Now, so far this seems to be needlessly complicating things. We could just store the red, green and blue values in separate columns and call it a day. However, being able to treat your custom type as units can be very helpful. This is useful for the same reason that abstract data types in programming languages are useful. It can even change your way of thinking about values.

In fact, high-quality PostgreSQL language bindings allow you to map these PostgreSQL composite types to types in the programming language and vice versa. Now that's when it gets really interesting!

For a quick example how to register custom types in Python's Psycopg2adapter, see these slides. I've also written an in-depth example on how to do this in the Scheme programming language.

LISTEN/NOTIFY

If you're looking for a simple but effective way to broadcast messages to multiple applications that connect to the same database, look no further than the LISTEN and NOTIFY commands.

On the consumer side, you can simply decide to listen on any"channel" name you can come up with:

LISTEN my_channel;

On the producer side, you can notify all consumers that are listening on a particular channel:

NOTIFY my_channel, '{"importance": 10, "message": "hello"}';

At each psql client that's connected, you'll see the message:

Asynchronous notification "my_channel" with payload "{"importance": 10, "message": "hello"}" received from server process with PID 123.

Note that you need to perform a query before you'll see the event happen (sending an empty query - just a semicolon - is fine, too).

It is common practice to JSON-encode the payload so you can have more structured messages, but that's not required; any string can be sent. Check your language's PostgreSQL library for documentation on how to read these notifications.

This is not a complete replacement for a message queue, because the messages are lost when the connection is dropped, and when you (re)connect after a message has been sent, you won't receive it. But it's a great, cheap way to simply send information to all active clients. The best part is that you don't have to set up any new services for this.

I hope these two examples have got your gears spinning. There are many cool things you can do with PostgreSQL that your framework might not directly expose, but which can still be very useful!