PostgreSQL 9.2 – Troubleshooting Word Boundary Issues

postgresqlpostgresql-9.1postgresql-9.2

In my postgresql version 9.1 this query is working fine:

select id,name from flash_card_set where name ~* '\yACT\y'

and matching:
text ACT txt,
ACT tests,
tests ACT
But in my postgresql version 9.2 it is not matching any of them.Am I missing something!

Best Answer

I'd suspect you have standard_conforming_strings set to OFF on the 9.1 instance (meaning it was changed explicitly, since the default is ON since 9.1), and the opposite in 9.2.

Demo:

test=> select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

 test=> show standard_conforming_strings ;
 standard_conforming_strings 
-----------------------------
 on
(1 row)

test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
test-> SELECT v FROM x WHERE v ~* '\yACT\y';
      v       
--------------
 text ACT txt
 ACT tests
 tests ACT
(3 rows)

Now with it to OFF:

test=> set standard_conforming_strings=off;
SET

test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
WARNING:  nonstandard use of escape in a string literal
LINE 2: SELECT v FROM x WHERE v ~* '\yACT\y';
                                   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 v 
---
(0 rows)

You'd need also to set escape_string_warning to OFF to mute the warning message and get a final result that plausibly matches your question:

test=> set escape_string_warning to off;
SET
test=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
 v 
---
(0 rows)
Related Question