Why Postgresql 9.2 word boundary not working! 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!
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=&gt; 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=&gt; show standard_conforming_strings ;
 standard_conforming_strings 
-----------------------------
 on
(1 row)

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


Now with it to OFF:

test=&gt; set standard_conforming_strings=off;
SET

test=&gt; 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=&gt; set escape_string_warning to off;
SET
test=&gt; WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT'))
SELECT v FROM x WHERE v ~* '\yACT\y';
 v 
---
(0 rows)