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
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!='' but that didn’t make a difference.
Why is this not working?
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
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
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 (!!)
SQL Fiddle with a matrix of comparisons.
- 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.
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.
SELECT '' <> ' ' true
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 – 188.8.131.52 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.