--- title: "FILTER vs CASE" publication: "Postgres Weekly" issue: 492 published_at: "2023-02-08" canonical_url: "https://postgresweekly.com/issues/492" --- Plus hypothetical indexing, cruddy tricks, and more Postgres events. | #​492 — February 8, 2023 [Web Version](https://postgresweekly.com/issues/492) Together with [![Polyscale\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_300,h_160/e_make_transparent/co_white,e_outline:7/rrkstxca0vg7e1eww6yw.jpg)](https://www.polyscale.ai/) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/zptmv6zk1ityaom0kvca.jpg)](https://blog.jooq.org/the-performance-impact-of-sqls-filter-clause/) [What Performs Better: `FILTER` or `CASE`?](https://blog.jooq.org/the-performance-impact-of-sqls-filter-clause/) — Lukas notes that SQL’s `FILTER` clause is syntactic sugar for an equivalent CASE expression in an aggregate function, but as we know, equivalent queries can have different performance profiles! Spoiler: This is also true for `FILTER` vs `CASE`. Lukas Eder 💡 If `FILTER` is new to you, never fear, Craig Kerstiens has [a 'Using Postgres `FILTER`' tutorial](https://www.crunchydata.com/blog/using-postgres-filter) to bring you up to speed. [Coming to Postgres 16: A New Way to Reserve Connection Slots](https://www.cybertec-postgresql.com/en/reserve-connections-in-postgresql-16/) — You’ve long been able to use `superuser_reserved_connections` to reserve connection slots for _superusers,_ but a new patch for Postgres 16 provides a way to reserve connection slots for _non_-superusers via a `pg_use_reserved_connections` role. Pavlo Golub [![\](https://copm.s3.amazonaws.com/e54130e9.png)](https://www.polyscale.ai/) [Database Caching, Without the Complexity](https://www.polyscale.ai/) — PolyScale.ai provides sub-millisecond read queries, without the cost and complexities typical of cache development. Deploy in minutes with our serverless edge network or on‑premise. PolyScale.ai sponsor **IN BRIEF:** - 📅 [PGDay Belgium 2023](https://pgconf.be/) takes place in Haasrode (not far outside Brussels) on May 12. A [call for speakers](https://pgconf.be/#cfs) is open till March 30 but take note of the special instructions if you submit a talk 😏 - 📅 [PGConf.de 2023](https://2023.pgconf.de/) takes place in Essen, Germany on June 27. [Swiss PGDay 2023](https://www.pgday.ch/2023/) is on later in the same week. - [Amazon RDS for Postgres now supports](https://aws.amazon.com/about-aws/whats-new/2023/02/amazon-rds-postgresql-new-minor-versions/) minor versions 14.6, 13.9, 12.13, 11.18, and 10.23. It has also added [support for the `seg` extension](https://aws.amazon.com/about-aws/whats-new/2023/02/amazon-rds-postgresql-supports-seg-extension/) which adds the `seg` data type. [Adventures in Embedding Postgres Schemas in GPT](https://canvasapp.com/blog/postgres-schemas-in-gpt/) — Fun experimentation in trying to convince OpenAI’s GPT to return useful SQL queries based upon a prompt, but there’s still plenty of work to do. William Pride [Simple SQL Tricks for More Effective CRUD](https://www.crunchydata.com/blog/sql-tricks-for-more-effective-crud) — The Create, Read, Update, Delete (CRUD) paradigm is a fundamental element of many apps that use databases and you can leverage SQL to make CRUD tasks more atomic and, as Paul says, _“do more things right inside the database.”_ Paul Ramsey [How to Get a Row and All Of Its Dependencies?](https://www.depesz.com/2023/02/07/how-to-get-a-row-and-all-of-its-dependencies/) — Getting a single row of a table is easy, but what about if you want to also get all of the rows it references? Hubert got experimenting with a function to build such documents – it’s as much interesting for the process as the end result. Hubert depesz Lubaczewski [Will Postgres Use My Index? Hypothetical Indexing for Postgres](https://www.crunchydata.com/blog/will-postgres-use-my-index-hypothetical-indexing-with-postgres) — [HypoPG](https://github.com/HypoPG/hypopg) can create _hypothetical_ indexes (B-tree, BRIN, and hash indexes, but not GIN or GIST) and test if Postgres will use them. You can couple it with `EXPLAIN` to produce the right indexes for your queries. Craig Kerstiens [Invoking (Your Own) Perl from PL/Perl](https://fluca1978.github.io/2023/02/06/PostgreSQLInvokingPerlFromPLPerl.html) Luca Ferrari 🛠 Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/lxwnydmo4bpoa6o9dqhl.jpg)](https://pgmodeler.io/blog/2023/2/1/i-proudly-announce-pgmodeler-100) [pgModeler 1.0 Released: A Postgres Database Modeler](https://pgmodeler.io/blog/2023/2/1/i-proudly-announce-pgmodeler-100) — An easy way to create and edit database models in a visual way. It’s packaged as a paid product but is also open source so you can build your own. [Official homepage](https://pgmodeler.io/) and [GitHub repo.](https://github.com/pgmodeler/pgmodeler) Raphael Araújo e Silva [Free eBook: How to Create the Best Postgres Index for Your Queries](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklySecondary02082023) pganalyze sponsor [pg_ivm 1.5: Incremental View Maintenance Postgres Extension](https://github.com/sraoss/pg_ivm/) — IVM can update materialized views more efficiently than recomputation when only small parts of the view are changed. [v1.5](https://github.com/sraoss/pg_ivm/releases/tag/v1.5) adds (basic) CTE support. SRA OSS LLC [pg_dumpbinary 2.9: Dump a Database in Binary Format](https://github.com/lzlabs/pg_dumpbinary) — Using a binary format for a dump has some benefits in [particular situations.](https://github.com/lzlabs/pg_dumpbinary#pg_dumpbinary) lzlabs [pgFormatter 5.5: SQL Syntax Beautifier](https://github.com/darold/pgFormatter) — Mostly a bug-fix release. There’s an [online demo here.](http://sqlformat.darold.net/) Gilles Darold - [Supabase Realtime 2.4](https://github.com/supabase/realtime) ↳ Postgres changes over WebSockets. - [pg_cron 1.5](https://github.com/citusdata/pg_cron) ↳ Run periodic jobs on Postgres. - [PEV2 1.7](https://github.com/dalibo/pev2) ↳ Postgres explain visualizer Vue.js component. - [pg-boss 8.4](https://github.com/timgit/pg-boss) ↳ Node.js job queuing system. - [Citus 11.2](https://github.com/citusdata/citus) ↳ Distributed Postgres as an extension. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)