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

John Lumby <johnlumby(at)hotmail(dot)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