Mysql Postgresql cast

Question

I have a column (varchar in mysql and a character varying in postgresql). I need to apply sum on the column and I need a cast syntax that works for both.

The db structure is old and has both int and varchar values. I can't change that.

Answer 1

Why do you use a VARCHAR? You can't SUM an apple and pear, that's not going to work. You can use CAST() to cast, but that will fail on PostgreSQL (and any other DBMS) when invalid data is detected.

SELECT
  CAST('1' AS int);

This will fail:

SELECT
  CAST('apple' AS int);

Use the correct datatypes.