How to limit rows to at most N per category – fix

A bit ago I wrote a blog post that was supposed to show how to keep number of rows in table to N per some category.

Unfortunately, I overlooked a problem related to concurrency.

Continue reading How to limit rows to at most N per category – fix

How to limit rows to at most N per category

The question was asked relatively recently on irc. And it proved to be non-trivial.

Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands?

Let's see.

Continue reading How to limit rows to at most N per category

Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.

On 5th of February, Tom Lane committed patch:

Add num_nulls() and num_nonnulls() to count NULL arguments.
 
An example use-case is "CHECK(num_nonnulls(a,b,c) = 1)" to assert that
exactly one of a,b,c isn't NULL.  The functions are variadic, so they
can also be pressed into service to count the number of null or nonnull
elements in an array.
 
Marko Tiikkaja, reviewed by Pavel Stehule

Continue reading Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.

How I Learned to Stop Worrying and Love the Triggers

Some people are afraid of triggers.

Reasons for this are not really understandable for me, but I guess it stems from the fact that these are usually application developers, and not database admins. Or they encountered some kind of problem with triggers, and now they tend to think that triggers are inherently evil.

But they are not.

As virtually anything, triggers have some benefits, and some drawbacks. With a bit of thinking you can use them to do really cool things. But first you have to understand what exactly trigger is, how it works, and when to use which kind.

Continue reading How I Learned to Stop Worrying and Love the Triggers

Pagination with fixed order

Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn't get page count, and easily jump to page number N.

I did some thinking on the subject, and I think I found a way to make it all work. Quite fast. And with not big overhead. Let me show you.

Continue reading Pagination with fixed order

Tips N’ Tricks – count of all and just some

Let's assume you have very simple table with users:

# \d users
                           Table "public.users"
  Column   |  Type   |                     Modifiers
-----------+---------+----------------------------------------------------
 id        | integer | not null default nextval('users_id_seq'::regclass)
 username  | text    | not null
 is_active | boolean | not null default true
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE, btree (username)

And you'd like to count all users, and know how many of them are active …

Continue reading Tips N' Tricks – count of all and just some