Query that was working on MySQL is not working on PostgreSQL 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&lt;&gt;'') 
ORDER BY bin_number asc;
&gt; 0 results   # should be 17


Postgres - works so issues with and bin_number&lt;&gt;''

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;
&gt; 17 results


I tried changing bin_number&lt;&gt;'' 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 |   |
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 '  '       &lt;&gt; '';  -- TRUE
SELECT '  '::text &lt;&gt; '';  -- TRUE (identical, because text is default
SELECT '  '::char &lt;&gt; '';  -- 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.