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?
Continue reading How to limit rows to at most N per category
This question appeared couple of times on irc, so I figured I can do a blogpost about it.
Continue reading How to make sure there is always at least one “sub" record?
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.
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
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
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
Is it possible to add “rank" column, that will increment whenever t changed?
Continue reading Tips n' tricks – rank on changes
Let's assume you have very simple table with users:
# \d 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
"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
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:
Continue reading Counting number of distinct elements