# Postgres hidden gems

DevFeed: [Postgres hidden gems](<https://devfeed.tech/articles/postgres-hidden-gems-41205.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2018/01/31/Postgres-hidden-gems/>)

Author: Map

Published: 2018-01-31T20:55:56Z

Content type: article

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Database](<https://devfeed.tech/topics/database.md>), [Replication](<https://devfeed.tech/topics/replication.md>), [Network Operations](<https://devfeed.tech/topics/network-operations.md>), [Statistics](<https://devfeed.tech/topics/statistics.md>)

Tags: [functions](<https://devfeed.tech/tags/functions.md>), [jsonb](<https://devfeed.tech/tags/jsonb.md>), [lateral](<https://devfeed.tech/tags/lateral.md>), [network-operations](<https://devfeed.tech/tags/network-operations.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [psql](<https://devfeed.tech/tags/psql.md>), [replication](<https://devfeed.tech/tags/replication.md>), [statistics](<https://devfeed.tech/tags/statistics.md>), [text](<https://devfeed.tech/tags/text.md>), [unicode](<https://devfeed.tech/tags/unicode.md>), [window-functions](<https://devfeed.tech/tags/window-functions.md>)

## AI overview

The article collects PostgreSQL features that the author and contributors consider useful or easily overlooked. It highlights capabilities including ltree, citext, date arithmetic, jsonb, lateral joins, window functions, custom foreign data wrappers, replication slots, range types, statistics, logical decoding, and psql features.

## Source excerpt

Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn't know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I'm capturing some of the responses here along with some resources many of the features. @listrophy - $ brew postgresql-update database Though personally I prefer Postgres.app ;) @pat_shaugnessy - ltree Pat has a great post that walks through ltree @billyfung - citext A really handy datatype for case insensitive text @eeeebbbbrrrr - date math with intervals I couldn't agree more on this one, working with time in Postgres is the easiest time I've every had @DataMiller - The jsonb datatype and lateral joins I'd argue it's hard to claim now JSONB is a hidden gem, but lateral joins are certain a great one @ideasasylum - row_number() over(partition http://orders.site_id order by orders.created_at) Window functions are definitely a handy feature was my hidden (to me) discovery this week @franckverrot - Index access method, and custom FDWs @jonjensen0 - Set-returning functions and custom aggregate functions can be very helpful. @ascherbaum - psql -x Psql is indeed awesome and can be well tuned @Abstr_ct - The fact that the docs are fantastic and all hidden gems are actually readily available. Oh, and pl/brainfuck obviously @Halpin_IO - Subnetting and network operations @jkatz05 - Replication slots, both physical and logical. They've made setting up replication infinitely easier. And range types. Because they're awesome. @petereisentraut - Unicode table borders @_avichalp Notify/listen @simonw - The fact that GIN indices can make LIKE queries run fast even if the % isn't just at the end of the string @javisantana - it has a statistics system to plan queries that can be used by the user when accuracy does not matt