[postgis-users] ERROR: invalid regular expression: parentheses () not balanced

[Paul Ramsey]

A LIKE query just gets re-written into an equivalent regex in the backend. There is no free lunch.

This can be fast, if you build an index using text_pattern_ops like

 CREATE INDEX ON footable (foo text_pattern_ops);

 SELECT * FROM footable WHERE foo LIKE 'bar%';

This will never be fast

 SELECT * FROM tootable WHERE foo LIKE '%bar';

(Well, unless you're clever and index and search it in reverse.)

If you want something to find particular words in a larger collection of words quickly, research full text search.

https://www.postgresql.org/docs/current/textsearch.html

If you want to look for parts of words and partial matches, research pg_tgrm.

https://www.postgresql.org/docs/current/pgtrgm.html

P

> On Dec 17, 2020, at 3:13 PM, Thiemo Kellner <thiemo at gelassene-pferde.biz> wrote:
> 
> In the case that the task is to select all records from mytable where the full_address contains the string pao_text, the like clause is probably much more efficient:
> 
> select * from mytable where full_address like '%pao_text%';
> 
> Performance might not be an issue here but it is never bad to have an eye on efficiency in my opinion.
> 
> Cheers
> 
> Thiemo
> 
> Quoting Bo Victor Thomsen <bo.victor.thomsen at gmail.com>:
> 
>> select * from mytable where full_address ~* 'pao_text';
>>                                                                                 ^               ^
>> 
>> You've forgotten the string delimiters
>> 
>> Med venlig hilsen / Kind regards
>> 
>> Bo Victor Thomsen
>> 
>> Den 17-12-2020 kl. 11:53 skrev Shaozhong SHI:
>>> select * from mytable where full_address ~* pao_text;
>>> 
> 
> 
> 
> S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
> Signal (Safer than WhatsApp): +49 1578 7723737
> Threema (Safer than WhatsApp): A76MKH3J
> Handys: +41 78 947 36 21 | +49 1578 772 37 37
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users