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

From Tom Lane
Subject Re: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
Date
Msg-id 27650.1323789495@sss.pgh.pa.us
Whole thread Raw
In response to Re: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...  (John Lumby <johnlumby@hotmail.com>)
Responses Re: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
List pgsql-bugs
John Lumby <johnlumby@hotmail.com> writes:
> Is your output different?  Do you think you could post the output you get?

Mine looks like yours, except no error.  [ looks closer... ]  Are you
sure you're running a 9.1.2 server?  The large difference in
datlastsysoid seems like it could not have happened if you had
production 9.1.x system catalog contents.  Now I'm suspicious there's
something wrong with your catalog entries, not the code itself.


DROP DATABASE
CREATE TABLE
Tue Dec 13 09:59:18 EST 2011
rc= 0 inserted 10000 entities Tue Dec 13 10:00:30 EST 2011
ANALYZE
  datname   | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid |
datfrozenxid| dattablespace  

------------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------------+---------------
 LIKE_42P22 |     10 |        6 | C          | C        | f             | t            |           -1 |         12772 |
       1795 |          1663 
(1 row)

 nspname | relname | attrelid |    attname    | atttypid | typname | collname
---------+---------+----------+---------------+----------+---------+----------
 public  | entity  |    41366 | 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)

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



            regards, tom lane

pgsql-bugs by date:

Previous
From: dmigowski@ikoffice.de
Date:
Subject: BUG #6333: pgAdmin 14.1 - Listbox stupidities
Next
From: John Lumby
Date:
Subject: Re: LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...