I hope someone here can see something that eludes me. I've recently moved a database from PostgreSQL 9.6 to 11, and there are a few oddities. The following select statement returns zero rows when it should return one. This is one of a small number of records that exist, but are not returned by the query. When I include the main table, event, and any one of the associated tables, the record is returned, but no record is returned with the entire statement. All the primary keys (_pkey) and foreign keys (_fkey) are integers. The field I suspect as the possible culprit, event.InsBy, is a character column I'm converting to do a lookup on a primary key (integer): event.InsBy::int = usr.Usr_pkey. Maybe PG 11 doesn't recognize the same syntax for cast as PG 9.6? Or maybe I'm overlooking something else basic. Thanks for reading!
SELECT event.Event_pkey,ombcase.CaseName,status.StatusID,eventtype.EventTypeID,event.datetime AT TIME ZONE 'US/Eastern',event.endtime AT TIME ZONE 'US/Eastern',event.EventDeadline,eventtype.EventIsDeadline,eventtype.OnCalendar,event.EventConfirmed,event.Description,event.PrivateNote,primaryresp.PrimaryrespID,event.IsPrivate,event.Hours,event.EventDone,event.EventDoneDate,event.Importance,event.Urgency,event.InCourt,event.LeadTime,doc.DocID,doctype.DocTypeID,project.ProjectName,usr.UserID,eventtype.Hours,event.Active,event.EditNum,event.InsBy,event.InsDateTime,event.ModBy,event.ModDateTime,event.Case_fkey,event.Eventtype_fkey,event.Project_fkey,event.Doc_fkey,event.Primaryresp_fkey,project.Project_pkey,primaryresp.PrimaryrespHolidayGroup,primaryresp.Usr_pkey,backup.Usr_pkey,doctype.Doctype_pkey,primaryresp.Primaryrespbackup_fkey,ombcase.Status_fkey,event.Parentevent_fkey,event.EventPostRemand
FROM event,ombcase,status,eventtype,primaryresp,doc,doctype,project,usr,backup
WHERE event.Case_fkey = Case_pkey
AND event.Eventtype_fkey = Eventtype_pkey
AND event.Project_fkey = Project_pkey
AND event.Primaryresp_fkey = primaryresp.Usr_pkey
AND event.Doc_fkey = Doc_pkey
AND Doctype_fkey = Doctype_pkey
AND usr.Backup_fkey = backup.Usr_pkey
AND ombcase.Status_fkey = status.Status_pkey
AND event.InsBy::int = usr.Usr_pkey
AND event.Event_pkey = 1060071
ORDER BY EventDone, DateTime DESC
Chuck Martin
Avondale Software
