When using Sphinx with Postgresql you have to be a little careful. Other than the search binary requiring the MySQL libraries even when you are not using MySQL, I just encountered another issue with Postgresql where it doesn’t look at postgresql boolean attributes correctly; in this case sphinx thinks of all values as true. To get around this, until the fix is released, you can CAST the boolean field as integer. So, in your sql_query you would do something like the following.

bc[sql].
sql_query = SELECT id, title, DATE_PART(‘epoch’, created_on) AS created_on, CAST FROM mytable

The above SQL also demonstrates the DATE_PART function. The sphinx documentation shows UNIX_TIMESTAMP, which is a MySQL only function, as the function to use when indexing sql_attr_timestamp.

Back to blog...