{"id":3743,"date":"2018-02-03T11:55:01","date_gmt":"2018-02-03T10:55:01","guid":{"rendered":"https:\/\/monodes.com\/predaelli\/?p=3743"},"modified":"2018-02-03T11:55:01","modified_gmt":"2018-02-03T10:55:01","slug":"postgres-hidden-gems-craig-kerstiens","status":"publish","type":"post","link":"https:\/\/monodes.com\/predaelli\/2018\/02\/03\/postgres-hidden-gems-craig-kerstiens\/","title":{"rendered":"Postgres hidden gems &#8211; Craig Kerstiens"},"content":{"rendered":"<blockquote><p>citext<\/p><\/blockquote>\n<p>I&#8217;ve always been fond of PostgreSQL, now this <em><a href=\"http:\/\/www.craigkerstiens.com\/2018\/01\/31\/postgres-hidden-gems\/?imm_mid=0fb066&amp;cmp=em-prog-na-na-newsltr_20180203\">Postgres hidden gems &#8211; Craig Kerstiens<\/a><\/em> shows its smart features even more!<\/p>\n<p>There are many interesting features of Postgresql that I didn&#8217;t knew, as I haven&#8217;t actually used it for a while<\/p>\n<p><!--more--><\/p>\n<blockquote>\n<div class=\"entry-content\">\n<p>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\u2019t 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\u2019m capturing some of the responses here along with some resources many of the features.<\/p>\n<ul>\n<li><a href=\"https:\/\/www.twitter.com\/listrophy\">@listrophy<\/a> \u2013 <code class=\"\" data-line=\"\">$ brew postgresql-update database<\/code>\n<ul>\n<li><em>Though personally I prefer Postgres.app \ud83d\ude09<\/em><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/pat_shaugnessy\">@pat_shaugnessy<\/a> \u2013 ltree\n<ul>\n<li>Pat has a great post that walks through <a href=\"http:\/\/patshaughnessy.net\/2017\/12\/13\/saving-a-tree-in-postgres-using-ltree\">ltree<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/billyfung\">@billyfung<\/a> \u2013 citext\n<ul>\n<li>A really handy datatype for case insensitive text<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/eeeebbbbrrrr\">@eeeebbbbrrrr<\/a> \u2013 date math with intervals\n<ul>\n<li>I couldn\u2019t agree more on this one, <a href=\"http:\/\/www.craigkerstiens.com\/2017\/06\/08\/working-with-time-in-postgres\/\">working with time in Postgres<\/a> is the easiest time I\u2019ve every had<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/DataMiller\">@DataMiller<\/a> \u2013 The jsonb datatype and lateral joins\n<ul>\n<li>I\u2019d argue it\u2019s hard to claim now JSONB is a hidden gem, but <a href=\"https:\/\/blog.heapanalytics.com\/postgresqls-powerful-new-join-type-lateral\/\">lateral joins<\/a> are certain a great one<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/ideasasylum\">@ideasasylum<\/a> \u2013 row_number() over(partition <a href=\"http:\/\/orders.site_id\">http:\/\/orders.site_id<\/a> order by orders.created_at)\n<ul>\n<li><a href=\"https:\/\/robots.thoughtbot.com\/postgres-window-functions\">Window functions<\/a> are definitely a handy feature was my hidden (to me) discovery this week<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/franckverrot\">@franckverrot<\/a> \u2013 Index access method, and custom FDWs<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/jonjensen0\">@jonjensen0<\/a> \u2013 <a href=\"https:\/\/www.postgresql.org\/docs\/9.5\/static\/functions-srf.html\">Set-returning functions<\/a> and custom aggregate functions can be very helpful.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/ascherbaum\">@ascherbaum<\/a> \u2013 psql -x\n<ul>\n<li>Psql is indeed awesome and can be <a href=\"http:\/\/www.craigkerstiens.com\/2013\/02\/13\/How-I-Work-With-Postgres\/\">well tuned<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/Abstr_ct\">@Abstr_ct<\/a> \u2013 The fact that the docs are fantastic and all hidden gems are actually readily available. Oh, and pl\/brainfuck obviously<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/Halpin_IO\">@Halpin_IO<\/a> \u2013 Subnetting and network operations<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/jkatz05\">@jkatz05<\/a> \u2013 Replication slots, both physical and logical. They\u2019ve made setting up replication infinitely easier. And range types. Because they\u2019re awesome.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/petereisentraut\">@petereisentraut<\/a> \u2013 Unicode table borders<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/_avichalp\">@_avichalp<\/a> Notify\/listen<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/simonw\">@simonw<\/a> \u2013 The fact that <a href=\"https:\/\/www.citusdata.com\/blog\/2017\/10\/17\/tour-of-postgres-index-types\/\">GIN indices<\/a> can make LIKE queries run fast even if the % isn\u2019t just at the end of the string<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/javisantana\">@javisantana<\/a> \u2013 it has a statistics system to plan queries that can be used by the user when accuracy does not matter, for example, use \u201cexplain select * from table\u201d to replace count() or use \u201c_postgis_selectivity\u201d to know how many points fall into a bbox.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/l_avrot\">@l_avrot<\/a> \u2013 The fact that we can use vim editor in psql<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/mashd\">@mashd<\/a> \u2013 Logical decoding for change data capture.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/4thdoctor_scarf\">@4thdoctor_scarf<\/a> \u2013 the MVCC. If I had a penny per each time I\u2019ve explained how really works, I\u2019ll be a millionaire now \ud83d\ude42<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/docteur_klein\">@docteur_klein<\/a> \u2013 \\timing in psql<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/thibaut_barrere\">@thibaut_barrere<\/a> \u2013 From times to times I find foreign data wrapper with CSV files very helpful (&amp; easy to setup with Ruby\u2019s Sequel library) <a href=\"https:\/\/gist.github.com\/thbar\/0093ee54c5a61aa5a0c5a4737fc3bd45\">https:\/\/gist.github.com\/thbar\/0093ee54c5a61aa5a0c5a4737fc3bd45<\/a><\/li>\n<li><a href=\"https:\/\/www.twitter.com\/steve_touw\">@steve_touw<\/a> \u2013 <a href=\"http:\/\/www.craigkerstiens.com\/2016\/09\/11\/a-tour-of-fdws\/\">Foreign data wrappers<\/a><\/li>\n<li><a href=\"https:\/\/www.twitter.com\/roimartinez_gis\">@roimartinez_gis<\/a> \u2013 Clearly aggregate functions make live very simple \ud83d\ude42 .<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/whalesalad\">@whalesalad<\/a> \u2013 select where datetime &gt; yesterday and other natural language time queries.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/pwramsey\">@pwramsey<\/a> \u2013 At the hacker level: hooks. So many cool hooks, and finding them, a bit of an easter egg hunt.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/pwramsey\">@pwramsey<\/a> \u2013 At the user level: the quality and breadth of tsearch still feels radically under appreciated; same for ranges.<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/JohnByrne1978\">@JohnByrne1978<\/a> \u2013 PostGIS \/ PgRouting<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/peterbe\">@peterbe<\/a> \u2013 psql -l<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/jbrancha\">@jbrancha<\/a> \u2013 In psql, setting \u2018\\x auto\u2019 so that wide table results get displayed vertically!<\/li>\n<li><a href=\"https:\/\/www.twitter.com\/westermanndanie\">@westermanndanie<\/a> \u2013 <code class=\"\" data-line=\"\">\\watch<\/code><\/li>\n<\/ul>\n<p>Well that was quite the list. And I\u2019m sure we\u2019ve only scratched the surface. Have something not on the list that you feel like classifies as a hidden gem? <a href=\"https:\/\/www.twitter.com\/craigkerstiens\">Lets hear about it<\/a><\/p>\n<\/div>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p class=\"excerpt\">citext I&#8217;ve always been fond of PostgreSQL, now this Postgres hidden gems &#8211; Craig Kerstiens shows its smart features even more! There are many interesting features of Postgresql that I didn&#8217;t knew, as I haven&#8217;t actually used it for a while<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"https:\/\/monodes.com\/predaelli\/2018\/02\/03\/postgres-hidden-gems-craig-kerstiens\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[72],"tags":[201],"class_list":["post-3743","post","type-post","status-publish","format-standard","hentry","category-documentations","tag-postgres"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6daft-Yn","jetpack-related-posts":[{"id":11818,"url":"https:\/\/monodes.com\/predaelli\/2024\/07\/27\/speeding-up-index-creation-in-postgresql\/","url_meta":{"origin":3743,"position":0},"title":"Speeding up index creation in PostgreSQL","author":"Paolo Redaelli","date":"2024-07-27","format":false,"excerpt":"In this blog we are talking about indexes. Speeding up index creation in PostgreSQL is one of the main topic. Source: Speeding up index creation in PostgreSQL","rel":"","context":"In &quot;Tricks&quot;","block_context":{"text":"Tricks","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/tricks\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":8922,"url":"https:\/\/monodes.com\/predaelli\/2021\/11\/18\/an-introduction-to-the-pg_auto_failover-project\/","url_meta":{"origin":3743,"position":1},"title":"An introduction to the pg_auto_failover project","author":"Paolo Redaelli","date":"2021-11-18","format":"link","excerpt":"\u201cPostgres is awesome, makes developping my application so much easier, it solves so many problems for me!\u201d and the next step \u201cso, how do I run Postgres in Production?\u201d. If you\u2019re not sure how to bridge that gap yourself, how to deploy your first production system with automated failover, then\u2026","rel":"","context":"In &quot;Senza categoria&quot;","block_context":{"text":"Senza categoria","link":"https:\/\/monodes.com\/predaelli\/category\/senza-categoria\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10679,"url":"https:\/\/monodes.com\/predaelli\/2023\/08\/09\/sqledge\/","url_meta":{"origin":3743,"position":2},"title":"SQLedge","author":"Paolo Redaelli","date":"2023-08-09","format":"link","excerpt":"SQLedge: Replicate Postgres to SQLite on the Edge - https:\/\/github.com\/zknill\/sqledge","rel":"","context":"In &quot;Software&quot;","block_context":{"text":"Software","link":"https:\/\/monodes.com\/predaelli\/category\/software\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":9759,"url":"https:\/\/monodes.com\/predaelli\/2022\/10\/24\/6-popular-postgresql-cli-libraries\/","url_meta":{"origin":3743,"position":3},"title":"6 Popular PostgreSQL CLI Libraries","author":"Paolo Redaelli","date":"2022-10-24","format":false,"excerpt":"6 Popular PostgreSQL CLI Libraries pgcli - Postgres CLI with autocompletion and syntax highlighting pgsh - Branch your PostgreSQL Database like Git psql - The built-in PostgreSQL CLI client psql2csv - Run a query in psql and output the result as CSV nancy - The Nancy CLI is a unified\u2026","rel":"","context":"In &quot;Documentations&quot;","block_context":{"text":"Documentations","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":10940,"url":"https:\/\/monodes.com\/predaelli\/2023\/11\/03\/versioning-data-in-postgres-testing-a-git-like-approach-specfy\/","url_meta":{"origin":3743,"position":4},"title":"Versioning data in Postgres? Testing a git like approach &#8211; Specfy","author":"Paolo Redaelli","date":"2023-11-03","format":false,"excerpt":"Versioning data in Postgres? Testing a git like approach - Specfy is fashinating but I think that most of the time these two proposed alternatives fit most of the needs: In-Table versioning, the Wordpress way of doing thing. Add a a column version (or modify date) and SELECT the maximum\u2026","rel":"","context":"In &quot;Tricks&quot;","block_context":{"text":"Tricks","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/tricks\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":9598,"url":"https:\/\/monodes.com\/predaelli\/2022\/09\/14\/choosing-a-postgres-primary-key\/","url_meta":{"origin":3743,"position":5},"title":"Choosing a Postgres Primary Key","author":"Paolo Redaelli","date":"2022-09-14","format":"link","excerpt":"\u00a0Choosing a Postgres Primary Key Choosing a Postgres Primary Key 2022-09-08 \u2022 24 minute read VictorGuest Author Primary keys are important. They uniquely identify rows of data in tables, and make it easy to fetch data. The job of a database is to archive and recall data and you're going\u2026","rel":"","context":"In &quot;Documentations&quot;","block_context":{"text":"Documentations","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/posts\/3743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/comments?post=3743"}],"version-history":[{"count":0,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/posts\/3743\/revisions"}],"wp:attachment":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/media?parent=3743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/categories?post=3743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/tags?post=3743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}