The following bug has been logged online: Bug reference: 5364 Logged by: Markus Wichitill Email address: ma...@gmx.de PostgreSQL version: 8.4.2 Operating system: Linux, Win7 Description: citext behavior when type not in public schema Details:
Comparisons with columns of type citext silently work case-sensitively without any error message, unless the search_path contains "public", even if the type is not located in "public", but in the same schema as the table using it. I don't know if this is a bug or if it's specific to citext, but it's surprising behavior. shell> psql template1 pgsql template1=# CREATE DATABASE db; CREATE DATABASE template1=# \c db psql (8.4.2) You are now connected to database "db". db=# \i /usr/local/pgsql/share/contrib/citext.sql SET CREATE TYPE [...] db=# CREATE SCHEMA sch; CREATE SCHEMA db=# ALTER TYPE citext SET SCHEMA sch; ALTER TYPE db=# SET SCHEMA 'sch'; SET db=# CREATE TABLE tbl (col citext); CREATE TABLE db=# INSERT INTO tbl (col) VALUES ('val'); INSERT 0 1 db=# SELECT col FROM tbl WHERE col = 'VaL'; col ----- (0 rows) db=# SET search_path = sch, public; SET db=# SELECT col FROM tbl WHERE col = 'VaL'; col ----- val (1 row) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs