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
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
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
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.
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 …
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: