|
PGX: Build Postgres Extensions with Rust — A framework for building Postgres extensions with Rust rather than the C or C++ you might be more used to. Why is this a good thing? Rust is becoming pretty popular in more cutting edge circles lately and the language’s safety features make it a compelling choice.
ZomboDB
|
|
JSONB: A Container of Types — Bruce reminds us that JSONB columns don’t just have to hold collections like arrays or maps/hashes, you can directly store strings, numbers and booleans too.
Bruce Momjian
|
Achieve High Availability with PostgreSQL [Whitepaper] — Critical business applications require availability of their backend database cluster. Discover how to setup & deploy enterprise-grade High Availability of up to four 9s. Learn the features and benefits in the Highly Available Postgres Clusters Whitepaper.
2ndQuadrant PostgreSQL Products
|
|
How to Find a Postgres Bug Born in 1997 — The tale of discovering a bug hidden away inside Postgres’s documentation or circle type (depending on your point of view) for the past 23 years.
David Zhang
|
|
How to SCRAM with pgBouncer — Not only can you see how pgBouncer (the connection pooler) uses SCRAM authentication but also what SCRAM authentication is and how it works.
Jonathan S. Katz
|
|
What Are Postgres Templates? — We’ve covered this in one of our ‘tips of the week’ before, but if you’re unaware of what template databases are and how they’re used, this is a quick primer.
Angelico de los Reyes
|
|
Foreign Data Wrappers: Postgres's Secret Weapon? — Foreign data wrappers (often called FDWs, for short) lets you query remote databases directly from your PostgreSQL instance and here’s how that works alongside Splitgraph, a toolkit for building and working with “data images”.
Artjoms Iškovs (Splitgraph)
|
supported by 
💡 Tip of the Week
RANK and DENSE_RANK for ranking orders
Let's say we've been taking votes of people's favorite database systems. Here's our schema:
|
|
CREATE TABLE databases (name TEXT, votes INT);
INSERT INTO databases VALUES
('Postgres', 10000),
('MySQL', 4522),
('SQLite', 9500),
('MongoDB', 4522),
('Oracle', 4580),
('Redis', 9500);
|
|
We want to get the results in order along with a rank of where each database stands:
|
|
SELECT row_number() OVER (ORDER by votes DESC), * FROM databases;
|
|
It doesn't seem very fair to me that Redis gets ranked #3 despite having the same number of votes as SQLite! Ranks in the real world often take note of equivalent/tied scores and give the same rank to similarly scored items.
Enter RANK and DENSE_RANK, two of several window functions:
|
|
SELECT DENSE_RANK() OVER (ORDER by votes DESC), * FROM databases;
SELECT RANK() OVER (ORDER by votes DESC), * FROM databases;
|
|
The difference is subtle but RANK gives us a more traditional rank approach where if there are two second place entries, there's no longer a third place entry. DENSE_RANK, however, ensures there are continguous rankings even if there are duplicates.
Try out this tip in PopSQL. PopSQL is a SQL editor built for teams. Think “Figma for SQL”. A free Premium trial awaits Postgres Weekly readers (no credit card required).
|
|
|
