Posts about Postgres Tutorials
Working with Money in Postgres
Elizabeth has a primer for working with money in Postgres including what data type to choose, storing currency, and some sample functions.
Top 10 Postgres Management Tasks
The must know Postgres management tasks to look at for any scale. Plus a bonus image included showing the recent and upcoming Postgres release schedule.
Postgres Subquery Powertools: CTEs, Materialized Views, Window Functions, and LATERAL Join
Wondering when to use a Materialized View or a CTE? Elizabeth has summaries, example queries, and comparisons for the most popular subquery tools.
10 min readTags and Postgres Arrays, a Purrrfect Combination
Are you using tags in your database with some of your main database properties? Paul reviews some of the ways to store tags in a database from basic relational models to text arrays. He provides some performance tests, sample queries, and guidance on choosing the best path.
7 min readWorking with Time in Postgres
A primer on working with time in Postgres. Covers data types, query formats, intervals, overlaps, range types, indexing, and roll ups.
Easy PostgreSQL Time Bins
Paul has some fast and easy tricks to show you how to get time series data into nice reportable data charts. Using functions like floor(), generate_series(), width_bucket(), and date_bin() you can bin your data in groups any way you like and retrieve charts in a flash.
7 min readIntroduction to Postgres Backups
A high level overview of the backup options in Postgres. Including pg_dump, pg_basebackup, and pgBackRest.
9 min readGeocoding with Web APIs in Postgres
Jacob walks you through the steps on how to set up a geocoder with the US census geocoding API inside your database with a plpython function and triggers.
3 min readPostgres Raster Query Basics
Paul takes us through how rasters are stored and queried in Postgres/PostGIS. He gives us the must have tips for dealing with rasters inside a database using digital elevation mode (DEM) examples.
6 min readSQL Tricks for More Effective CRUD
Level up your SQL skills with this quick tutorial of some simple and not-so-simple create, read, update, and delete (CRUD) statements.
7 min readUsing Postgres FILTER
Craig dives into using FILTER and shows a query example using CTEs, CASE WHEN, and FILTER.
5 min readWill Postgres Use My Index? Hypothetical Indexing for Postgres
HypoPG is an extension to create hypothetical indexes and test if Postgres will use them. We think this tool is a good one to use in combination with our other favorites like EXPLAIN and pg_stat_statements.
3 min readIntro to Postgres Custom Data Types
A quick primer on the two main ways to create custom data types in PostgreSQL - DOMAINs and user-defined custom data types. Get some quick examples and learn which tools are recommended.
Postgres support for JSON is 10 years old!
JSON in Postgres is now 10 years old. Chris recently published a JSON tutorial and offers some thoughts on the past and future of JSON, and what developers need to be aware of.
5 min readPostgres Strings to Arrays and Back Again
Time to have some fun with strings and arrays. Learn how to separate strings into arrays, find array size, and turn comma separated lists into individual rows.
4 min readPercentage Calculations Using Postgres Window Functions
Learn how to do percentage calculations in one-pass. Sample data and queries for basic use of window functions.
3 min readPostgres Insider Terminology
If you've ever wondered - What is a tuple? What is a Postgres page? What's the difference between a record and a row? What is a relation? What's an array? What is TOAST? This post is for you! These are terms you hear all the time in the Postgres world. This post breaks down what these terms mean and how they are used.
7 min readData Loading in Postgres for Newbies
This post covers many ways to get data in Postgres including csv, json, backup files, foreign data wrappers, and generating data with queries. Examples include simple steps you can copy and reuse.
Postgres Databases and Schemas
Instances, database, and schemas are all ways to talk about your database. We have a deep dive into understanding these core fundamentals and what they really mean.
5 min readA Look at Postgres 15: MERGE Command with Examples
We’re excited about the MERGE command in PostgreSQL 15. We have some ideas about using it for data loading with an example use case for remote sensors.
4 min read
