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(dot)klaver(at)aklaver(dot)com