From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Chuck Martin <clmartin(at)theombudsman(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Change from 9.6 to 11? |
Date: | 2018-12-21 03:12:13 |
Message-ID: | 37b96fd6-c5f5-e1d3-6284-6f1521a02638@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rijo Roy | 2018-12-21 12:00:07 | Re: Wals not recycling |
Previous Message | George Neuner | 2018-12-21 02:28:13 | Re: Watching for view changes |