0

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 | |

  • 2
    What's the data type of location_id? Why is an integer in quotes? – Philᵀᴹ Mar 29 '15 at 17:51
  • 2
    I also don't understand "Postgres - works" vs. "doesn't work on PostgreSQL" what exactly is "working" and what exactly is "not working" – a_horse_with_no_name Mar 29 '15 at 17:53
  • 3
    Don't compare numbers and strings. Use proper numeric literals 37 is a number, '37' is a string. – a_horse_with_no_name Mar 29 '15 at 19:03
  • 1
    @tblPhil It's legal in SQL to have an integer in quotes anyway. – Craig Ringer Mar 30 '15 at 0:30
  • 1
    Please provide proper information: "string" is not a data type. It should be obvious to provide the actual data type. And your version of Postgres. – Erwin Brandstetter Mar 30 '15 at 1:10
3

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:

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.

  • 2
    My guess was that he has nulls. Postgres strictness vs. MySQL willingness to absorb data without errors, could very well have same statements resulting in different data in the tables between the two dbms. But the ' '::char <> '' resulting in false is quite probable reason, too. – ypercubeᵀᴹ Mar 30 '15 at 9:26
  • thx, you were right regarding character (have added the output from \d+ menu_items from psql. Reading this stackoverflow.com/questions/8694273/… makes me think I should be using these. Might try to hotfix for this situation for time being though. thx again – timpone Mar 30 '15 at 16:30
  • 2
    @timpone: This seems to be a misunderstanding. character varying(255) is the same as varchar(n) not character(n). And varchar is just fine (except hat the modifier 255 is typically based on another misunderstanding) and cannot explain the problem. Read the manual and check for NULL values ... – Erwin Brandstetter Mar 30 '15 at 16:34
  • thx Erwin - I'm going back to this being a data issue; this has been a bit of a big migration that went mostly well but I really appreciate your help and insights. – timpone Mar 30 '15 at 16:36
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.

  • thx - I think the like statement is fine, the second result returns the anticipated 17 results using like. – timpone Mar 29 '15 at 17:58
  • Yeah, sorry didn't read it thoroughly enough. I added one case where that bin_number comparison is handled differently. If it's not about that we would then need the 17 values returned as ypercube mentioned. – Simo Kivistö Mar 29 '15 at 18:18
  • 1
    And actually that can't be the problem as PostgreSQLs more strict comparison actually returns true more often. Well, we'll wait to see those 17 values. – Simo Kivistö Mar 29 '15 at 18:28
  • You may be on the right track, let's try the opposite direction ... – Erwin Brandstetter Mar 30 '15 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.