Query that was working on MySQL is not working on PostgreSQL

Question

I had the following query (with bin_number being a string) which was working on MySQL 5.5:

Postgres - was working on MySQL and doesn't work on PostgreSQL

SELECT "menu_items".* 
FROM "menu_items" 
WHERE ((header like '%red%' 
         or detail like '%red%' 
         or bin_number like '%red%') 
  and location_id='37' 
  and is_deleted=false and bin_number<>'') 
ORDER BY bin_number asc;
> 0 results   # should be 17

Postgres - works so issues with and bin_number<>''

SELECT "menu_items".* 
FROM "menu_items" 
WHERE ((header like '%red%' 
        or detail like '%red%' 
        or bin_number like '%red%') 
and location_id='37' 
and is_deleted=false) 
ORDER BY bin_number asc;
> 17 results

I tried changing bin_number<>'' to bin_number!='' but that didn't make a difference.
Why is this not working?

edit 1

The bin_number in menu_items is declared as:

bin_number | character varying(255) | | extended | |

Answer 1

My educated guess (while proper information is missing): You declared bin_number as character(n), char(n) or just char data type.

character(n) is an outdated, inefficient, weird data type, but defined in the SQL standard. char (without double quotes!) or char(1) are aliases for character(1).

Typically you do not want to use it. It's a blank-padded data type where all strings containing only blanks or nothing (i.e. the empty string '') are considered identical. Effectively all of these are treated as empty strings because trailing blanks are considered insignificant.

SELECT '  '       <> '';  -- TRUE
SELECT '  '::text <> '';  -- TRUE (identical, because text is default
SELECT '  '::char <> '';  -- FALSE (!!)

Use varchar or text instead.

SQL Fiddle with a matrix of comparisons.

More:

• Compare varchar with char
• Any downsides of using data type “text” for storing strings?
• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

It's either that, or you have NULL values in the column where you had empty strings in MySQL. Either way, the table definition (\d tbl in psql) in the question and some sample values would allow answers instead of speculation.

Answer 2

There is at least one case where bin_number <> '' is handled differently in MySQL and PostgreSQL. It's when the string only contains spaces. Check out this earlier answer that sheds a light to the MySQL side of things.

PostgreSQL:

SELECT '' <> ' '
true

MySQL:

SELECT '' <> ' '
false

Usually when the statement contains LIKE the difference happens because keyword LIKE is handled differently in MySQL and PostgreSQL. In PostgreSQL you have to use ILIKE to get case-insensitive pattern matching.

MySQL Documentation - 3.3.4.7 Pattern Matching:

SQL patterns are case-insensitive by default

PostgreSQL Documentation - 9.7.1. LIKE:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.