How to use like operator in Presto with string that contain a dot?

I have a column string like this

"test.123.test" "something"

And I want run a query to find string like "test.*.test". In postgresql I use this query:

select * from table where string_column like 'test.%.test'

I run this query in presto but got nothing! It should be related to dot in my string because when I replace string like with something like this 'test.1%1.test' it work but it's not my result.

1 Answer

For a Presto query

string_column like 'test.%.test'

the predicate pushed down into the PostgreSQL connector is similar to:

string_column BETWEEN 'test.' AND 'test/'

however, string comparison are subject to collation and trailing punctuations hits an edge case of Presto/PostgreSQL incompatibility:

You can workaround this by preventing predicate pushdown into the connector. You can achieve this by adding OR rand() = 42 to your query:

string_column like 'test.%.test' OR rand() = 42