Upping One’s PostGreSQL-fu

Learnt a lot this season.

Photo by Geran de Klerk on Unsplash

using like vs ilike vs ~ vs ~~ vs ~~* vs in vs =

using replace — we have a language in and of itself like this!

• or, if necessary: regexp_replace and even, split_part — (who knew PostGreSQL had so many dedicated string manipulation functionalities!)

using begin and commit

using returning — get feedback when you need it.

using aggregate functions with group by : min , max , sum , count , string_agg , array_agg , xmlagg , avg , every , bool_or

• my only gripe, no first , or last ! — but you can write your own aggregate function like so.
• alternatively, use lead or lag via window functions: think group by on steroids with over and partition by as keywords. This Medium article has some nice diagrams.

finally learnt to use join : putting your where s before the join rather than after.

Other useful things that are not new/you can just use off the shelf:

order by — always use this! otherwise it’ll be a pain to compare outputs at a glance from two or more instances.

setval and nextval

select pg_size_pretty(pg_database_size(--your database name--))

select pg_size_pretty(pg_total_relation_size(--your table name--))

pg_dump : dump your entire database contents for archival — use flags for various modes!

pg_isready : test your database connection readiness

List of Common DB migrations:

• creating a table
• setting a primary key
• setting a foreign key
• setting is nullable
• setting a default value

This is a curated but growing list.

Updates:

Last Edit: 11 Oct 2020. Addedpg_dump, pg_isready