On 12/20/18 12:35 PM, Chuck Martin wrote:
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!
So if in the WHERE you leave out the:
AND event.InsBy::int = usr.Usr_pkey
and in the SELECT you add:
event.InsBy, event.InsBy::int AS InsByInt
what do you see?
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
