When migrating from MySQL to PostgreSQL a question which seems to come up often is how to do the equivalent of REPLACE INTO? At the supplied link you will find the answer (with the mention that you should wrap the code in a transaction if it’s not done already for you by your data access library), however I would like to talk about a little quirk regarding the problem / solution:

Let’s assume that you have a VARCHAR(10) column, where you try to insert a longer value (ok, your input validation should have catched this, but that’s not the point). MySQL will normally emit a warning (which you won’t see if you’re not looking for it) and truncate the value before insertion (unless the sql_mode is set to STRICT_ALL_TABLES or STRICT_TRANS_TABLES). PostgreSQL will however die in the middle of the code with an error message. This can be very enigmatic if your code happens to a stored procedure. Unfortunately I haven’t found any elegant solution to declare that a stored procedure takes only strings up to a given length, so here is a small function which verifies that an array of strings conforms to a given maximum length restriction and throws a less enigmatic exception if not:

CREATE OR REPLACE FUNCTION check_array_elements_length(max_length integer, elements anyarray) RETURNS void AS $BODY$

BEGIN

 IF elements IS NULL THEN

  RETURN;

 END IF;

 FOR idx IN array_lower(elements, 1)..array_upper(elements, 1) LOOP

  IF elements[idx] IS NOT NULL THEN

   IF length(elements[idx]) > max_length THEN

    RAISE EXCEPTION 'String exceeds maximum admitted length (%): %', max_length, length(elements[idx]);

   END IF;

  END IF;

 END LOOP;

END;

$BODY$ LANGUAGE 'plpgsql' STABLE;

To use this procedure, start your code with:

PERFORM check_array_elements_length(10, ARRAY[param1, param2, param3]);

It also works for binary strings, but in this case you must supply the length in bits (for example the length of x'1F2' is 12, not 3!).