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

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

The only thing I can think of that might explain different outcome is if,  in your environment,
the planner did not choose the index-access plan after the index was created,
and/or did not add the two range predicates.    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
(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?   Do you think you could post the output you get?

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

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

 nspname | relname | attrelid |    attname    | atttypid | typname | collname
---------+---------+----------+---------------+----------+---------+----------
 public  | entity  |    26506 | discriminator |     1043 | varchar | default
(1 row)

                            QUERY PLAN                            
-------------------------------------------------------------------
 Aggregate  (cost=190.26..190.27 rows=1 width=0)
   ->  Seq Scan on entity e1  (cost=0.00..189.00 rows=505 width=0)
         Filter: ((discriminator)::text ~~ 'DEPLOY%'::text)
(3 rows)

 count
-------
   500
(1 row)

CREATE INDEX
ANALYZE
                                                  QUERY PLAN                                                 
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=23.71..23.72 rows=1 width=0)
   ->  Index Scan using entity_discriminator on entity e1  (cost=0.00..22.45 rows=505 width=0)
         Index Cond: (((discriminator)::text >= 'DEPLOY'::text) AND ((discriminator)::text < 'DEPLOZ'::text))
         Filter: ((discriminator)::text ~~ 'DEPLOY%'::text)
(4 rows)

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

----------------------------------------
> To: johnlumby(at)hotmail(dot)com
> CC: pgsql-bugs(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us
>
> John Lumby <johnlumby(at)hotmail(dot)com> writes:
> > Sorry about that, here is a script to demonstrate using a much simpler 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 successful query explain and run,
> > then create index and re-run the explain (now uses index) and query (now 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs