special characters in postgres _

Question

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.

Why do you use LIKE in the first place? Apparently you don't want a wildcard search but an equality condition. So you should use = instead of LIKE — user330315
– user330315, Commented Jan 15, 2013 at 16:05

Eric Petroelje · Accepted Answer · 2013-01-15 15:54:01Z

3

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.

answered Jan 15, 2013 at 15:54

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sharafjaffri Over a year ago

Is there any postgres escape function available? or list of characters to escape?

user330315 Over a year ago

@sharafjaffri: you only need to escape the SQL wildcards: _ and %

Ihor Romanchenko Over a year ago

@sharafjaffri it may better to use simple = operator, instead of escaping every _ and % char.

Daniel Vérité Over a year ago

Plus the escape character itself that must be escaped too.

Collectives™ on Stack Overflow

special characters in postgres _

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related