---
title: "Creating fractals with SQL and Postgres"
publication: "Postgres Weekly"
issue: 302
published_at: "2019-04-24"
canonical_url: "https://postgresweekly.com/issues/302"
---

The latest Postgres news, articles, tools, and projects.       

#302 — April 24, 2019 

[Read on the Web](https://postgresweekly.com/issues/302)   

👋 This issue's **Tip of the Week** looks at SQL's `FILTER` clause. Scroll to the end of this issue to check it out.. 👀  

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/kkezxirgkdbs56ajblmu.jpg)](https://malisper.me/generating-fractals-with-postgres-escape-time-fractals/)   

[Generating Fractals with SQL and Postgres](https://malisper.me/generating-fractals-with-postgres-escape-time-fractals/) — At !!Con West 2019, Michael gave [a talk about creating fractals using SQL](https://www.youtube.com/watch?v=xKoYIvMFnoQ) and here’s the written version. This is not only a (very detailed) bit of fun, but you’ll learn something about both SQL and Postgres along the way — strongly recommended! 

Michael Malis   [![\](https://copm.s3.amazonaws.com/0da05f27.png)](https://pganalyze.com/ebooks/monitoring-postgres-logs?utm_source=PostgresWeeklyPrimary) 

[eBook: The Most Important Events to Monitor in Your Postgres Logs](https://pganalyze.com/ebooks/monitoring-postgres-logs?utm_source=PostgresWeeklyPrimary) — In this pganalyze eBook, we are looking at the Top 6 Postgres log events for monitoring query performance and preventing downtime. 

pganalyze sponsor   

[More on Postgres 12's `REINDEX CONCURRENTLY`](https://paquier.xyz/postgresql-2/postgres-12-reindex-concurrently/) — A few weeks ago we linked to a brief post about [REINDEX CONCURRENTLY](https://www.depesz.com/2019/03/29/waiting-for-postgresql-12-reindex-concurrently/), a new feature coming to Postgres 12 that would allow read and write queries to occur during the rebuilding of an index. Michael Paquier now has more insight on its operation and history. 

Michael Paquier   

[How I Use Postgres with Go(lang)](https://jbrandhorst.com/post/postgres/) — An experienced ‘gopher’ imparts knowledge around the best packages for driver, migrations, queries, and testing. If you happen to be a Go developer yourself, did you know we [have a Go newsletter?](https://golangweekly.com/?utm_source=pg&utm_medium=email&utm_campaign=cross_promo&utm_content=302) 

Johan Brandhorst   

▶[How to Fix Up a Corrupted TOAST Table](https://www.youtube.com/watch?v=4jcC-lYGM0k) 

Raghavendra Tadipathri   

[Coming to PostgreSQL 12: Log All Statements from a Sample of Transactions](https://www.depesz.com/2019/04/18/waiting-for-postgresql-12-log-all-statements-from-a-sample-of-transactions/) — A quick look at a new feature coming to Postgres 12 that will let you keep your statement log files under control by only logging a _sample_ of them when you wish to do so. 

Hubert depesz Lubaczewski  

🛠 Tools and Code  

[pg_sampletolog: An Extension to Sample Statements or Transactions to Logs](https://github.com/anayrat/pg_sampletolog) — Statement sampling is coming to Postgres 12 (above), but this extension works on Postgres 9.4-11 to let you log a sample of statements or transactions _now_. 

Adrien Nayrat   

[plpgsql_check: A PL/pgSQL Code Checker](https://github.com/okbob/plpgsql_check) — This is quite specialized but if you’re looking to find any errors lurking in your PL/pgSQL functions, it could be a lifesaver. The project is quite old but has been kept up to date and runs on modern Postgres versions. 

Pavel Stehule   

[The Best Way to Manage Access to Postgres](https://www.strongdm.com/product/?utm_source=&utm_medium=email&utm_campaign=2019-04-24%20-%20%5BUSA%5D%20-%20%5BSchD%5D%20-%20%5BSDM%5D%20-%20%5BENT%5D%20-%20PGW) — strongDM works with everything in your stack. Seriously. Any database. Any server. Every environment. [See how it works](https://www.strongdm.com/product/?utm_source=&utm_medium=email&utm_campaign=2019-04-24%20-%20%5BUSA%5D%20-%20%5BSchD%5D%20-%20%5BSDM%5D%20-%20%5BENT%5D%20-%20PGW). 

strongDM sponsor   

[tail_n_mail: A Log File Watcher Focused On Postgres Logs](https://github.com/bucardo/tail_n_mail) — A Perl script that automatically detects and emails an admin about ‘interesting’ items found in log files. [Docs here](https://bucardo.org/tail_n_mail/). 

Josh Williams   

[deno-postgres: A PostgreSQL Driver for Deno](https://github.com/bartlomieju/deno-postgres) — [Deno](https://github.com/denoland/deno) is an interesting, and experimental, JavaScript runtime built by the original creator of Node.js (_note that Deno is an anagram of Node!_) 

Bartek Iwańczuk   

[node-postgres: A Non-Blocking PostgreSQL Client for Node.js](https://github.com/brianc/node-postgres) — Very popular and has had some releases in the past few weeks. 

Brian Carlson   

[pq: The Pure Go Postgres Driver for `database/sql`](https://github.com/lib/pq) — [v1.1](https://github.com/lib/pq/releases/tag/v1.1.0) came out last week dropping support for Go 1.8 and below. 

Blake Mizerany and contributors       supported by [![Hasura\](https://res.cloudinary.com/cpress/image/upload/v1556017860/cpfwjijcqwvspqyggfja.png)](https://github.com/hasura/graphql-engine)  

💡 Tip of the Week 

Using `FILTER` to turn entity/value tables into rows of entities 

An entire article could be written on this topic, but I wanted to show off the most basic use of SQL:2003's `FILTER` clause that was added to Postgres 9.4. 

Let's say you have a table called `props` that represents [entities, attributes, and values](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) with an integer `id` column, textual attribute and value columns, and the following contents:  

![](https://res.cloudinary.com/cpress/image/upload/v1556051543/gyrmqkc10uqzt0liqnmi.jpg)   

The [`FILTER` clause](https://modern-sql.com/feature/filter) essentially adds an extra `WHERE` clause to aggregate functions (such as `MIN`, `MAX` and `SUM`) allowing you to scope them. 

This comes in _very_ handy for pulling out values from our `val` column based upon the value of the _attr_ column, therefore allowing us to turn a table of entities, attributes and values into a more classical set of columns.  

SELECT id,
 MAX(val) FILTER(WHERE attr='name') AS name,
 MAX(val) FILTER(WHERE attr='age') AS age,
 MAX(val) FILTER(where attr='city') AS city
 FROM props GROUP BY id;   

You might need to reproduce this table and play with the query to get the feel of what's going on, but essentially we are selecting each row _grouped by the ID_ (i.e. the ID of the underlying entity) and then picking the value associated with each ID that matches certain attribute names, allowing us, in this case, to extract the `name`, `age`, and `city` values. `MAX` works fine as an aggregate function here as there is only one attr/val pair per entity. 

The `FILTER` clause has a lot more uses than this, but I felt this was both a pretty neat and perhaps unexpected example of its use. 

You can learn more in [this article](https://medium.com/little-programming-joys/the-filter-clause-in-postgres-9-4-3dd327d3c852) and in [the official Postgres documentation.](https://www.postgresql.org/docs/9.5/sql-expressions.html#SYNTAX-AGGREGATES) 

This week's tip is sponsored by _[Hasura](https://github.com/hasura/graphql-engine)_, creators of the high-performance GraphQL engine on new and existing Postgres databases. [Check them out on GitHub](https://github.com/hasura/graphql-engine).      

🗓 **Upcoming Postgres Events** 

- [pgconf.de 2019 (May 10 in Leipzig, Germany)](https://2019.pgconf.de/) — The latest edition of the highly successful German-speaking PostgreSQL conference.
- [PGDay.IT 2019 (May 16-17 in Bologna, Italy)](https://2019.pgday.it/en/)
- [PGCon 2019 (May 28-31 in Ottawa, Canada)](https://www.pgcon.org/2019/) — An annual conference for users and developers to meet and discuss all things Postgres.
- [Postgres Vision 2019 (June 24-26 in Boston, MA)](https://postgresvision.com/)          

Curated by [Cooperpress](https://cooperpress.com/) and [Craig Kerstiens](https://twitter.com/craigkerstiens). 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom