String and Int Comparison - Oracle to PostgreSQL Migration

Both Oracle and PostgreSQL allows you to compare string and integer without explicit casting. But PostgreSQL requires the explicit casting if a built-in function is involved:

Oracle:

  -- Implicit casting to compare string and integer
  SELECT 't' FROM dual WHERE '0' < 1;
  # t
 
  -- Implicit casting with function SUBSTR
  SELECT 't' FROM dual WHERE SUBSTR('0', 1, 1) < 1;
  # t
 
  -- Implicit casting with function CAST
  SELECT 't' FROM dual WHERE CAST('0' AS CHAR(1)) < 1;
  # t

PostgreSQL:

  -- Implicit casting to compare string and integer
  SELECT '0' < 1;
  # t
 
  -- Implicit casting with function SUBSTR
  SELECT SUBSTR('0', 1, 1) < 1;
  # ERROR:  operator does not exist: text < integer
 
  -- Implicit casting with function CAST
  SELECT CAST('0' AS CHAR(1)) < 1;
  # ERROR:  operator does not exist: character < integer
 
  -- Implicit casting with function CAST
  SELECT CAST('0' AS VARCHAR(1)) < 1;
  # ERROR:  operator does not exist: character varying < integer

You can see that you can compare a string literal with integer without explicit casting, i.e. '0' < 1, and in other cases you have to use the casting:

PostgreSQL:

  -- Implicit casting with function SUBSTR
  SELECT CAST(SUBSTR('0', 1, 1) AS INT) < 1;
  # t
 
  -- Implicit casting with function CAST
  SELECT CAST('0' AS INT) < 1;
  # t

For more information, see Oracle to PostgreSQL Migration.