Re: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...

Well,=A0 thanks for trying it=A0 -=A0 but that is strange.=A0=A0 Yes,=A0 my=
 database is created with datcollate "C"=A0 -
the script displays that (see below).

The only thing I can think of that might explain different outcome is if,=
=A0 in your environment,
the planner did not choose the index-access plan after the index was create=
d,
and/or did not add the two range predicates.=A0=A0=A0 Is that the case?
The script output shows the two plans before and after creating the index.

Here is the output from running the script just now=20
(and I ran it on a very different system than before just to get another
sample point and it exhibits the same bug).
Is your output different?=A0=A0 Do you think you could post the output you =
get?

Regards,=A0=A0=A0 John Lumby
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
/home/postgres/bin/demo_like_42p22.sh
WARNING=A0=A0 this script will (try to) drop and recreate a database named =
LIKE_42P22
press enter to continue or Ctl-C to cancel

NOTICE:=A0 database "LIKE_42P22" does not exist, skipping
DROP DATABASE
CREATE TABLE
Tue Dec 13 08:50:12 EST 2011
rc=3D 0 inserted 10000 entities Tue Dec 13 09:05:30 EST 2011
ANALYZE
=A0 datname=A0=A0 | datdba | encoding | datcollate | datctype | datistempla=
te | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattables=
pace=20
------------+--------+----------+------------+----------+---------------+--=
------------+--------------+---------------+--------------+---------------
=A0LIKE_42P22 |=A0=A0=A0=A0 10 |=A0=A0=A0=A0=A0=A0=A0 6 | C=A0=A0=A0=A0=A0=
=A0=A0=A0=A0 | C=A0=A0=A0=A0=A0=A0=A0 | f=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0 | t=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 -1=
 |=A0=A0=A0=A0=A0=A0=A0=A0 11910 |=A0=A0=A0=A0=A0=A0=A0=A0=A0 670 |=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 1663
(1 row)

=A0nspname | relname | attrelid |=A0=A0=A0 attname=A0=A0=A0 | atttypid | ty=
pname | collname=20
---------+---------+----------+---------------+----------+---------+-------=
---
=A0public=A0 | entity=A0 |=A0=A0=A0 26506 | discriminator |=A0=A0=A0=A0 104=
3 | varchar | default
(1 row)

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
-------------------------------------------------------------------
=A0Aggregate=A0 (cost=3D190.26..190.27 rows=3D1 width=3D0)
=A0=A0 ->=A0 Seq Scan on entity e1=A0 (cost=3D0.00..189.00 rows=3D505 width=
=3D0)
=A0=A0=A0=A0=A0=A0=A0=A0 Filter: ((discriminator)::text ~~ 'DEPLOY%'::text)
(3 rows)

=A0count=20
-------
=A0=A0 500
(1 row)

CREATE INDEX
ANALYZE
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QU=
ERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=20
---------------------------------------------------------------------------=
-----------------------------------
=A0Aggregate=A0 (cost=3D23.71..23.72 rows=3D1 width=3D0)
=A0=A0 ->=A0 Index Scan using entity_discriminator on entity e1=A0 (cost=3D=
0.00..22.45 rows=3D505 width=3D0)
=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (((discriminator)::text >=3D 'DEPLOY':=
:text) AND ((discriminator)::text < 'DEPLOZ'::text))
=A0=A0=A0=A0=A0=A0=A0=A0 Filter: ((discriminator)::text ~~ 'DEPLOY%'::text)
(4 rows)

ERROR:=A0 could not determine which collation to use for string comparison
HINT:=A0 Use the COLLATE clause to set the collation explicitly.


----------------------------------------
> To: johnlumby@hotmail.com
> CC: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine =
which collation to use for string comparison - HINT: Use the COLLATE clause=
 ...
> Date: Mon, 12 Dec 2011 16:38:53 -0500
> From: tgl@sss.pgh.pa.us
>
> John Lumby <johnlumby@hotmail.com> writes:
> > Sorry about that,   here is a script to demonstrate using a much simple=
r example.
> > It presumes linux and that there is a bash shell at /bin/bash.
>
> > You should see table created and loaded (takes a few minutes),   then s=
uccessful query explain and run,
> > then create index and re-run the explain (now uses index) and query (no=
w fails)
>
> Hmm ... I tried this script on both HEAD and 9.1, and see no failure.
> Possibly it requires some non-default configuration setting to show
> the problem? Also, are you certain your database is ending up with
> LC_COLLATE and LC_CTYPE set to "C"? I am not sure that createdb pays
> attention to those as locale settings.
>
> regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
=20=09=09=20=09=20=20=20=09=09=20=20