SQL best practices – don’t compare count(*) with 0

Every now and then I see something like this:

select u.* from users u
where 0 = (select count(*) from addresses a where a.user_id = u.id);

and it kinda pains me. So figured, I'll write about what is the problem with it, and how to avoid such constructs.

Continue reading SQL best practices – don't compare count(*) with 0

How can I send mail or HTTP request from database?

This question happens every now and then in one of PostgreSQL support places.

Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY.

But it occurred to me, that while I know how to do it, I don't think I actually did it. It being: handle listen/notify in real life code. So let's try. I will not be writing actual email sending or http requesting, but will make sure that my program will get, from database, information when NOTIFY happens. How hard could it be?

Continue reading How can I send mail or HTTP request from database?

Changes on pgdoc.link

Based on checking logs, and my own personal needs I added more categories of keywords to pgdoc.link:

This brought the total number of known keyword to 2410.

As a side note – while I generally like PostgreSQL docs, state of contrib module documentation is …, well, challenging. Every doc has its own approach to listing stuff. That was “fun" to work on, and it's the main reason why I'm sure not all functions/modules are handled. If you will notice something that isn't – please let me know, I'll do my best to add it.

Grouping data into array of sums – fun with custom aggregates

Was asked recently about optimization of interesting case. There was table like:

=$ CREATE TABLE input_data (
    category_id        INT8,
    object_id          INT8,
    interaction_ts     timestamptz,
    interaction_type    TEXT,
    interaction_count  INT4
);

And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and timestamp truncated to hour.

Basically, storing somewhere result of:

=$ select
    category_id,
    object_id,
    date_trunc( 'hour', interaction_ts ) as ts,
    sum(interaction_count) filter (where interaction_type = 'a') as a_count,
    sum(interaction_count) filter (where interaction_type = 'b') as b_count
from
    input_data
group by 1, 2, 3;

While talking about optimizations, one idea that came was to store whole day of counts in single row, as array. So the resulting count table would be:

=$ create table results (
    category_id      int8,
    object_id        int8
    interaction_day  date,
    a_counts         int4[],
    b_counts         int4[]
);

Where a_counts, and b_counts would always have 24 elements, one for each hour.

Now, how to roll it up like this?

Continue reading Grouping data into array of sums – fun with custom aggregates

Waiting for PostgreSQL 18 – Add SQL function array_reverse()

On 1st of November 2024, Michael Paquier committed patch:

Add SQL function array_reverse()
 
This function takes in input an array, and reverses the position of all
its elements.  This operation only affects the first dimension of the
array, like array_shuffle().
 
The implementation structure is inspired by array_shuffle(), with a
subroutine called array_reverse_n() that may come in handy in the
future, should more functions able to reverse portions of arrays be
introduced.
 
Bump catalog version.
 
Author: Aleksander Alekseev
Reviewed-by: Ashutosh Bapat, Tom Lane, Vladlen Popolitov
Discussion: https://postgr.es/m/CAJ7c6TMpeO_ke+QGOaAx9xdJuxa7r=49-anMh3G5476e3CX1CA@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add SQL function array_reverse()

New way to search PostgreSQL documentation

PostgreSQL documentation is, generally speaking, great. But it isn't the easiest thing to search in. Over the years I memorized urls to certain docs, but there is a limit to it.

What's more, there are certain inconsistencies. For example – most pages that describe program have name that starts with app-. But not all. Some programs have names that start with pg_, and some don't have this _ thing. Their docs usually get rid of _ in file name, but, again, not always, sometimes it's changed to –.

I've been aware of all these things, and even tried to look for a way to fix it in docs, but never could get around to learn docbook.

So, figured I can provide a tool that will search the way I think it should…

Continue reading New way to search PostgreSQL documentation

Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints

On 17th of September 2024, Peter Eisentraut committed patch:

Add temporal FOREIGN KEY contraints
 
Add PERIOD clause to foreign key constraint definitions.  This is
supported for range and multirange types.  Temporal foreign keys check
for range containment instead of equality.
 
This feature matches the behavior of the SQL standard temporal foreign
keys, but it works on PostgreSQL's native ranges instead of SQL's
"periods", which don't exist in PostgreSQL (yet).
 
Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}
are not supported yet.
 
(previously committed as 34768ee3616, reverted by 8aee330af55; this is
essentially unchanged from those)
 
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

Continue reading Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints