Archive for the ‘

PostgreSQL

’ Category

PostgreSQL and network operators

I am working on a couple of projects that require the use of PostgreSQL and I really like dealing with it. Full of features that are sometimes quite usefull. One of them is the <<= network operator.

is contained within or equals – inet ’192.168.1/24′ <<= inet ’192.168.1/24′

This way one can check if an ip is in the range of a given network. Pretty simple, yeah! But I had the need to query ip address that are not in a specific network range :) . Well there is no opposite operator – at least I couldn’t find one. So what I did is is the following:


SELECT COUNT(*) FROM ips WHERE  CASE WHEN ip <<= inet '192.168.1/24' THEN false ELSE true END

Adding a number of random generated unique strings into db

Today I accidentally got to a piece of code I have written long ago and really got surprised from me. I have created a not conventional solution to a interesting problem. Imagine you have to add a bunch of rows into a db table, for example promo codes, that has column holding randomly generated strings. The strings should be unique of course, so we have unique key in the db! So instead of generating the string, checking if it is already in the table and if not -> adding it to the table, I have just written this:

$j = 0;
for($i=0; $i<$count; $i++)
{
	while(true)
	{
		try
		{
			$code = str_makerand();
			$codeID = $this->addItem(
				$this->tableNameCodes,
				array('code'),
				array($code)
			);

			break;
		}
		catch(Exception $e){
			$j++;
			if($j=10) break;
		}
	}
}