Re: Change from 9.6 to 11?

On Thu, Dec 20, 2018 at 10:12 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
> --
Chuck Martin
Avondale Software