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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx
