Indexes in PostgreSQL
One thing that most people don't have to worry about in MySQL is case-sensitivity. Unless you're trying to authenticate a user against a database password and don't use MD5 (like you should!), in which case the password is not case sensitive by default.
In PostgreSQL everything is case sensitive. That means that if you have a user "Bob", and you run a query such as:
SELECT * FROM users WHERE username = 'bob';
you won't get a match. You can get around that by using:
SELECT * FROM users WHERE LOWER(username) = LOWER('bob');
However, now PostgreSQL will ignore your regular index on the username column. Fortunately, PostgreSQL will let you create indexes based on expressions. In this case you'd want to use the following index:
CREATE UNIQUE INDEX myindex ON users((LOWER(username));
Cheers!
Print This Post
PostgreSQL-style COALESCE() in PHP
There's a rather handy command in Oracle, PostgreSQL, SQLite, and MySQL (since version 3.23.3) called COALESCE(). Its purpose is rather simple: return the first non-NULL value (If you're used to older versions of MySQL, the IFNULL() is quite similar, if not as flexible). This comes in handy when you want to use fallback values in SELECT statements.
The closest construct in PHP is the ternary operator. Example:
echo ($username ? $username : 'Guest');
And that's sufficient most of the time. However, nesting ternary operators doesn't exactly promote readability and, if you're not sure about operator precedence, is prone to errors.
Implementing the coalesce() function in PHP is rather easy:
/**
* Returns the first non-false argument (equivalent
* to PostgreSQL's or Oracle's function)
*/
function coalesce()
{
for ($i = 0; $i < func_num_args(); $i++)
if (func_get_arg($i))
return func_get_arg($i);
}
That's it already. So what's this good for you ask? Really depends on your needs of course.
For example, say you allow visitors to post comments on your site, but they don't have to specify their name. Instead, it should just say "Guest". if the form variable is "name" you might have code that looks somewhat like this:
$name = coalesce($_POST['name'], 'Guest');
Big deal, right? After all, you could have just used a simple if-statement to see if the name was specified.
Okay, now let's say, the visitor can supply their email address as well. But they don't have to. If they supply the name, they should be known by their name, if they supply an email address, that'll be their name, and if neither is supplied, they'll just be a "Guest". Here's the code:
$name = coalesce($_POST['name'], $_POST['email'], 'Guest');
The nice thing is just that there's no limit on how many parameters you supply to the coalesce() function. It could be two, three, ten, or fifteen parameters. In the web application I use it a lot to check for optional configuration settings. There's the hardcoded application default, to be overridden by the "global" configuration, to be overridden by the "private label" configuration, to be overridden by the sql-based "per-user" configuration. Coalesce() makes that painless — I just pass in all the variables in the order of preference and get back the one proper value.
Print This Post