update results in postgresql 8.4 and 9.5 are different

Question

In our software we are still using postgresql 8.4. On Archlinux I could not find any possibility to install 8.4, so I started a task with 9.5 and wanted to translate it to 8.4 afterwards. Now I can run the UPDATE query on both versions but I get different results.

Query:

UPDATE properties                                                               
SET propertyvalue = regexp_replace(propertyvalue, E'[\u0001-\u001f]', '', 'g') 
WHERE properties_id in 
   (SELECT DISTINCT(properties_id) 
    FROM regexp_matches(propertyvalue, E'[\u0001-\u001f]'));

Workflow is that I reinit the database and dump the same sql-dump into both versions.

On 8.4 the message says "UPDATE 2689816" and on 9.5 it says "UPDATE 241294".

When I run

SELECT count(*) 
FROM properties 
WHERE properties_id in 
  (SELECT DISTINCT(properties_id) 
   FROM regexp_matches(propertyvalue, E'[\\u0001-\\u001f]'));

I get the same result with both versions:

count  
--------
 241294
(1 row)

That's what confuses me the most. why does regexp_matches seem to interpret the pattern differently from regexp_replace?

Does anyone have any experience with the matter?

Answer 1

I was just stupid, the script just removed a \ and I just saw it in my question here, sorry for the question