From: Erik Wienhold <ewie@ewie.name> Sent: Wednesday, July 5, 2023 5:25 PM To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org> Subject: Re: SQL is fetching results BLANK checks but the data has no BLANKS
> On 05/07/2023 23:10 CEST M Sarwar <sarwarmd02@outlook.com> wrote: > > Data in the database is uploaded using python. > > I have tried to test the same results by creating a sample table where I > inserted some sample data. In my sample test, I do not see any discrepancies. > > ------------------------------ > From: M Sarwar <sarwarmd02@outlook.com> > Sent: Wednesday, July 5, 2023 5:01 PM > To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org> > Subject: SQL is fetching results BLANK checks but the data has no BLANKS > > SELECT TEST_NUMBER > FROM BRONX.TEST_TEST_DETAILS_ALL_MCM > WHERE LEFT(TEST_DESCRIPTION, 1) = ' ' > ; > The above SQL is fetching lot of data but when I take closer look at the data > in the table TEST_TEST_DETAILS_ALL_MCM, I see no prefixed BLANKS in the column > TEST_DESCRIPTION. > > test_description character varying(50) COLLATE pg_catalog."default", -- TEST_DESCRIPTION, type > > What could be the reason here for fetching the data when I do not have the > data which has prefixes of BLANK , ‘ ‘.
Is there a functional index on left(test_description, 1) ? My guess is that the index is corrupted. This can be fixed with REINDEX.