CODEWITHSUNDEEP
postgresql
sharafjaffri
sharafjaffri

Reputation: 2214

special characters in postgres _

I have a query like this which use index on call_id while if I add _ in value then it changes from index search to seq search.

explain analyze 
DELETE 
FROM completedcalls 
WHERE call_id like '[email protected]';


                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Delete on completedcalls  (cost=0.00..8.67 rows=1 width=6) (actual time=0.036..0.036 rows=0 loops=1)
   ->  Index Scan using i_call_id on completedcalls  (cost=0.00..8.67 rows=1 width=6) (actual time=0.034..0.034 rows=0 loops=1)
         Index Cond: ((call_id)::text = '[email protected]'::text)
         Filter: ((call_id)::text ~~ '[email protected]'::text)
 Total runtime: 0.069 ms
(5 rows)

This statement: 

explain analyze 
DELETE 
FROM completedcalls 
WHERE call_id like '[email protected]_1';

Returns this execution plan:

QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Delete on completedcalls  (cost=0.00..39548.64 rows=84 width=6) (actual time=194.313..194.313 rows=0 loops=1)
   ->  Seq Scan on completedcalls  (cost=0.00..39548.64 rows=84 width=6) (actual time=194.310..194.310 rows=0 loops=1)
         Filter: ((call_id)::text ~~ '[email protected]_1'::text)
 Total runtime: 194.349 ms
(4 rows)

My Question is how to escape these characters in query. Using psycopg2 in python.

Upvotes: 0

Views: 8380

Answers (1)

Eric Petroelje
Eric Petroelje

Reputation: 60508

You would need to escape the _ with a backslash, like so:

DELETE FROM completedcalls 
WHERE call_id like '[email protected]\_1';

Also, if you don't want pattern matching, it would probably make more sense to use = instead of LIKE.

Upvotes: 3

Related Questions