Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation when needed via the
COLLATE clause. For example, in your case, you can use the
tr-TR-x-icu collation as follows:
select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu"
You can see a working demo of this, using your sample code posted in the question, on db<>fiddle.
Of course, that will be case-sensitive. For this you need to use the
ILIKE operator which is case-insensitive. For example:
select name from testtable1 WHERE name ILIKE '%A%' COLLATE "tr-TR-x-icu"
= operator is still case-sensitive because the collation as defined in the
CREATE COLLATION statement did not alter the default sensitivities, and by default collations are everything-sensitive (which is not the same as binary collations, just FYI).
To create a collation that uses the "tr-TR" locale and is also case-insensitive, you need to specify the
locale property in the
CREATE COLLATION statement as follows:
locale = 'tr-TR-u-ks-level2'
To see that in action, please see: db<>fiddle example 2
Please see "Unicode Technical Standard #35: UNICODE LOCALE DATA MARKUP LANGUAGE (LDML), PART 5: COLLATION" for the full list of collation options.
And, please visit Collations Info for collation-related info. It's geared mostly towards SQL Server and .NET, but has some MySQL and PostgreSQL stuff, and the key concepts are generally the same across platforms and languages.