This question appeared couple of times on irc, so I figured I can do a blogpost about it.
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
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.
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.
I got asked this: having this table:
# SELECT * FROM a ORDER BY d; t | d ---+---- O | 1 O | 2 O | 3 M | 4 M | 5 M | 6 M | 7 O | 8 O | 9 O | 10 I | 11 I | 12 I | 13 (13 ROWS)
Is it possible to add “rank" column, that will increment whenever t changed?
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 …
When I was working for one of customers we found some strange thing. We needed to found number of distinct sessions per day. Table layout was very simple: