Solving NULL values are recognized as text-values within dynamic queries in PostgresSQL

I’ve written a dynamic query which is finally executed via EXECUTE my_dynamic_query; The query contained a select list in which two columns should always be NULL. — … stuff before dynamic_select_cols := ‘tbl1.foo, tbl1.bar, null AS theAlias1, null AS theAlias2, tbl2.foo2’; — … stuff after combining the dynamic queries EXECUTE my_dynamic_query;– … stuff before dynamic_select_cols […]

How to install postgres 8.4 on Debian 5 (lenny)

Because I didn’t found this information in one place I’m writing this quick “guide” on how to install PostgreSQL 8.4.x on Debian 5.x “lenny”. Debian lenny stable packages are bundled with Postgres 8.3.x but thanks to Debian Backports the newer Postgres version is available for lenny. Simply do it Add the backports-sources (/etc/apt/sources.list) and update […]

Value too long error in PostgreSQL inside a plpgsql-function

In an application I am working on I recently found errors in the applications log file. Value too long for type character varying(64)Value too long for type character varying(64) Within a function (written with plpgsql) a dynamic query is built and executed via sql-EXECUTE. The error PostgreSQL gives me stated out that the error was […]

Allowing PostgreSQL to use an index when the query uses the LIKE-operator

When enabling search in database its very important to have the appropriate indexes defined to make the queries as fast as possible. If your query looks like this (and the appropriate index is defined) the index can be used by the database (the query planner) even with the LIKE-operator. So fast wildcard-searches are possible … […]

Performance increase when using case-insensitive queries with Symfony, Propel and PostgreSQL

Symfony makes heavy use of Propel. Propel finds objects in the storage container using criteria. If you want to let Propel (okay the database) ignore the case you can set this in the criteria. $c = new Criteria ();   // Allow case sensitive searches $c->setIgnoreCase (true);   // Find all posts which name contains […]