PostgreSQL 8.3 Upgrade Tip for Custom SQL Queries
Problem. After upgrading to PostgreSQL 8.3, some custom SQL queries may generate errors instead of running. An example of an error message you might observe:
Query 'Physical Exam Query' has errors
java.sql.SQLException: ERROR: operator does not exist: character varying = integer
If your query is simple enough for viewing in the
Query Designer:
- View your query in the Query Designer.
- Save your query. The Query Designer will make the adjustments necessary for compatibility with PostgreSQL 8.3 automatically.
- Your query will now run instead of generating an error message.
2. Use the Source Editor. If your query is too complicated for viewing in the Query Designer:
- Open it in the Source Editor.
- In the query editor, add single quotes around numbers so that they will be saved appropriately. For example, change
WHERE "Physical Exam".ParticipantId.ParticipantId=249318596
to:
WHERE "Physical Exam".ParticipantId.ParticipantId='249318596'
- Your query will now run instead of generating an error message.
Cause. As of LabKey Server v9.1, the Query Designer uses column types in deciding how to save comparison values. In versions of LabKey Server pre-dating v9.1, an entry such as 1234 became 1234 regardless of whether the column type was string or numeric. In LabKey Server v9.1, the Query Designer saves 1234 as '1234' if appropriate. Older queries need to be resaved or edited manually to make this change occur.
