Conquest and Postgresql 8.3

This is for anyone wanting to use postgersql with conquest. After setting up a new installation with Ubuntu Hardy and intending to use postgresql as the database I came accross the following problem. The most recent postgresql available (8.3) has a number of default behaviour changes that includes casting of non-character types.

Adding images to conquest now produces the message:
***Filter: Unable to query for image record
and the postgresql log has the message:
2008-07-16 09:55:05 EST ERROR: operator does not exist: character >= integer at character 107
2008-07-16 09:55:05 EST HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
2008-07-16 09:55:05 EST STATEMENT: SELECT DICOMImages.ObjectFile FROM DICOMImages, DICOMSeries, DICOMStudies, DICOMPatients WHERE (StudyDate >= 20080623) and DICOMImages.SopInstanc = '1.2.840.113704.1.111.2192.1208497733.2553' and DICOMStudies.PatientID = DICOMPatients.PatientID and DICOMImages.SeriesInst = DICOMSeries.SeriesInst and DICOMSeries.StudyInsta = DICOMStudies.StudyInsta AND DICOMImages.ImagePat = '1042021'

The release notes for 8.3 inludes the following section

Quote

Non-character data types are no longer automatically cast to TEXT (Peter, Tom)

Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. For example, these expressions formerly worked:

substr(current_date, 1, 4)
23 LIKE '2%'

but will now draw "function does not exist" and "operator does not exist" errors respectively. Use an explicit cast instead:

substr(current_date::text, 1, 4)
23::text LIKE '2%'

(Of course, you can use the more verbose CAST() syntax too.) The reason for the change is that these automatic casts too often caused surprising behavior. An example is that in previous releases, this expression was accepted but did not do what was expected:

current_date < 2017-11-17

This is actually comparing a date to an integer, which should be (and now is) rejected — but in the presence of automatic casts both sides were cast to text and a textual comparison was done, because the text < text operator was able to match the expression when no other < operator could.

Types char(n) and varchar(n) still cast to text automatically. Also, automatic casting to text still works for inputs to the concatenation (||) operator, so long as least one input is a character-string type.

Display More

So in this case StudyDate needs to be explicitly cast as an integer, and I suspect there are many other places where it will fail.
My old installation using postgresql 8.1 is still working OK.

Thanks Marcel,
I have an export filter in the form
ExportFilter0 = StudyDate >= 20080815
where a script updates the date every day which was OK up until postgres 8.3.
I tried this setup on another machine and could not reproduce the problem, and have since changed to mysql on my production machine so I'll have to investigate further.

One thing I did notice was that if the setting in postgresql.conf of standard_conforming_strings is set to off you get the following types of warnings from conquest
WARNING: nonstandard use of \\ in a string literal
LINE 1: ...3234.00', '1', 'MONOCHROME2', '128', '128', '16', 'DERIVED\\...
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.

If you set standard_conforming_strings to on which the release notes say will be standard in the future, then the warning goes away but modality strings like NM\CT are not handled correctly until the DoubleBackSlashToDB is changed to 0.