Change from 9.6 to 11?

From: Chuck Martin <clmartin(at)theombudsman(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Change from 9.6 to 11?
Date: 2018-12-20 20:35:23
Message-ID: CAFw6=U1jYhUD+E7u47Jxhs6Ta9-EkhFxcJnZPHvbj1OW-X9ynw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Brannen 2018-12-20 20:54:39 RE: Watching for view changes
Previous Message Alvaro Herrera 2018-12-20 20:25:14 Re: Multiple LDAP Servers for ldap Authentication