Re: Comparison of Strings

Mike Schulte <schulte(at)cs(dot)umsl(dot)edu> writes:
> If I run the following command, I get [the string matches the first two
> entries in the table]:

> cs302=# select * from author where isbn = '1565921941';
> isbn | author
> ------+--------
> (0 rows)

Ugh :-(. This looks like a corrupted-index problem --- is there an
index on the isbn column? If so, does EXPLAIN show that this query
uses an indexscan?

Dropping and recreating the index would probably make the problem
go away, but that doesn't answer the more interesting question: how'd
you get into this state? Have you had any crashes or peculiar behavior
with this database? Do you have LOCALE support compiled in, and if so
have you been careful to start the postmaster with the same locale
setting every time? (A column of ISBNs doesn't seem like promising
material for a locale problem, but you never know...)

It would be revealing to check whether the index is really in proper
order or not. Try

set enable_sort to off;
select isdn from author order by isdn;

(make sure EXPLAIN agrees that this query will be done by an indexscan
with no explicit sort step). See if the output is in proper sort
order or not...

regards, tom lane