On Thu, Dec 20, 2018 at 10:12 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 12/20/18 5:51 PM, Chuck Martin wrote: > > Please reply to list also. > Ccing list. > > > > > > > On Thu, Dec 20, 2018 at 7:56 PM Adrian Klaver <adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > 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? > > > > > > I get 91 copies of the record. One for each record in the usr table. > > But do the event.InsBy, event.InsBy::int AS InsByInt values match each > other? > > Just had a thought, what if you join just the event and usr tables on: > > event.InsBy::int = usr.Usr_pkey > > Trying to determine whether your suspected culprit really is the culprit. Thanks, Adrian. This led me to the problem. The data in InsBy was invalid. That is to say, a join wasn’t possible because no record exists with that primary key. Not sure how that occurred, but now I know why. Had I anticipated this might happen, I would have used an outer join. I appreciate your help solving this minor, but annoying, issue. > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Chuck Martin Avondale Software
