Re: Change from 9.6 to 11?

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@aklaver.com