PostgreSQL 8.2.15 (Greenplum Database 184.108.40.206 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 10 2015 14:15:10
I'm having a problem in one of our environments where the tables in one schema seem to behave incorrectly when doing simple queries, character columns are not comparing to literal strings or to other columns. Joins within the schema to other identical columns work, but joins to tables outside of the schema do not. Here's a simple example with literal strings.
I do this:
SELECT * FROM carriers WHERE carrier_code = 'FR'
Then I copy and paste the value from a character(32) column and do this:
SELECT * FROM carriers WHERE carrier_hash = '11aedd0e432747c2bcd97b82808d24a0'
This returns nothing. That's a straight copy-and-paste from that actual column as returned by the first query. If I do this, however:
SELECT * FROM carriers WHERE carrier_hash like '11aedd0e432747c2bcd97b82808d24a0'
That returns the same record. This also works:
SELECT * FROM carriers WHERE substring(carrier_hash,1,32) = '11aedd0e432747c2bcd97b82808d24a0'
like behave differently with a simple string and no
% characters? This has been working for months. The only thing that's changed recently is we've backed up, cleared out, and restored the database. Could something have become messed up during the restore?
We have other tables in other schemas that have the same column, and those work just fine.
Our DBA recently restored this environment from a backup. Don't know if that is relevant.