summaryrefslogtreecommitdiffstats
path: root/contrib/citext
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/citext')
-rw-r--r--contrib/citext/.gitignore4
-rw-r--r--contrib/citext/Makefile25
-rw-r--r--contrib/citext/citext--1.0--1.1.sql21
-rw-r--r--contrib/citext/citext--1.1--1.2.sql68
-rw-r--r--contrib/citext/citext--1.2--1.3.sql21
-rw-r--r--contrib/citext/citext--1.3--1.4.sql12
-rw-r--r--contrib/citext/citext--1.4--1.5.sql88
-rw-r--r--contrib/citext/citext--1.4.sql501
-rw-r--r--contrib/citext/citext--1.5--1.6.sql12
-rw-r--r--contrib/citext/citext.c408
-rw-r--r--contrib/citext/citext.control6
-rw-r--r--contrib/citext/expected/citext.out2693
-rw-r--r--contrib/citext/expected/citext_1.out2693
-rw-r--r--contrib/citext/expected/citext_utf8.out146
-rw-r--r--contrib/citext/expected/citext_utf8_1.out9
-rw-r--r--contrib/citext/expected/create_index_acl.out86
-rw-r--r--contrib/citext/sql/citext.sql810
-rw-r--r--contrib/citext/sql/citext_utf8.sql51
-rw-r--r--contrib/citext/sql/create_index_acl.sql88
19 files changed, 7742 insertions, 0 deletions
diff --git a/contrib/citext/.gitignore b/contrib/citext/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/citext/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
new file mode 100644
index 0000000..35db6ea
--- /dev/null
+++ b/contrib/citext/Makefile
@@ -0,0 +1,25 @@
+# contrib/citext/Makefile
+
+MODULES = citext
+
+EXTENSION = citext
+DATA = citext--1.4.sql \
+ citext--1.5--1.6.sql \
+ citext--1.4--1.5.sql \
+ citext--1.3--1.4.sql \
+ citext--1.2--1.3.sql citext--1.1--1.2.sql \
+ citext--1.0--1.1.sql
+PGFILEDESC = "citext - case-insensitive character string data type"
+
+REGRESS = create_index_acl citext citext_utf8
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/citext
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/citext/citext--1.0--1.1.sql b/contrib/citext/citext--1.0--1.1.sql
new file mode 100644
index 0000000..e06627e
--- /dev/null
+++ b/contrib/citext/citext--1.0--1.1.sql
@@ -0,0 +1,21 @@
+/* contrib/citext/citext--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.1'" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION citext DROP FUNCTION regexp_matches( citext, citext );
+ALTER EXTENSION citext DROP FUNCTION regexp_matches( citext, citext, text );
+
+/* Then we can drop them */
+DROP FUNCTION regexp_matches( citext, citext );
+DROP FUNCTION regexp_matches( citext, citext, text );
+
+/* Now redefine */
+CREATE FUNCTION regexp_matches( citext, citext ) RETURNS SETOF TEXT[] AS $$
+ SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT ROWS 1;
+
+CREATE FUNCTION regexp_matches( citext, citext, text ) RETURNS SETOF TEXT[] AS $$
+ SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+$$ LANGUAGE SQL IMMUTABLE STRICT ROWS 10;
diff --git a/contrib/citext/citext--1.1--1.2.sql b/contrib/citext/citext--1.1--1.2.sql
new file mode 100644
index 0000000..a8bba86
--- /dev/null
+++ b/contrib/citext/citext--1.1--1.2.sql
@@ -0,0 +1,68 @@
+/* contrib/citext/citext--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.2'" to load this file. \quit
+
+ALTER FUNCTION citextin(cstring) PARALLEL SAFE;
+ALTER FUNCTION citextout(citext) PARALLEL SAFE;
+ALTER FUNCTION citextrecv(internal) PARALLEL SAFE;
+ALTER FUNCTION citextsend(citext) PARALLEL SAFE;
+ALTER FUNCTION citext(bpchar) PARALLEL SAFE;
+ALTER FUNCTION citext(boolean) PARALLEL SAFE;
+ALTER FUNCTION citext(inet) PARALLEL SAFE;
+ALTER FUNCTION citext_eq(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_ne(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_lt(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_le(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_gt(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_ge(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_cmp(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_hash(citext) PARALLEL SAFE;
+ALTER FUNCTION citext_smaller(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION citext_larger(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION texticlike(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION texticnlike(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION texticregexeq(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION texticregexne(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION texticlike(citext, text) PARALLEL SAFE;
+ALTER FUNCTION texticnlike(citext, text) PARALLEL SAFE;
+ALTER FUNCTION texticregexeq(citext, text) PARALLEL SAFE;
+ALTER FUNCTION texticregexne(citext, text) PARALLEL SAFE;
+ALTER FUNCTION regexp_matches(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION regexp_matches(citext, citext, text) PARALLEL SAFE;
+ALTER FUNCTION regexp_replace(citext, citext, text) PARALLEL SAFE;
+ALTER FUNCTION regexp_replace(citext, citext, text, text) PARALLEL SAFE;
+ALTER FUNCTION regexp_split_to_array(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION regexp_split_to_array(citext, citext, text) PARALLEL SAFE;
+ALTER FUNCTION regexp_split_to_table(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION regexp_split_to_table(citext, citext, text) PARALLEL SAFE;
+ALTER FUNCTION strpos(citext, citext) PARALLEL SAFE;
+ALTER FUNCTION replace(citext, citext, citext) PARALLEL SAFE;
+ALTER FUNCTION split_part(citext, citext, int) PARALLEL SAFE;
+ALTER FUNCTION translate(citext, citext, text) PARALLEL SAFE;
+
+-- We have to update aggregates the hard way for lack of ALTER support
+DO LANGUAGE plpgsql
+$$
+DECLARE
+ my_schema pg_catalog.text := pg_catalog.quote_ident(pg_catalog.current_schema());
+ old_path pg_catalog.text := pg_catalog.current_setting('search_path');
+BEGIN
+-- for safety, transiently set search_path to just pg_catalog+pg_temp
+PERFORM pg_catalog.set_config('search_path', 'pg_catalog, pg_temp', true);
+
+UPDATE pg_proc SET proparallel = 's'
+WHERE oid = (my_schema || '.min(' || my_schema || '.citext)')::pg_catalog.regprocedure;
+
+UPDATE pg_proc SET proparallel = 's'
+WHERE oid = (my_schema || '.max(' || my_schema || '.citext)')::pg_catalog.regprocedure;
+
+UPDATE pg_aggregate SET aggcombinefn = (my_schema || '.citext_smaller')::regproc
+WHERE aggfnoid = (my_schema || '.max(' || my_schema || '.citext)')::pg_catalog.regprocedure;
+
+UPDATE pg_aggregate SET aggcombinefn = (my_schema || '.citext_larger')::regproc
+WHERE aggfnoid = (my_schema || '.max(' || my_schema || '.citext)')::pg_catalog.regprocedure;
+
+PERFORM pg_catalog.set_config('search_path', old_path, true);
+END
+$$;
diff --git a/contrib/citext/citext--1.2--1.3.sql b/contrib/citext/citext--1.2--1.3.sql
new file mode 100644
index 0000000..24a7145
--- /dev/null
+++ b/contrib/citext/citext--1.2--1.3.sql
@@ -0,0 +1,21 @@
+/* contrib/citext/citext--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.3'" to load this file. \quit
+
+-- We have to update aggregates the hard way for lack of ALTER support
+DO LANGUAGE plpgsql
+$$
+DECLARE
+ my_schema pg_catalog.text := pg_catalog.quote_ident(pg_catalog.current_schema());
+ old_path pg_catalog.text := pg_catalog.current_setting('search_path');
+BEGIN
+-- for safety, transiently set search_path to just pg_catalog+pg_temp
+PERFORM pg_catalog.set_config('search_path', 'pg_catalog, pg_temp', true);
+
+UPDATE pg_aggregate SET aggcombinefn = (my_schema || '.citext_smaller')::regproc
+WHERE aggfnoid = (my_schema || '.min(' || my_schema || '.citext)')::pg_catalog.regprocedure;
+
+PERFORM pg_catalog.set_config('search_path', old_path, true);
+END
+$$;
diff --git a/contrib/citext/citext--1.3--1.4.sql b/contrib/citext/citext--1.3--1.4.sql
new file mode 100644
index 0000000..7b36651
--- /dev/null
+++ b/contrib/citext/citext--1.3--1.4.sql
@@ -0,0 +1,12 @@
+/* contrib/citext/citext--1.3--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.4'" to load this file. \quit
+
+CREATE FUNCTION regexp_match( citext, citext ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_match( citext, citext, text ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
diff --git a/contrib/citext/citext--1.4--1.5.sql b/contrib/citext/citext--1.4--1.5.sql
new file mode 100644
index 0000000..5ae522b
--- /dev/null
+++ b/contrib/citext/citext--1.4--1.5.sql
@@ -0,0 +1,88 @@
+/* contrib/citext/citext--1.4--1.5.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.5'" to load this file. \quit
+
+ALTER OPERATOR <= (citext, citext) SET (
+ RESTRICT = scalarlesel,
+ JOIN = scalarlejoinsel
+);
+
+ALTER OPERATOR >= (citext, citext) SET (
+ RESTRICT = scalargesel,
+ JOIN = scalargejoinsel
+);
+
+CREATE FUNCTION citext_pattern_lt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_le( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_gt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_ge( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR ~<~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~>=~,
+ COMMUTATOR = ~>~,
+ PROCEDURE = citext_pattern_lt,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR ~<=~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~>~,
+ COMMUTATOR = ~>=~,
+ PROCEDURE = citext_pattern_le,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR ~>=~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~<~,
+ COMMUTATOR = ~<=~,
+ PROCEDURE = citext_pattern_ge,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE OPERATOR ~>~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~<=~,
+ COMMUTATOR = ~<~,
+ PROCEDURE = citext_pattern_gt,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE FUNCTION citext_pattern_cmp(citext, citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+CREATE OPERATOR CLASS citext_pattern_ops
+FOR TYPE CITEXT USING btree AS
+ OPERATOR 1 ~<~ (citext, citext),
+ OPERATOR 2 ~<=~ (citext, citext),
+ OPERATOR 3 = (citext, citext),
+ OPERATOR 4 ~>=~ (citext, citext),
+ OPERATOR 5 ~>~ (citext, citext),
+ FUNCTION 1 citext_pattern_cmp(citext, citext);
diff --git a/contrib/citext/citext--1.4.sql b/contrib/citext/citext--1.4.sql
new file mode 100644
index 0000000..7b06198
--- /dev/null
+++ b/contrib/citext/citext--1.4.sql
@@ -0,0 +1,501 @@
+/* contrib/citext/citext--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION citext" to load this file. \quit
+
+--
+-- PostgreSQL code for CITEXT.
+--
+-- Most I/O functions, and a few others, piggyback on the "text" type
+-- functions via the implicit cast to text.
+--
+
+--
+-- Shell type to keep things a bit quieter.
+--
+
+CREATE TYPE citext;
+
+--
+-- Input and output functions.
+--
+CREATE FUNCTION citextin(cstring)
+RETURNS citext
+AS 'textin'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citextout(citext)
+RETURNS cstring
+AS 'textout'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citextrecv(internal)
+RETURNS citext
+AS 'textrecv'
+LANGUAGE internal STABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citextsend(citext)
+RETURNS bytea
+AS 'textsend'
+LANGUAGE internal STABLE STRICT PARALLEL SAFE;
+
+--
+-- The type itself.
+--
+
+CREATE TYPE citext (
+ INPUT = citextin,
+ OUTPUT = citextout,
+ RECEIVE = citextrecv,
+ SEND = citextsend,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended,
+ -- make it a non-preferred member of string type category
+ CATEGORY = 'S',
+ PREFERRED = false,
+ COLLATABLE = true
+);
+
+--
+-- Type casting functions for those situations where the I/O casts don't
+-- automatically kick in.
+--
+
+CREATE FUNCTION citext(bpchar)
+RETURNS citext
+AS 'rtrim1'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext(boolean)
+RETURNS citext
+AS 'booltext'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext(inet)
+RETURNS citext
+AS 'network_show'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+--
+-- Implicit and assignment type casts.
+--
+
+CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT;
+CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
+CREATE CAST (citext AS bpchar) WITHOUT FUNCTION AS ASSIGNMENT;
+CREATE CAST (text AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
+CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
+CREATE CAST (bpchar AS citext) WITH FUNCTION citext(bpchar) AS ASSIGNMENT;
+CREATE CAST (boolean AS citext) WITH FUNCTION citext(boolean) AS ASSIGNMENT;
+CREATE CAST (inet AS citext) WITH FUNCTION citext(inet) AS ASSIGNMENT;
+
+--
+-- Operator Functions.
+--
+
+CREATE FUNCTION citext_eq( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_ne( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_lt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_le( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_gt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_ge( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+--
+-- Operators.
+--
+
+CREATE OPERATOR = (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ COMMUTATOR = =,
+ NEGATOR = <>,
+ PROCEDURE = citext_eq,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel,
+ HASHES,
+ MERGES
+);
+
+CREATE OPERATOR <> (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = =,
+ COMMUTATOR = <>,
+ PROCEDURE = citext_ne,
+ RESTRICT = neqsel,
+ JOIN = neqjoinsel
+);
+
+CREATE OPERATOR < (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = >=,
+ COMMUTATOR = >,
+ PROCEDURE = citext_lt,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR <= (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = >,
+ COMMUTATOR = >=,
+ PROCEDURE = citext_le,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR >= (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = <,
+ COMMUTATOR = <=,
+ PROCEDURE = citext_ge,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE OPERATOR > (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = <=,
+ COMMUTATOR = <,
+ PROCEDURE = citext_gt,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+--
+-- Support functions for indexing.
+--
+
+CREATE FUNCTION citext_cmp(citext, citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION citext_hash(citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+--
+-- The btree indexing operator class.
+--
+
+CREATE OPERATOR CLASS citext_ops
+DEFAULT FOR TYPE CITEXT USING btree AS
+ OPERATOR 1 < (citext, citext),
+ OPERATOR 2 <= (citext, citext),
+ OPERATOR 3 = (citext, citext),
+ OPERATOR 4 >= (citext, citext),
+ OPERATOR 5 > (citext, citext),
+ FUNCTION 1 citext_cmp(citext, citext);
+
+--
+-- The hash indexing operator class.
+--
+
+CREATE OPERATOR CLASS citext_ops
+DEFAULT FOR TYPE citext USING hash AS
+ OPERATOR 1 = (citext, citext),
+ FUNCTION 1 citext_hash(citext);
+
+--
+-- Aggregates.
+--
+
+CREATE FUNCTION citext_smaller(citext, citext)
+RETURNS citext
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_larger(citext, citext)
+RETURNS citext
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE AGGREGATE min(citext) (
+ SFUNC = citext_smaller,
+ STYPE = citext,
+ SORTOP = <,
+ PARALLEL = SAFE,
+ COMBINEFUNC = citext_smaller
+);
+
+CREATE AGGREGATE max(citext) (
+ SFUNC = citext_larger,
+ STYPE = citext,
+ SORTOP = >,
+ PARALLEL = SAFE,
+ COMBINEFUNC = citext_larger
+);
+
+--
+-- CITEXT pattern matching.
+--
+
+CREATE FUNCTION texticlike(citext, citext)
+RETURNS bool AS 'texticlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticnlike(citext, citext)
+RETURNS bool AS 'texticnlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexeq(citext, citext)
+RETURNS bool AS 'texticregexeq'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexne(citext, citext)
+RETURNS bool AS 'texticregexne'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR ~ (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR ~* (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~*,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR !~ (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR !~* (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~*,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR ~~ (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~~,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR ~~* (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~~*,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR !~~ (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~~,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+CREATE OPERATOR !~~* (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~~*,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+--
+-- Matching citext to text.
+--
+
+CREATE FUNCTION texticlike(citext, text)
+RETURNS bool AS 'texticlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticnlike(citext, text)
+RETURNS bool AS 'texticnlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexeq(citext, text)
+RETURNS bool AS 'texticregexeq'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexne(citext, text)
+RETURNS bool AS 'texticregexne'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR ~ (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR ~* (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~*,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR !~ (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR !~* (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~*,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR ~~ (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~~,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR ~~* (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~~*,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR !~~ (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~~,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+CREATE OPERATOR !~~* (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~~*,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+--
+-- Matching citext in string comparison functions.
+-- XXX TODO Ideally these would be implemented in C.
+--
+
+CREATE FUNCTION regexp_match( citext, citext ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_match( citext, citext, text ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_matches( citext, citext ) RETURNS SETOF TEXT[] AS $$
+ SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1;
+
+CREATE FUNCTION regexp_matches( citext, citext, text ) RETURNS SETOF TEXT[] AS $$
+ SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10;
+
+CREATE FUNCTION regexp_replace( citext, citext, text ) returns TEXT AS $$
+ SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_replace( citext, citext, text, text ) returns TEXT AS $$
+ SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN $4 || 'i' ELSE $4 END);
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_array( citext, citext ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_array( citext, citext, text ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_table( citext, citext ) RETURNS SETOF TEXT AS $$
+ SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_table( citext, citext, text ) RETURNS SETOF TEXT AS $$
+ SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION strpos( citext, citext ) RETURNS INT AS $$
+ SELECT pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION replace( citext, citext, citext ) RETURNS TEXT AS $$
+ SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION split_part( citext, citext, int ) RETURNS TEXT AS $$
+ SELECT (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION translate( citext, citext, text ) RETURNS TEXT AS $$
+ SELECT pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
diff --git a/contrib/citext/citext--1.5--1.6.sql b/contrib/citext/citext--1.5--1.6.sql
new file mode 100644
index 0000000..3226898
--- /dev/null
+++ b/contrib/citext/citext--1.5--1.6.sql
@@ -0,0 +1,12 @@
+/* contrib/citext/citext--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.6'" to load this file. \quit
+
+CREATE FUNCTION citext_hash_extended(citext, int8)
+RETURNS int8
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+ALTER OPERATOR FAMILY citext_ops USING hash ADD
+ FUNCTION 2 citext_hash_extended(citext, int8);
diff --git a/contrib/citext/citext.c b/contrib/citext/citext.c
new file mode 100644
index 0000000..df13946
--- /dev/null
+++ b/contrib/citext/citext.c
@@ -0,0 +1,408 @@
+/*
+ * contrib/citext/citext.c
+ */
+#include "postgres.h"
+
+#include "catalog/pg_collation.h"
+#include "common/hashfn.h"
+#include "utils/builtins.h"
+#include "utils/formatting.h"
+#include "utils/varlena.h"
+
+PG_MODULE_MAGIC;
+
+/*
+ * ====================
+ * FORWARD DECLARATIONS
+ * ====================
+ */
+
+static int32 citextcmp(text *left, text *right, Oid collid);
+static int32 internal_citext_pattern_cmp(text *left, text *right, Oid collid);
+
+/*
+ * =================
+ * UTILITY FUNCTIONS
+ * =================
+ */
+
+/*
+ * citextcmp()
+ * Internal comparison function for citext strings.
+ * Returns int32 negative, zero, or positive.
+ */
+static int32
+citextcmp(text *left, text *right, Oid collid)
+{
+ char *lcstr,
+ *rcstr;
+ int32 result;
+
+ /*
+ * We must do our str_tolower calls with DEFAULT_COLLATION_OID, not the
+ * input collation as you might expect. This is so that the behavior of
+ * citext's equality and hashing functions is not collation-dependent. We
+ * should change this once the core infrastructure is able to cope with
+ * collation-dependent equality and hashing functions.
+ */
+
+ lcstr = str_tolower(VARDATA_ANY(left), VARSIZE_ANY_EXHDR(left), DEFAULT_COLLATION_OID);
+ rcstr = str_tolower(VARDATA_ANY(right), VARSIZE_ANY_EXHDR(right), DEFAULT_COLLATION_OID);
+
+ result = varstr_cmp(lcstr, strlen(lcstr),
+ rcstr, strlen(rcstr),
+ collid);
+
+ pfree(lcstr);
+ pfree(rcstr);
+
+ return result;
+}
+
+/*
+ * citext_pattern_cmp()
+ * Internal character-by-character comparison function for citext strings.
+ * Returns int32 negative, zero, or positive.
+ */
+static int32
+internal_citext_pattern_cmp(text *left, text *right, Oid collid)
+{
+ char *lcstr,
+ *rcstr;
+ int llen,
+ rlen;
+ int32 result;
+
+ lcstr = str_tolower(VARDATA_ANY(left), VARSIZE_ANY_EXHDR(left), DEFAULT_COLLATION_OID);
+ rcstr = str_tolower(VARDATA_ANY(right), VARSIZE_ANY_EXHDR(right), DEFAULT_COLLATION_OID);
+
+ llen = strlen(lcstr);
+ rlen = strlen(rcstr);
+
+ result = memcmp((void *) lcstr, (void *) rcstr, Min(llen, rlen));
+ if (result == 0)
+ {
+ if (llen < rlen)
+ result = -1;
+ else if (llen > rlen)
+ result = 1;
+ }
+
+ pfree(lcstr);
+ pfree(rcstr);
+
+ return result;
+}
+
+/*
+ * ==================
+ * INDEXING FUNCTIONS
+ * ==================
+ */
+
+PG_FUNCTION_INFO_V1(citext_cmp);
+
+Datum
+citext_cmp(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ int32 result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION());
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_INT32(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_cmp);
+
+Datum
+citext_pattern_cmp(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ int32 result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION());
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_INT32(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_hash);
+
+Datum
+citext_hash(PG_FUNCTION_ARGS)
+{
+ text *txt = PG_GETARG_TEXT_PP(0);
+ char *str;
+ Datum result;
+
+ str = str_tolower(VARDATA_ANY(txt), VARSIZE_ANY_EXHDR(txt), DEFAULT_COLLATION_OID);
+ result = hash_any((unsigned char *) str, strlen(str));
+ pfree(str);
+
+ /* Avoid leaking memory for toasted inputs */
+ PG_FREE_IF_COPY(txt, 0);
+
+ PG_RETURN_DATUM(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_hash_extended);
+
+Datum
+citext_hash_extended(PG_FUNCTION_ARGS)
+{
+ text *txt = PG_GETARG_TEXT_PP(0);
+ uint64 seed = PG_GETARG_INT64(1);
+ char *str;
+ Datum result;
+
+ str = str_tolower(VARDATA_ANY(txt), VARSIZE_ANY_EXHDR(txt), DEFAULT_COLLATION_OID);
+ result = hash_any_extended((unsigned char *) str, strlen(str), seed);
+ pfree(str);
+
+ /* Avoid leaking memory for toasted inputs */
+ PG_FREE_IF_COPY(txt, 0);
+
+ PG_RETURN_DATUM(result);
+}
+
+/*
+ * ==================
+ * OPERATOR FUNCTIONS
+ * ==================
+ */
+
+PG_FUNCTION_INFO_V1(citext_eq);
+
+Datum
+citext_eq(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ char *lcstr,
+ *rcstr;
+ bool result;
+
+ /* We can't compare lengths in advance of downcasing ... */
+
+ lcstr = str_tolower(VARDATA_ANY(left), VARSIZE_ANY_EXHDR(left), DEFAULT_COLLATION_OID);
+ rcstr = str_tolower(VARDATA_ANY(right), VARSIZE_ANY_EXHDR(right), DEFAULT_COLLATION_OID);
+
+ /*
+ * Since we only care about equality or not-equality, we can avoid all the
+ * expense of strcoll() here, and just do bitwise comparison.
+ */
+ result = (strcmp(lcstr, rcstr) == 0);
+
+ pfree(lcstr);
+ pfree(rcstr);
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_ne);
+
+Datum
+citext_ne(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ char *lcstr,
+ *rcstr;
+ bool result;
+
+ /* We can't compare lengths in advance of downcasing ... */
+
+ lcstr = str_tolower(VARDATA_ANY(left), VARSIZE_ANY_EXHDR(left), DEFAULT_COLLATION_OID);
+ rcstr = str_tolower(VARDATA_ANY(right), VARSIZE_ANY_EXHDR(right), DEFAULT_COLLATION_OID);
+
+ /*
+ * Since we only care about equality or not-equality, we can avoid all the
+ * expense of strcoll() here, and just do bitwise comparison.
+ */
+ result = (strcmp(lcstr, rcstr) != 0);
+
+ pfree(lcstr);
+ pfree(rcstr);
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_lt);
+
+Datum
+citext_lt(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION()) < 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_le);
+
+Datum
+citext_le(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION()) <= 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_gt);
+
+Datum
+citext_gt(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION()) > 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_ge);
+
+Datum
+citext_ge(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION()) >= 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_lt);
+
+Datum
+citext_pattern_lt(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) < 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_le);
+
+Datum
+citext_pattern_le(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) <= 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_gt);
+
+Datum
+citext_pattern_gt(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) > 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_ge);
+
+Datum
+citext_pattern_ge(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) >= 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+/*
+ * ===================
+ * AGGREGATE FUNCTIONS
+ * ===================
+ */
+
+PG_FUNCTION_INFO_V1(citext_smaller);
+
+Datum
+citext_smaller(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ text *result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION()) < 0 ? left : right;
+ PG_RETURN_TEXT_P(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_larger);
+
+Datum
+citext_larger(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ text *result;
+
+ result = citextcmp(left, right, PG_GET_COLLATION()) > 0 ? left : right;
+ PG_RETURN_TEXT_P(result);
+}
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
new file mode 100644
index 0000000..ccf4454
--- /dev/null
+++ b/contrib/citext/citext.control
@@ -0,0 +1,6 @@
+# citext extension
+comment = 'data type for case-insensitive character strings'
+default_version = '1.6'
+module_pathname = '$libdir/citext'
+relocatable = true
+trusted = true
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out
new file mode 100644
index 0000000..1c55598
--- /dev/null
+++ b/contrib/citext/expected/citext.out
@@ -0,0 +1,2693 @@
+--
+-- Test citext datatype
+--
+CREATE EXTENSION citext;
+-- Check whether any of our opclasses fail amvalidate
+SELECT amname, opcname
+FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
+WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
+ amname | opcname
+--------+---------
+(0 rows)
+
+-- Test the operators and indexing functions
+-- Test = and <>.
+SELECT 'a'::citext = 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext = 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext = 'A'::text AS f; -- text wins the discussion
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext = 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext = 'ab'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext <> 'ab'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test > and >=
+SELECT 'B'::citext > 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext > 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext > 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext >= 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test < and <=
+SELECT 'a'::citext < 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext <= 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'a'::citext = 'a'::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'A'::text <> 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext < 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext <= 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext > 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext >= 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'a'::citext = 'a'::varchar AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'A'::varchar <> 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext < 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext <= 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext > 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext >= 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+-- A couple of longer examples to ensure that we don't get any issues with bad
+-- conversions to char[] in the c code. Yes, I did do this.
+SELECT 'aardvark'::citext = 'aardvark'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Check the citext_cmp() function explicitly.
+SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+-- Check the citext_hash() and citext_hash_extended() function explicitly.
+SELECT v as value, citext_hash(v)::bit(32) as standard,
+ citext_hash_extended(v, 0)::bit(32) as extended0,
+ citext_hash_extended(v, 1)::bit(32) as extended1
+FROM (VALUES (NULL::citext), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'),
+ ('muop28x03'), ('yi3nm0d73')) x(v)
+WHERE citext_hash(v)::bit(32) != citext_hash_extended(v, 0)::bit(32)
+ OR citext_hash(v)::bit(32) = citext_hash_extended(v, 1)::bit(32);
+ value | standard | extended0 | extended1
+-------+----------+-----------+-----------
+(0 rows)
+
+-- Do some tests using a table and index.
+CREATE TEMP TABLE try (
+ name citext PRIMARY KEY
+);
+INSERT INTO try (name)
+VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');
+SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â';
+ name | eq_a
+------+------
+ a | t
+ ab | f
+ aba | f
+ b | f
+ ba | f
+ bab | f
+ AZ | f
+(7 rows)
+
+SELECT name, 'a' = name AS t FROM try where name = 'a';
+ name | t
+------+---
+ a | t
+(1 row)
+
+SELECT name, 'A' = name AS "eq_A" FROM try WHERE name <> 'â';
+ name | eq_A
+------+------
+ a | t
+ ab | f
+ aba | f
+ b | f
+ ba | f
+ bab | f
+ AZ | f
+(7 rows)
+
+SELECT name, 'A' = name AS t FROM try where name = 'A';
+ name | t
+------+---
+ a | t
+(1 row)
+
+SELECT name, 'A' = name AS t FROM try where name = 'A';
+ name | t
+------+---
+ a | t
+(1 row)
+
+-- expected failures on duplicate key
+INSERT INTO try (name) VALUES ('a');
+ERROR: duplicate key value violates unique constraint "try_pkey"
+DETAIL: Key (name)=(a) already exists.
+INSERT INTO try (name) VALUES ('A');
+ERROR: duplicate key value violates unique constraint "try_pkey"
+DETAIL: Key (name)=(A) already exists.
+INSERT INTO try (name) VALUES ('aB');
+ERROR: duplicate key value violates unique constraint "try_pkey"
+DETAIL: Key (name)=(aB) already exists.
+-- Make sure that citext_smaller() and citext_larger() work properly.
+SELECT citext_smaller( 'ab'::citext, 'ac'::citext ) = 'ab' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_smaller( 'ABC'::citext, 'bbbb'::citext ) = 'ABC' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_smaller( 'aardvark'::citext, 'Aaba'::citext ) = 'Aaba' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ) = 'AARDVARK' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_larger( 'ab'::citext, 'ac'::citext ) = 'ac' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_larger( 'ABC'::citext, 'bbbb'::citext ) = 'bbbb' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_larger( 'aardvark'::citext, 'Aaba'::citext ) = 'aardvark' AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test aggregate functions and sort ordering
+CREATE TEMP TABLE srt (
+ name CITEXT
+);
+INSERT INTO srt (name)
+VALUES ('abb'),
+ ('ABA'),
+ ('ABC'),
+ ('abd');
+CREATE INDEX srt_name ON srt (name);
+-- Check the min() and max() aggregates, with and without index.
+set enable_seqscan = off;
+SELECT MIN(name) AS "ABA" FROM srt;
+ ABA
+-----
+ ABA
+(1 row)
+
+SELECT MAX(name) AS abd FROM srt;
+ abd
+-----
+ abd
+(1 row)
+
+reset enable_seqscan;
+set enable_indexscan = off;
+SELECT MIN(name) AS "ABA" FROM srt;
+ ABA
+-----
+ ABA
+(1 row)
+
+SELECT MAX(name) AS abd FROM srt;
+ abd
+-----
+ abd
+(1 row)
+
+reset enable_indexscan;
+-- Check sorting likewise
+set enable_seqscan = off;
+SELECT name FROM srt ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+reset enable_seqscan;
+set enable_indexscan = off;
+SELECT name FROM srt ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+reset enable_indexscan;
+-- Test assignment casts.
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::text;
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::varchar;
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::bpchar;
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA';
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::citext;
+ aba
+-----
+ aba
+(1 row)
+
+-- LIKE should be case-insensitive
+SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name;
+ name
+------
+(0 rows)
+
+SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name;
+ name
+------
+(0 rows)
+
+-- ~~ should be case-insensitive
+SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name;
+ name
+------
+(0 rows)
+
+SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name;
+ name
+------
+(0 rows)
+
+-- ~ should be case-insensitive
+SELECT name FROM srt WHERE name ~ '^a' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name;
+ name
+------
+ abb
+ ABC
+ abd
+(3 rows)
+
+SELECT name FROM srt WHERE name ~ '^A' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name;
+ name
+------
+ abb
+ ABC
+ abd
+(3 rows)
+
+-- SIMILAR TO should be case-insensitive.
+SELECT name FROM srt WHERE name SIMILAR TO '%a.*';
+ name
+------
+ ABA
+(1 row)
+
+SELECT name FROM srt WHERE name SIMILAR TO '%A.*';
+ name
+------
+ ABA
+(1 row)
+
+-- Explicit casts.
+SELECT true::citext = 'true' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'true'::citext::boolean = true AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::int4::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::int4 = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::integer::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::integer = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::int8::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::int8 = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::bigint::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::bigint = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::int2::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::int2 = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::smallint::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::smallint = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::numeric = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::numeric = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::decimal = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::decimal = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::real = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::real = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::float4 = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::float4 = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::double precision = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::double precision = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::float8 = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::float8 = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::name::citext = 'foo' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::citext::name = 'foo'::name AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::char::citext = 'f' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::citext::char = 'f'::char AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::"char"::citext = 'f' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::citext::"char" = 'f'::"char" AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '100'::money::citext = '$100.00' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '100'::citext::money = '100'::money AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::char::citext = 'a' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext::char = 'a'::char AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::varchar::citext = 'foo' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::text::citext = 'foo' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::citext::text = 'foo'::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::time::citext = '04:05:06' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::citext::time = '04:05:06'::time AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::bit::citext = '101'::bit::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::citext::bit = '101'::text::bit AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::bit varying::citext = '101'::bit varying::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::citext::bit varying = '101'::text::bit varying AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
+SELECT 'sad'::mood::citext = 'sad' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'sad'::citext::mood = 'sad'::mood AS t;
+ t
+---
+ t
+(1 row)
+
+-- Assignment casts.
+CREATE TABLE caster (
+ citext citext,
+ text text,
+ varchar varchar,
+ bpchar bpchar,
+ char char,
+ chr "char",
+ name name,
+ bytea bytea,
+ boolean boolean,
+ float4 float4,
+ float8 float8,
+ numeric numeric,
+ int8 int8,
+ int4 int4,
+ int2 int2,
+ cidr cidr,
+ inet inet,
+ macaddr macaddr,
+ money money,
+ timestamp timestamp,
+ timestamptz timestamptz,
+ interval interval,
+ date date,
+ time time,
+ timetz timetz,
+ point point,
+ lseg lseg,
+ box box,
+ path path,
+ polygon polygon,
+ circle circle,
+ bit bit,
+ bitv bit varying,
+ tsvector tsvector,
+ tsquery tsquery,
+ uuid uuid
+);
+INSERT INTO caster (text) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::text);
+INSERT INTO caster (varchar) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::varchar);
+INSERT INTO caster (varchar) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::varchar);
+INSERT INTO caster (bpchar) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::bpchar);
+INSERT INTO caster (bpchar) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::bpchar);
+INSERT INTO caster (char) VALUES ('f'::text);
+INSERT INTO caster (text) VALUES ('f'::char);
+INSERT INTO caster (char) VALUES ('f'::citext);
+INSERT INTO caster (citext) VALUES ('f'::char);
+INSERT INTO caster (chr) VALUES ('f'::text);
+INSERT INTO caster (text) VALUES ('f'::"char");
+INSERT INTO caster (chr) VALUES ('f'::citext); -- requires cast
+ERROR: column "chr" is of type "char" but expression is of type citext
+LINE 1: INSERT INTO caster (chr) VALUES ('f'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (chr) VALUES ('f'::citext::text);
+INSERT INTO caster (citext) VALUES ('f'::"char");
+INSERT INTO caster (name) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::name);
+INSERT INTO caster (name) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::name);
+-- Cannot cast to bytea on assignment.
+INSERT INTO caster (bytea) VALUES ('foo'::text);
+ERROR: column "bytea" is of type bytea but expression is of type text
+LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('foo'::bytea);
+INSERT INTO caster (bytea) VALUES ('foo'::citext);
+ERROR: column "bytea" is of type bytea but expression is of type citext
+LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('foo'::bytea);
+-- Cannot cast to boolean on assignment.
+INSERT INTO caster (boolean) VALUES ('t'::text);
+ERROR: column "boolean" is of type boolean but expression is of type text
+LINE 1: INSERT INTO caster (boolean) VALUES ('t'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('t'::boolean);
+INSERT INTO caster (boolean) VALUES ('t'::citext);
+ERROR: column "boolean" is of type boolean but expression is of type citext
+LINE 1: INSERT INTO caster (boolean) VALUES ('t'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('t'::boolean);
+-- Cannot cast to float8 on assignment.
+INSERT INTO caster (float8) VALUES ('12.42'::text);
+ERROR: column "float8" is of type double precision but expression is of type text
+LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12.42'::float8);
+INSERT INTO caster (float8) VALUES ('12.42'::citext);
+ERROR: column "float8" is of type double precision but expression is of type citext
+LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12.42'::float8);
+-- Cannot cast to float4 on assignment.
+INSERT INTO caster (float4) VALUES ('12.42'::text);
+ERROR: column "float4" is of type real but expression is of type text
+LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12.42'::float4);
+INSERT INTO caster (float4) VALUES ('12.42'::citext);
+ERROR: column "float4" is of type real but expression is of type citext
+LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12.42'::float4);
+-- Cannot cast to numeric on assignment.
+INSERT INTO caster (numeric) VALUES ('12.42'::text);
+ERROR: column "numeric" is of type numeric but expression is of type text
+LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12.42'::numeric);
+INSERT INTO caster (numeric) VALUES ('12.42'::citext);
+ERROR: column "numeric" is of type numeric but expression is of type citext
+LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12.42'::numeric);
+-- Cannot cast to int8 on assignment.
+INSERT INTO caster (int8) VALUES ('12'::text);
+ERROR: column "int8" is of type bigint but expression is of type text
+LINE 1: INSERT INTO caster (int8) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::int8);
+INSERT INTO caster (int8) VALUES ('12'::citext);
+ERROR: column "int8" is of type bigint but expression is of type citext
+LINE 1: INSERT INTO caster (int8) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::int8);
+-- Cannot cast to int4 on assignment.
+INSERT INTO caster (int4) VALUES ('12'::text);
+ERROR: column "int4" is of type integer but expression is of type text
+LINE 1: INSERT INTO caster (int4) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::int4);
+INSERT INTO caster (int4) VALUES ('12'::citext);
+ERROR: column "int4" is of type integer but expression is of type citext
+LINE 1: INSERT INTO caster (int4) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::int4);
+-- Cannot cast to int2 on assignment.
+INSERT INTO caster (int2) VALUES ('12'::text);
+ERROR: column "int2" is of type smallint but expression is of type text
+LINE 1: INSERT INTO caster (int2) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::int2);
+INSERT INTO caster (int2) VALUES ('12'::citext);
+ERROR: column "int2" is of type smallint but expression is of type citext
+LINE 1: INSERT INTO caster (int2) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::int2);
+-- Cannot cast to cidr on assignment.
+INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text);
+ERROR: column "cidr" is of type cidr but expression is of type text
+LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr);
+INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext);
+ERROR: column "cidr" is of type cidr but expression is of type citext
+LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr);
+-- Cannot cast to inet on assignment.
+INSERT INTO caster (inet) VALUES ('192.168.100.128'::text);
+ERROR: column "inet" is of type inet but expression is of type text
+LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('192.168.100.128'::inet);
+INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext);
+ERROR: column "inet" is of type inet but expression is of type citext
+LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet);
+-- Cannot cast to macaddr on assignment.
+INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text);
+ERROR: column "macaddr" is of type macaddr but expression is of type text
+LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr);
+INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext);
+ERROR: column "macaddr" is of type macaddr but expression is of type citext
+LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr);
+-- Cannot cast to money on assignment.
+INSERT INTO caster (money) VALUES ('12'::text);
+ERROR: column "money" is of type money but expression is of type text
+LINE 1: INSERT INTO caster (money) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::money);
+INSERT INTO caster (money) VALUES ('12'::citext);
+ERROR: column "money" is of type money but expression is of type citext
+LINE 1: INSERT INTO caster (money) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::money);
+-- Cannot cast to timestamp on assignment.
+INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text);
+ERROR: column "timestamp" is of type timestamp without time zone but expression is of type text
+LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp);
+INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext);
+ERROR: column "timestamp" is of type timestamp without time zone but expression is of type citext
+LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp);
+-- Cannot cast to timestamptz on assignment.
+INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text);
+ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type text
+LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz);
+INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext);
+ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type citext
+LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz);
+-- Cannot cast to interval on assignment.
+INSERT INTO caster (interval) VALUES ('1 hour'::text);
+ERROR: column "interval" is of type interval but expression is of type text
+LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1 hour'::interval);
+INSERT INTO caster (interval) VALUES ('1 hour'::citext);
+ERROR: column "interval" is of type interval but expression is of type citext
+LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::citext)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1 hour'::interval);
+-- Cannot cast to date on assignment.
+INSERT INTO caster (date) VALUES ('1999-01-08'::text);
+ERROR: column "date" is of type date but expression is of type text
+LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::tex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1999-01-08'::date);
+INSERT INTO caster (date) VALUES ('1999-01-08'::citext);
+ERROR: column "date" is of type date but expression is of type citext
+LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::cit...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1999-01-08'::date);
+-- Cannot cast to time on assignment.
+INSERT INTO caster (time) VALUES ('04:05:06'::text);
+ERROR: column "time" is of type time without time zone but expression is of type text
+LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::text)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('04:05:06'::time);
+INSERT INTO caster (time) VALUES ('04:05:06'::citext);
+ERROR: column "time" is of type time without time zone but expression is of type citext
+LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::citex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('04:05:06'::time);
+-- Cannot cast to timetz on assignment.
+INSERT INTO caster (timetz) VALUES ('04:05:06'::text);
+ERROR: column "timetz" is of type time with time zone but expression is of type text
+LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::text)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('04:05:06'::timetz);
+INSERT INTO caster (timetz) VALUES ('04:05:06'::citext);
+ERROR: column "timetz" is of type time with time zone but expression is of type citext
+LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::citex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('04:05:06'::timetz);
+-- Cannot cast to point on assignment.
+INSERT INTO caster (point) VALUES ('( 1 , 1)'::text);
+ERROR: column "point" is of type point but expression is of type text
+LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::text)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('( 1 , 1)'::point);
+INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext);
+ERROR: column "point" is of type point but expression is of type citext
+LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::citex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point);
+-- Cannot cast to lseg on assignment.
+INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text);
+ERROR: column "lseg" is of type lseg but expression is of type text
+LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
+INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext);
+ERROR: column "lseg" is of type lseg but expression is of type citext
+LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
+-- Cannot cast to box on assignment.
+INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text);
+ERROR: column "box" is of type box but expression is of type text
+LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::te...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box);
+INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext);
+ERROR: column "box" is of type box but expression is of type citext
+LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::ci...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box);
+-- Cannot cast to path on assignment.
+INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text);
+ERROR: column "path" is of type path but expression is of type text
+LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path);
+INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext);
+ERROR: column "path" is of type path but expression is of type citext
+LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path);
+-- Cannot cast to polygon on assignment.
+INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text);
+ERROR: column "polygon" is of type polygon but expression is of type text
+LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon);
+INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext);
+ERROR: column "polygon" is of type polygon but expression is of type citext
+LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon);
+-- Cannot cast to circle on assignment.
+INSERT INTO caster (circle) VALUES ('((0,0),2)'::text);
+ERROR: column "circle" is of type circle but expression is of type text
+LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::text...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('((0,0),2)'::circle);
+INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext);
+ERROR: column "circle" is of type circle but expression is of type citext
+LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::cite...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle);
+-- Cannot cast to bit on assignment.
+INSERT INTO caster (bit) VALUES ('101'::text);
+ERROR: column "bit" is of type bit but expression is of type text
+LINE 1: INSERT INTO caster (bit) VALUES ('101'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('101'::bit);
+INSERT INTO caster (bit) VALUES ('101'::citext);
+ERROR: column "bit" is of type bit but expression is of type citext
+LINE 1: INSERT INTO caster (bit) VALUES ('101'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('101'::bit);
+-- Cannot cast to bit varying on assignment.
+INSERT INTO caster (bitv) VALUES ('101'::text);
+ERROR: column "bitv" is of type bit varying but expression is of type text
+LINE 1: INSERT INTO caster (bitv) VALUES ('101'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('101'::bit varying);
+INSERT INTO caster (bitv) VALUES ('101'::citext);
+ERROR: column "bitv" is of type bit varying but expression is of type citext
+LINE 1: INSERT INTO caster (bitv) VALUES ('101'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('101'::bit varying);
+-- Cannot cast to tsvector on assignment.
+INSERT INTO caster (tsvector) VALUES ('the fat cat'::text);
+ERROR: column "tsvector" is of type tsvector but expression is of type text
+LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::te...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('the fat cat'::tsvector);
+INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext);
+ERROR: column "tsvector" is of type tsvector but expression is of type citext
+LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::ci...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector);
+-- Cannot cast to tsquery on assignment.
+INSERT INTO caster (tsquery) VALUES ('fat & rat'::text);
+ERROR: column "tsquery" is of type tsquery but expression is of type text
+LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::text...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('fat & rat'::tsquery);
+INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext);
+ERROR: column "tsquery" is of type tsquery but expression is of type citext
+LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::cite...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery);
+-- Cannot cast to uuid on assignment.
+INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);
+ERROR: column "uuid" is of type uuid but expression is of type text
+LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
+INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext);
+ERROR: column "uuid" is of type uuid but expression is of type citext
+LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
+-- Table 9-5. SQL String Functions and Operators
+SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat;
+ citext_concat
+---------------
+ t
+(1 row)
+
+SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat;
+ text_concat
+-------------
+ t
+(1 row)
+
+SELECT 42 || ': value'::citext ='42: value' AS int_concat;
+ int_concat
+------------
+ t
+(1 row)
+
+SELECT bit_length('jose'::citext) = 32 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT textlen( name ) = textlen( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT char_length( name ) = char_length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT lower( name ) = lower( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT substr('alphabet'::citext, 3) = 'phabet' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('alphabet'::citext, 3) = 'phabet' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext from 2) = 'homas' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext from '...$') = 'mas' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext similar '%#"o_a#"_' escape '#') = 'oma' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim(' trim '::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT upper( name ) = upper( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Table 9-6. Other String Functions.
+SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT btrim(' trim'::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+-- chr() takes an int and returns text.
+-- convert() and convert_from take bytea and return text.
+SELECT convert_from( name::bytea, 'SQL_ASCII' ) = convert_from( name::text::bytea, 'SQL_ASCII' ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t;
+ t
+---
+ t
+(1 row)
+
+-- encode() takes bytea and returns text.
+SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT length( name ) = length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT lpad('hi'::citext, 5 ) = ' hi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim(' trim'::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT md5( name ) = md5( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- pg_client_encoding() takes no args and returns name.
+SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no result";
+ no result
+-----------
+
+(1 row)
+
+-- g is not allowed
+SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error";
+ERROR: regexp_match() does not support the "global" option
+HINT: Use the regexp_matches function instead.
+CONTEXT: SQL function "regexp_match" statement 1
+SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no rows";
+ no rows
+---------
+(0 rows)
+
+-- g allows multiple output rows
+SELECT regexp_matches('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g'::citext) AS "two rows";
+ two rows
+-------------
+ {bar,beque}
+ {bar,beque}
+(2 rows)
+
+SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+-- c forces case-sensitive
+SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word;
+ word
+-------------
+ helloTworld
+(1 row)
+
+SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trim '::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'gh' ) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high', 'gh'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'gh'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'GH' ) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high', 'GH'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'GH'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+-- to_ascii() does not support UTF-8.
+-- to_hex() takes a numeric argument.
+SELECT substr('alphabet', 3, 2) = 'ph' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+-- Table 9-20. Formatting Functions
+SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY')
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext)
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext)
+ = to_number('12,454.8-', '99G999D9S') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_number('12,454.8-'::citext, '99G999D9S')
+ = to_number('12,454.8-', '99G999D9S') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_number('12,454.8-', '99G999D9S'::citext)
+ = to_number('12,454.8-', '99G999D9S') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY')
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext)
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+-- Try assigning function results to a column.
+SELECT COUNT(*) = 8::bigint AS t FROM try;
+ t
+---
+ t
+(1 row)
+
+INSERT INTO try
+VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ),
+ ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timestamptz
+ ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ),
+ ( to_char( current_date, '999') ),
+ ( to_char( 125::int, '999') ),
+ ( to_char( 127::int4, '999') ),
+ ( to_char( 126::int8, '999') ),
+ ( to_char( 128.8::real, '999D9') ),
+ ( to_char( 125.7::float4, '999D9') ),
+ ( to_char( 125.9::float8, '999D9') ),
+ ( to_char( -125.8::numeric, '999D99S') );
+SELECT COUNT(*) = 19::bigint AS t FROM try;
+ t
+---
+ t
+(1 row)
+
+SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Ensure correct behavior for citext with materialized views.
+CREATE TABLE citext_table (
+ id serial primary key,
+ name citext
+);
+INSERT INTO citext_table (name)
+ VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
+CREATE MATERIALIZED VIEW citext_matview AS
+ SELECT * FROM citext_table;
+CREATE UNIQUE INDEX citext_matview_id
+ ON citext_matview (id);
+SELECT *
+ FROM citext_matview m
+ FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+ WHERE t.id IS NULL OR m.id IS NULL;
+ id | name | id | name
+----+------+----+------
+(0 rows)
+
+UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
+SELECT *
+ FROM citext_matview m
+ FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+ WHERE t.id IS NULL OR m.id IS NULL;
+ id | name | id | name
+----+------+----+------
+ | | 2 | Two
+ 2 | two | |
+(2 rows)
+
+REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
+SELECT * FROM citext_matview ORDER BY id;
+ id | name
+----+-------
+ 1 | one
+ 2 | Two
+ 3 | three
+ 4 |
+ 5 |
+(5 rows)
+
+-- test citext_pattern_cmp() function explicitly.
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+-- test operator functions
+-- lt
+SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- le
+SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- gt
+SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- ge
+SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- Test ~<~ and ~<=~
+SELECT 'a'::citext ~<~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~<~ 'A'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out
new file mode 100644
index 0000000..4a979d7
--- /dev/null
+++ b/contrib/citext/expected/citext_1.out
@@ -0,0 +1,2693 @@
+--
+-- Test citext datatype
+--
+CREATE EXTENSION citext;
+-- Check whether any of our opclasses fail amvalidate
+SELECT amname, opcname
+FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
+WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
+ amname | opcname
+--------+---------
+(0 rows)
+
+-- Test the operators and indexing functions
+-- Test = and <>.
+SELECT 'a'::citext = 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext = 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext = 'A'::text AS f; -- text wins the discussion
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext = 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext = 'ab'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext <> 'ab'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test > and >=
+SELECT 'B'::citext > 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext > 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext > 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext >= 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test < and <=
+SELECT 'a'::citext < 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext <= 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'a'::citext = 'a'::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'A'::text <> 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext < 'a'::text AS t; -- text wins.
+ t
+---
+ f
+(1 row)
+
+SELECT 'B'::citext <= 'a'::text AS t; -- text wins.
+ t
+---
+ f
+(1 row)
+
+SELECT 'a'::citext > 'B'::text AS t; -- text wins.
+ t
+---
+ f
+(1 row)
+
+SELECT 'a'::citext >= 'B'::text AS t; -- text wins.
+ t
+---
+ f
+(1 row)
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'a'::citext = 'a'::varchar AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'A'::varchar <> 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext < 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ f
+(1 row)
+
+SELECT 'B'::citext <= 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ f
+(1 row)
+
+SELECT 'a'::citext > 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ f
+(1 row)
+
+SELECT 'a'::citext >= 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ f
+(1 row)
+
+-- A couple of longer examples to ensure that we don't get any issues with bad
+-- conversions to char[] in the c code. Yes, I did do this.
+SELECT 'aardvark'::citext = 'aardvark'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Check the citext_cmp() function explicitly.
+SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+-- Check the citext_hash() and citext_hash_extended() function explicitly.
+SELECT v as value, citext_hash(v)::bit(32) as standard,
+ citext_hash_extended(v, 0)::bit(32) as extended0,
+ citext_hash_extended(v, 1)::bit(32) as extended1
+FROM (VALUES (NULL::citext), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'),
+ ('muop28x03'), ('yi3nm0d73')) x(v)
+WHERE citext_hash(v)::bit(32) != citext_hash_extended(v, 0)::bit(32)
+ OR citext_hash(v)::bit(32) = citext_hash_extended(v, 1)::bit(32);
+ value | standard | extended0 | extended1
+-------+----------+-----------+-----------
+(0 rows)
+
+-- Do some tests using a table and index.
+CREATE TEMP TABLE try (
+ name citext PRIMARY KEY
+);
+INSERT INTO try (name)
+VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');
+SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â';
+ name | eq_a
+------+------
+ a | t
+ ab | f
+ aba | f
+ b | f
+ ba | f
+ bab | f
+ AZ | f
+(7 rows)
+
+SELECT name, 'a' = name AS t FROM try where name = 'a';
+ name | t
+------+---
+ a | t
+(1 row)
+
+SELECT name, 'A' = name AS "eq_A" FROM try WHERE name <> 'â';
+ name | eq_A
+------+------
+ a | t
+ ab | f
+ aba | f
+ b | f
+ ba | f
+ bab | f
+ AZ | f
+(7 rows)
+
+SELECT name, 'A' = name AS t FROM try where name = 'A';
+ name | t
+------+---
+ a | t
+(1 row)
+
+SELECT name, 'A' = name AS t FROM try where name = 'A';
+ name | t
+------+---
+ a | t
+(1 row)
+
+-- expected failures on duplicate key
+INSERT INTO try (name) VALUES ('a');
+ERROR: duplicate key value violates unique constraint "try_pkey"
+DETAIL: Key (name)=(a) already exists.
+INSERT INTO try (name) VALUES ('A');
+ERROR: duplicate key value violates unique constraint "try_pkey"
+DETAIL: Key (name)=(A) already exists.
+INSERT INTO try (name) VALUES ('aB');
+ERROR: duplicate key value violates unique constraint "try_pkey"
+DETAIL: Key (name)=(aB) already exists.
+-- Make sure that citext_smaller() and citext_larger() work properly.
+SELECT citext_smaller( 'ab'::citext, 'ac'::citext ) = 'ab' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_smaller( 'ABC'::citext, 'bbbb'::citext ) = 'ABC' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_smaller( 'aardvark'::citext, 'Aaba'::citext ) = 'Aaba' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ) = 'AARDVARK' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_larger( 'ab'::citext, 'ac'::citext ) = 'ac' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_larger( 'ABC'::citext, 'bbbb'::citext ) = 'bbbb' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_larger( 'aardvark'::citext, 'Aaba'::citext ) = 'aardvark' AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test aggregate functions and sort ordering
+CREATE TEMP TABLE srt (
+ name CITEXT
+);
+INSERT INTO srt (name)
+VALUES ('abb'),
+ ('ABA'),
+ ('ABC'),
+ ('abd');
+CREATE INDEX srt_name ON srt (name);
+-- Check the min() and max() aggregates, with and without index.
+set enable_seqscan = off;
+SELECT MIN(name) AS "ABA" FROM srt;
+ ABA
+-----
+ ABA
+(1 row)
+
+SELECT MAX(name) AS abd FROM srt;
+ abd
+-----
+ abd
+(1 row)
+
+reset enable_seqscan;
+set enable_indexscan = off;
+SELECT MIN(name) AS "ABA" FROM srt;
+ ABA
+-----
+ ABA
+(1 row)
+
+SELECT MAX(name) AS abd FROM srt;
+ abd
+-----
+ abd
+(1 row)
+
+reset enable_indexscan;
+-- Check sorting likewise
+set enable_seqscan = off;
+SELECT name FROM srt ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+reset enable_seqscan;
+set enable_indexscan = off;
+SELECT name FROM srt ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+reset enable_indexscan;
+-- Test assignment casts.
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::text;
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::varchar;
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::bpchar;
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA';
+ aba
+-----
+ aba
+(1 row)
+
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::citext;
+ aba
+-----
+ aba
+(1 row)
+
+-- LIKE should be case-insensitive
+SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name;
+ name
+------
+(0 rows)
+
+SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name;
+ name
+------
+(0 rows)
+
+-- ~~ should be case-insensitive
+SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name;
+ name
+------
+(0 rows)
+
+SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name;
+ name
+------
+(0 rows)
+
+-- ~ should be case-insensitive
+SELECT name FROM srt WHERE name ~ '^a' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name;
+ name
+------
+ abb
+ ABC
+ abd
+(3 rows)
+
+SELECT name FROM srt WHERE name ~ '^A' ORDER BY name;
+ name
+------
+ ABA
+ abb
+ ABC
+ abd
+(4 rows)
+
+SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name;
+ name
+------
+ abb
+ ABC
+ abd
+(3 rows)
+
+-- SIMILAR TO should be case-insensitive.
+SELECT name FROM srt WHERE name SIMILAR TO '%a.*';
+ name
+------
+ ABA
+(1 row)
+
+SELECT name FROM srt WHERE name SIMILAR TO '%A.*';
+ name
+------
+ ABA
+(1 row)
+
+-- Explicit casts.
+SELECT true::citext = 'true' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'true'::citext::boolean = true AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::int4::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::int4 = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::integer::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::integer = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::int8::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::int8 = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::bigint::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::bigint = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::int2::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::int2 = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4::smallint::citext = '4' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4'::citext::smallint = 4 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::numeric = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::numeric = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::decimal = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::decimal = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::real = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::real = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::float4 = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::float4 = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::double precision = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::double precision = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 4.0::float8 = '4.0' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '4.0'::citext::float8 = 4.0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::name::citext = 'foo' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::citext::name = 'foo'::name AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::char::citext = 'f' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::citext::char = 'f'::char AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::"char"::citext = 'f' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'f'::citext::"char" = 'f'::"char" AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '100'::money::citext = '$100.00' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '100'::citext::money = '100'::money AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::char::citext = 'a' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext::char = 'a'::char AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::varchar::citext = 'foo' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::text::citext = 'foo' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'foo'::citext::text = 'foo'::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::time::citext = '04:05:06' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::citext::time = '04:05:06'::time AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::bit::citext = '101'::bit::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::citext::bit = '101'::text::bit AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::bit varying::citext = '101'::bit varying::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT '101'::citext::bit varying = '101'::text::bit varying AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t;
+ t
+---
+ t
+(1 row)
+
+CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
+SELECT 'sad'::mood::citext = 'sad' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'sad'::citext::mood = 'sad'::mood AS t;
+ t
+---
+ t
+(1 row)
+
+-- Assignment casts.
+CREATE TABLE caster (
+ citext citext,
+ text text,
+ varchar varchar,
+ bpchar bpchar,
+ char char,
+ chr "char",
+ name name,
+ bytea bytea,
+ boolean boolean,
+ float4 float4,
+ float8 float8,
+ numeric numeric,
+ int8 int8,
+ int4 int4,
+ int2 int2,
+ cidr cidr,
+ inet inet,
+ macaddr macaddr,
+ money money,
+ timestamp timestamp,
+ timestamptz timestamptz,
+ interval interval,
+ date date,
+ time time,
+ timetz timetz,
+ point point,
+ lseg lseg,
+ box box,
+ path path,
+ polygon polygon,
+ circle circle,
+ bit bit,
+ bitv bit varying,
+ tsvector tsvector,
+ tsquery tsquery,
+ uuid uuid
+);
+INSERT INTO caster (text) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::text);
+INSERT INTO caster (varchar) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::varchar);
+INSERT INTO caster (varchar) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::varchar);
+INSERT INTO caster (bpchar) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::bpchar);
+INSERT INTO caster (bpchar) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::bpchar);
+INSERT INTO caster (char) VALUES ('f'::text);
+INSERT INTO caster (text) VALUES ('f'::char);
+INSERT INTO caster (char) VALUES ('f'::citext);
+INSERT INTO caster (citext) VALUES ('f'::char);
+INSERT INTO caster (chr) VALUES ('f'::text);
+INSERT INTO caster (text) VALUES ('f'::"char");
+INSERT INTO caster (chr) VALUES ('f'::citext); -- requires cast
+ERROR: column "chr" is of type "char" but expression is of type citext
+LINE 1: INSERT INTO caster (chr) VALUES ('f'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (chr) VALUES ('f'::citext::text);
+INSERT INTO caster (citext) VALUES ('f'::"char");
+INSERT INTO caster (name) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::name);
+INSERT INTO caster (name) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::name);
+-- Cannot cast to bytea on assignment.
+INSERT INTO caster (bytea) VALUES ('foo'::text);
+ERROR: column "bytea" is of type bytea but expression is of type text
+LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('foo'::bytea);
+INSERT INTO caster (bytea) VALUES ('foo'::citext);
+ERROR: column "bytea" is of type bytea but expression is of type citext
+LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('foo'::bytea);
+-- Cannot cast to boolean on assignment.
+INSERT INTO caster (boolean) VALUES ('t'::text);
+ERROR: column "boolean" is of type boolean but expression is of type text
+LINE 1: INSERT INTO caster (boolean) VALUES ('t'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('t'::boolean);
+INSERT INTO caster (boolean) VALUES ('t'::citext);
+ERROR: column "boolean" is of type boolean but expression is of type citext
+LINE 1: INSERT INTO caster (boolean) VALUES ('t'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('t'::boolean);
+-- Cannot cast to float8 on assignment.
+INSERT INTO caster (float8) VALUES ('12.42'::text);
+ERROR: column "float8" is of type double precision but expression is of type text
+LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12.42'::float8);
+INSERT INTO caster (float8) VALUES ('12.42'::citext);
+ERROR: column "float8" is of type double precision but expression is of type citext
+LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12.42'::float8);
+-- Cannot cast to float4 on assignment.
+INSERT INTO caster (float4) VALUES ('12.42'::text);
+ERROR: column "float4" is of type real but expression is of type text
+LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12.42'::float4);
+INSERT INTO caster (float4) VALUES ('12.42'::citext);
+ERROR: column "float4" is of type real but expression is of type citext
+LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12.42'::float4);
+-- Cannot cast to numeric on assignment.
+INSERT INTO caster (numeric) VALUES ('12.42'::text);
+ERROR: column "numeric" is of type numeric but expression is of type text
+LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12.42'::numeric);
+INSERT INTO caster (numeric) VALUES ('12.42'::citext);
+ERROR: column "numeric" is of type numeric but expression is of type citext
+LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12.42'::numeric);
+-- Cannot cast to int8 on assignment.
+INSERT INTO caster (int8) VALUES ('12'::text);
+ERROR: column "int8" is of type bigint but expression is of type text
+LINE 1: INSERT INTO caster (int8) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::int8);
+INSERT INTO caster (int8) VALUES ('12'::citext);
+ERROR: column "int8" is of type bigint but expression is of type citext
+LINE 1: INSERT INTO caster (int8) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::int8);
+-- Cannot cast to int4 on assignment.
+INSERT INTO caster (int4) VALUES ('12'::text);
+ERROR: column "int4" is of type integer but expression is of type text
+LINE 1: INSERT INTO caster (int4) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::int4);
+INSERT INTO caster (int4) VALUES ('12'::citext);
+ERROR: column "int4" is of type integer but expression is of type citext
+LINE 1: INSERT INTO caster (int4) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::int4);
+-- Cannot cast to int2 on assignment.
+INSERT INTO caster (int2) VALUES ('12'::text);
+ERROR: column "int2" is of type smallint but expression is of type text
+LINE 1: INSERT INTO caster (int2) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::int2);
+INSERT INTO caster (int2) VALUES ('12'::citext);
+ERROR: column "int2" is of type smallint but expression is of type citext
+LINE 1: INSERT INTO caster (int2) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::int2);
+-- Cannot cast to cidr on assignment.
+INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text);
+ERROR: column "cidr" is of type cidr but expression is of type text
+LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr);
+INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext);
+ERROR: column "cidr" is of type cidr but expression is of type citext
+LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr);
+-- Cannot cast to inet on assignment.
+INSERT INTO caster (inet) VALUES ('192.168.100.128'::text);
+ERROR: column "inet" is of type inet but expression is of type text
+LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('192.168.100.128'::inet);
+INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext);
+ERROR: column "inet" is of type inet but expression is of type citext
+LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet);
+-- Cannot cast to macaddr on assignment.
+INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text);
+ERROR: column "macaddr" is of type macaddr but expression is of type text
+LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr);
+INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext);
+ERROR: column "macaddr" is of type macaddr but expression is of type citext
+LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr);
+-- Cannot cast to money on assignment.
+INSERT INTO caster (money) VALUES ('12'::text);
+ERROR: column "money" is of type money but expression is of type text
+LINE 1: INSERT INTO caster (money) VALUES ('12'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('12'::money);
+INSERT INTO caster (money) VALUES ('12'::citext);
+ERROR: column "money" is of type money but expression is of type citext
+LINE 1: INSERT INTO caster (money) VALUES ('12'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('12'::money);
+-- Cannot cast to timestamp on assignment.
+INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text);
+ERROR: column "timestamp" is of type timestamp without time zone but expression is of type text
+LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp);
+INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext);
+ERROR: column "timestamp" is of type timestamp without time zone but expression is of type citext
+LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp);
+-- Cannot cast to timestamptz on assignment.
+INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text);
+ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type text
+LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz);
+INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext);
+ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type citext
+LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz);
+-- Cannot cast to interval on assignment.
+INSERT INTO caster (interval) VALUES ('1 hour'::text);
+ERROR: column "interval" is of type interval but expression is of type text
+LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1 hour'::interval);
+INSERT INTO caster (interval) VALUES ('1 hour'::citext);
+ERROR: column "interval" is of type interval but expression is of type citext
+LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::citext)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1 hour'::interval);
+-- Cannot cast to date on assignment.
+INSERT INTO caster (date) VALUES ('1999-01-08'::text);
+ERROR: column "date" is of type date but expression is of type text
+LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::tex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('1999-01-08'::date);
+INSERT INTO caster (date) VALUES ('1999-01-08'::citext);
+ERROR: column "date" is of type date but expression is of type citext
+LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::cit...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('1999-01-08'::date);
+-- Cannot cast to time on assignment.
+INSERT INTO caster (time) VALUES ('04:05:06'::text);
+ERROR: column "time" is of type time without time zone but expression is of type text
+LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::text)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('04:05:06'::time);
+INSERT INTO caster (time) VALUES ('04:05:06'::citext);
+ERROR: column "time" is of type time without time zone but expression is of type citext
+LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::citex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('04:05:06'::time);
+-- Cannot cast to timetz on assignment.
+INSERT INTO caster (timetz) VALUES ('04:05:06'::text);
+ERROR: column "timetz" is of type time with time zone but expression is of type text
+LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::text)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('04:05:06'::timetz);
+INSERT INTO caster (timetz) VALUES ('04:05:06'::citext);
+ERROR: column "timetz" is of type time with time zone but expression is of type citext
+LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::citex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('04:05:06'::timetz);
+-- Cannot cast to point on assignment.
+INSERT INTO caster (point) VALUES ('( 1 , 1)'::text);
+ERROR: column "point" is of type point but expression is of type text
+LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::text)...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('( 1 , 1)'::point);
+INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext);
+ERROR: column "point" is of type point but expression is of type citext
+LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::citex...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point);
+-- Cannot cast to lseg on assignment.
+INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text);
+ERROR: column "lseg" is of type lseg but expression is of type text
+LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
+INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext);
+ERROR: column "lseg" is of type lseg but expression is of type citext
+LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
+-- Cannot cast to box on assignment.
+INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text);
+ERROR: column "box" is of type box but expression is of type text
+LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::te...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box);
+INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext);
+ERROR: column "box" is of type box but expression is of type citext
+LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::ci...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box);
+-- Cannot cast to path on assignment.
+INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text);
+ERROR: column "path" is of type path but expression is of type text
+LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path);
+INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext);
+ERROR: column "path" is of type path but expression is of type citext
+LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path);
+-- Cannot cast to polygon on assignment.
+INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text);
+ERROR: column "polygon" is of type polygon but expression is of type text
+LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon);
+INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext);
+ERROR: column "polygon" is of type polygon but expression is of type citext
+LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon);
+-- Cannot cast to circle on assignment.
+INSERT INTO caster (circle) VALUES ('((0,0),2)'::text);
+ERROR: column "circle" is of type circle but expression is of type text
+LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::text...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('((0,0),2)'::circle);
+INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext);
+ERROR: column "circle" is of type circle but expression is of type citext
+LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::cite...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle);
+-- Cannot cast to bit on assignment.
+INSERT INTO caster (bit) VALUES ('101'::text);
+ERROR: column "bit" is of type bit but expression is of type text
+LINE 1: INSERT INTO caster (bit) VALUES ('101'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('101'::bit);
+INSERT INTO caster (bit) VALUES ('101'::citext);
+ERROR: column "bit" is of type bit but expression is of type citext
+LINE 1: INSERT INTO caster (bit) VALUES ('101'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('101'::bit);
+-- Cannot cast to bit varying on assignment.
+INSERT INTO caster (bitv) VALUES ('101'::text);
+ERROR: column "bitv" is of type bit varying but expression is of type text
+LINE 1: INSERT INTO caster (bitv) VALUES ('101'::text);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('101'::bit varying);
+INSERT INTO caster (bitv) VALUES ('101'::citext);
+ERROR: column "bitv" is of type bit varying but expression is of type citext
+LINE 1: INSERT INTO caster (bitv) VALUES ('101'::citext);
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('101'::bit varying);
+-- Cannot cast to tsvector on assignment.
+INSERT INTO caster (tsvector) VALUES ('the fat cat'::text);
+ERROR: column "tsvector" is of type tsvector but expression is of type text
+LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::te...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('the fat cat'::tsvector);
+INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext);
+ERROR: column "tsvector" is of type tsvector but expression is of type citext
+LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::ci...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector);
+-- Cannot cast to tsquery on assignment.
+INSERT INTO caster (tsquery) VALUES ('fat & rat'::text);
+ERROR: column "tsquery" is of type tsquery but expression is of type text
+LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::text...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('fat & rat'::tsquery);
+INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext);
+ERROR: column "tsquery" is of type tsquery but expression is of type citext
+LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::cite...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery);
+-- Cannot cast to uuid on assignment.
+INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);
+ERROR: column "uuid" is of type uuid but expression is of type text
+LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
+INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext);
+ERROR: column "uuid" is of type uuid but expression is of type citext
+LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e...
+ ^
+HINT: You will need to rewrite or cast the expression.
+INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
+-- Table 9-5. SQL String Functions and Operators
+SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat;
+ citext_concat
+---------------
+ t
+(1 row)
+
+SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat;
+ text_concat
+-------------
+ t
+(1 row)
+
+SELECT 42 || ': value'::citext ='42: value' AS int_concat;
+ int_concat
+------------
+ t
+(1 row)
+
+SELECT bit_length('jose'::citext) = 32 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT textlen( name ) = textlen( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT char_length( name ) = char_length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT lower( name ) = lower( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT substr('alphabet'::citext, 3) = 'phabet' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('alphabet'::citext, 3) = 'phabet' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext from 2) = 'homas' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext from '...$') = 'mas' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT substring('Thomas'::citext similar '%#"o_a#"_' escape '#') = 'oma' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim(' trim '::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT upper( name ) = upper( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Table 9-6. Other String Functions.
+SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT btrim(' trim'::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+-- chr() takes an int and returns text.
+-- convert() and convert_from take bytea and return text.
+SELECT convert_from( name::bytea, 'SQL_ASCII' ) = convert_from( name::text::bytea, 'SQL_ASCII' ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t;
+ t
+---
+ t
+(1 row)
+
+-- encode() takes bytea and returns text.
+SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT length( name ) = length( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT lpad('hi'::citext, 5 ) = ' hi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim(' trim'::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT md5( name ) = md5( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- pg_client_encoding() takes no args and returns name.
+SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no result";
+ no result
+-----------
+
+(1 row)
+
+-- g is not allowed
+SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error";
+ERROR: regexp_match() does not support the "global" option
+HINT: Use the regexp_matches function instead.
+CONTEXT: SQL function "regexp_match" statement 1
+SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no rows";
+ no rows
+---------
+(0 rows)
+
+-- g allows multiple output rows
+SELECT regexp_matches('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g'::citext) AS "two rows";
+ two rows
+-------------
+ {bar,beque}
+ {bar,beque}
+(2 rows)
+
+SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
+ t
+---
+ t
+(1 row)
+
+-- c forces case-sensitive
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words;
+ words
+-------
+ hello
+ world
+(2 rows)
+
+-- c forces case-sensitive
+SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word;
+ word
+-------------
+ helloTworld
+(1 row)
+
+SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trim '::citext ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'gh' ) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high', 'gh'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'gh'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'GH' ) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high', 'GH'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT strpos('high'::citext, 'GH'::citext) = 3 AS t;
+ t
+---
+ t
+(1 row)
+
+-- to_ascii() does not support UTF-8.
+-- to_hex() takes a numeric argument.
+SELECT substr('alphabet', 3, 2) = 'ph' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+ t
+---
+ t
+(1 row)
+
+-- Table 9-20. Formatting Functions
+SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY')
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext)
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext)
+ = to_number('12,454.8-', '99G999D9S') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_number('12,454.8-'::citext, '99G999D9S')
+ = to_number('12,454.8-', '99G999D9S') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_number('12,454.8-', '99G999D9S'::citext)
+ = to_number('12,454.8-', '99G999D9S') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY')
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext)
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+ t
+---
+ t
+(1 row)
+
+-- Try assigning function results to a column.
+SELECT COUNT(*) = 8::bigint AS t FROM try;
+ t
+---
+ t
+(1 row)
+
+INSERT INTO try
+VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ),
+ ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timestamptz
+ ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ),
+ ( to_char( current_date, '999') ),
+ ( to_char( 125::int, '999') ),
+ ( to_char( 127::int4, '999') ),
+ ( to_char( 126::int8, '999') ),
+ ( to_char( 128.8::real, '999D9') ),
+ ( to_char( 125.7::float4, '999D9') ),
+ ( to_char( 125.9::float8, '999D9') ),
+ ( to_char( -125.8::numeric, '999D99S') );
+SELECT COUNT(*) = 19::bigint AS t FROM try;
+ t
+---
+ t
+(1 row)
+
+SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;
+ t
+---
+ t
+ t
+ t
+ t
+(4 rows)
+
+-- Ensure correct behavior for citext with materialized views.
+CREATE TABLE citext_table (
+ id serial primary key,
+ name citext
+);
+INSERT INTO citext_table (name)
+ VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
+CREATE MATERIALIZED VIEW citext_matview AS
+ SELECT * FROM citext_table;
+CREATE UNIQUE INDEX citext_matview_id
+ ON citext_matview (id);
+SELECT *
+ FROM citext_matview m
+ FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+ WHERE t.id IS NULL OR m.id IS NULL;
+ id | name | id | name
+----+------+----+------
+(0 rows)
+
+UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
+SELECT *
+ FROM citext_matview m
+ FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+ WHERE t.id IS NULL OR m.id IS NULL;
+ id | name | id | name
+----+------+----+------
+ | | 2 | Two
+ 2 | two | |
+(2 rows)
+
+REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
+SELECT * FROM citext_matview ORDER BY id;
+ id | name
+----+-------
+ 1 | one
+ 2 | Two
+ 3 | three
+ 4 |
+ 5 |
+(5 rows)
+
+-- test citext_pattern_cmp() function explicitly.
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+-- test operator functions
+-- lt
+SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- le
+SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- gt
+SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- ge
+SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- Test ~<~ and ~<=~
+SELECT 'a'::citext ~<~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~<~ 'A'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/citext/expected/citext_utf8.out b/contrib/citext/expected/citext_utf8.out
new file mode 100644
index 0000000..666b07c
--- /dev/null
+++ b/contrib/citext/expected/citext_utf8.out
@@ -0,0 +1,146 @@
+/*
+ * This test must be run in a database with UTF-8 encoding
+ * and a Unicode-aware locale.
+ */
+SELECT getdatabaseencoding() <> 'UTF8' OR
+ current_setting('lc_ctype') = 'C'
+ AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+set client_encoding = utf8;
+-- CREATE EXTENSION IF NOT EXISTS citext;
+-- Multibyte sanity tests.
+SELECT 'À'::citext = 'À'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'À'::citext = 'à'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'À'::text = 'à'::text AS f; -- text wins.
+ f
+---
+ f
+(1 row)
+
+SELECT 'À'::citext <> 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test combining characters making up canonically equivalent strings.
+SELECT 'Ä'::text <> 'Ä'::text AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ä'::citext <> 'Ä'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test the Turkish dotted I. The lowercase is a single byte while the
+-- uppercase is multibyte. This is why the comparison code can't be optimized
+-- to compare string lengths.
+SELECT 'i'::citext = 'İ'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Regression.
+SELECT 'láska'::citext <> 'laská'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ask Bjørn Hansen'::citext = 'Ask Bjørn Hansen'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ask Bjørn Hansen'::citext = 'ASK BJØRN HANSEN'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ask Bjørn Hansen'::citext <> 'Ask Bjorn Hansen'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ask Bjørn Hansen'::citext <> 'ASK BJORN HANSEN'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjørn Hansen'::citext) = 0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) = 0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) = 0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) > 0 AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) < 0 AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test ~<~ and ~<=~
+SELECT 'à'::citext ~<~ 'À'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'à'::citext ~<=~ 'À'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test ~>~ and ~>=~
+SELECT 'à'::citext ~>~ 'À'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'à'::citext ~>=~ 'À'::citext AS t;
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/citext/expected/citext_utf8_1.out b/contrib/citext/expected/citext_utf8_1.out
new file mode 100644
index 0000000..433e985
--- /dev/null
+++ b/contrib/citext/expected/citext_utf8_1.out
@@ -0,0 +1,9 @@
+/*
+ * This test must be run in a database with UTF-8 encoding
+ * and a Unicode-aware locale.
+ */
+SELECT getdatabaseencoding() <> 'UTF8' OR
+ current_setting('lc_ctype') = 'C'
+ AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/contrib/citext/expected/create_index_acl.out b/contrib/citext/expected/create_index_acl.out
new file mode 100644
index 0000000..33be13a
--- /dev/null
+++ b/contrib/citext/expected/create_index_acl.out
@@ -0,0 +1,86 @@
+-- Each DefineIndex() ACL check uses either the original userid or the table
+-- owner userid; see its header comment. Here, confirm that DefineIndex()
+-- uses its original userid where necessary. The test works by creating
+-- indexes that refer to as many sorts of objects as possible, with the table
+-- owner having as few applicable privileges as possible. (The privileges.sql
+-- regress_sro_user tests look for the opposite defect; they confirm that
+-- DefineIndex() uses the table owner userid where necessary.)
+SET allow_in_place_tablespaces = true;
+CREATE TABLESPACE regress_create_idx_tblspace LOCATION '';
+RESET allow_in_place_tablespaces;
+BEGIN;
+CREATE ROLE regress_minimal;
+CREATE SCHEMA s;
+CREATE EXTENSION citext SCHEMA s;
+-- Revoke all conceivably-relevant ACLs within the extension. The system
+-- doesn't check all these ACLs, but this will provide some coverage if that
+-- ever changes.
+REVOKE ALL ON TYPE s.citext FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_lt FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_le FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_eq FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_ge FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_gt FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_cmp FROM PUBLIC;
+-- Functions sufficient for making an index column that has the side effect of
+-- changing search_path at expression planning time.
+CREATE FUNCTION public.setter() RETURNS bool VOLATILE
+ LANGUAGE SQL AS $$SET search_path = s; SELECT true$$;
+CREATE FUNCTION s.const() RETURNS bool IMMUTABLE
+ LANGUAGE SQL AS $$SELECT public.setter()$$;
+CREATE FUNCTION s.index_this_expr(s.citext, bool) RETURNS s.citext IMMUTABLE
+ LANGUAGE SQL AS $$SELECT $1$$;
+REVOKE ALL ON FUNCTION public.setter FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.const FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.index_this_expr FROM PUBLIC;
+-- Even for an empty table, expression planning calls s.const & public.setter.
+GRANT EXECUTE ON FUNCTION public.setter TO regress_minimal;
+GRANT EXECUTE ON FUNCTION s.const TO regress_minimal;
+-- Function for index predicate.
+CREATE FUNCTION s.index_row_if(s.citext) RETURNS bool IMMUTABLE
+ LANGUAGE SQL AS $$SELECT $1 IS NOT NULL$$;
+REVOKE ALL ON FUNCTION s.index_row_if FROM PUBLIC;
+-- Even for an empty table, CREATE INDEX checks ii_Predicate permissions.
+GRANT EXECUTE ON FUNCTION s.index_row_if TO regress_minimal;
+-- Non-extension, non-function objects.
+CREATE COLLATION s.coll (LOCALE="C");
+CREATE TABLE s.x (y s.citext);
+ALTER TABLE s.x OWNER TO regress_minimal;
+-- Empty-table DefineIndex()
+CREATE UNIQUE INDEX u0rows ON s.x USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll s.citext_pattern_ops)
+ TABLESPACE regress_create_idx_tblspace
+ WHERE s.index_row_if(y);
+ALTER TABLE s.x ADD CONSTRAINT e0rows EXCLUDE USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll WITH s.=)
+ USING INDEX TABLESPACE regress_create_idx_tblspace
+ WHERE (s.index_row_if(y));
+-- Make the table nonempty.
+INSERT INTO s.x VALUES ('foo'), ('bar');
+-- If the INSERT runs the planner on index expressions, a search_path change
+-- survives. As of 2022-06, the INSERT reuses a cached plan. It does so even
+-- under debug_discard_caches, since each index is new-in-transaction. If
+-- future work changes a cache lifecycle, this RESET may become necessary.
+RESET search_path;
+-- For a nonempty table, owner needs permissions throughout ii_Expressions.
+GRANT EXECUTE ON FUNCTION s.index_this_expr TO regress_minimal;
+CREATE UNIQUE INDEX u2rows ON s.x USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll s.citext_pattern_ops)
+ TABLESPACE regress_create_idx_tblspace
+ WHERE s.index_row_if(y);
+ALTER TABLE s.x ADD CONSTRAINT e2rows EXCLUDE USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll WITH s.=)
+ USING INDEX TABLESPACE regress_create_idx_tblspace
+ WHERE (s.index_row_if(y));
+-- Shall not find s.coll via search_path, despite the s.const->public.setter
+-- call having set search_path=s during expression planning. Suppress the
+-- message itself, which depends on the database encoding.
+\set VERBOSITY sqlstate
+ALTER TABLE s.x ADD CONSTRAINT underqualified EXCLUDE USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE coll WITH s.=)
+ USING INDEX TABLESPACE regress_create_idx_tblspace
+ WHERE (s.index_row_if(y));
+ERROR: 42704
+\set VERBOSITY default
+ROLLBACK;
+DROP TABLESPACE regress_create_idx_tblspace;
diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql
new file mode 100644
index 0000000..b329253
--- /dev/null
+++ b/contrib/citext/sql/citext.sql
@@ -0,0 +1,810 @@
+--
+-- Test citext datatype
+--
+
+CREATE EXTENSION citext;
+
+-- Check whether any of our opclasses fail amvalidate
+SELECT amname, opcname
+FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
+WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
+
+-- Test the operators and indexing functions
+
+-- Test = and <>.
+SELECT 'a'::citext = 'a'::citext AS t;
+SELECT 'a'::citext = 'A'::citext AS t;
+SELECT 'a'::citext = 'A'::text AS f; -- text wins the discussion
+SELECT 'a'::citext = 'b'::citext AS f;
+SELECT 'a'::citext = 'ab'::citext AS f;
+SELECT 'a'::citext <> 'ab'::citext AS t;
+
+-- Test > and >=
+SELECT 'B'::citext > 'a'::citext AS t;
+SELECT 'b'::citext > 'A'::citext AS t;
+SELECT 'B'::citext > 'b'::citext AS f;
+SELECT 'B'::citext >= 'b'::citext AS t;
+
+-- Test < and <=
+SELECT 'a'::citext < 'B'::citext AS t;
+SELECT 'a'::citext <= 'B'::citext AS t;
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'a'::citext = 'a'::text AS t;
+SELECT 'A'::text <> 'a'::citext AS t;
+
+SELECT 'B'::citext < 'a'::text AS t; -- text wins.
+SELECT 'B'::citext <= 'a'::text AS t; -- text wins.
+
+SELECT 'a'::citext > 'B'::text AS t; -- text wins.
+SELECT 'a'::citext >= 'B'::text AS t; -- text wins.
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'a'::citext = 'a'::varchar AS t;
+SELECT 'A'::varchar <> 'a'::citext AS t;
+
+SELECT 'B'::citext < 'a'::varchar AS t; -- varchar wins.
+SELECT 'B'::citext <= 'a'::varchar AS t; -- varchar wins.
+
+SELECT 'a'::citext > 'B'::varchar AS t; -- varchar wins.
+SELECT 'a'::citext >= 'B'::varchar AS t; -- varchar wins.
+
+-- A couple of longer examples to ensure that we don't get any issues with bad
+-- conversions to char[] in the c code. Yes, I did do this.
+
+SELECT 'aardvark'::citext = 'aardvark'::citext AS t;
+SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
+
+-- Check the citext_cmp() function explicitly.
+SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+SELECT citext_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true;
+
+-- Check the citext_hash() and citext_hash_extended() function explicitly.
+SELECT v as value, citext_hash(v)::bit(32) as standard,
+ citext_hash_extended(v, 0)::bit(32) as extended0,
+ citext_hash_extended(v, 1)::bit(32) as extended1
+FROM (VALUES (NULL::citext), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'),
+ ('muop28x03'), ('yi3nm0d73')) x(v)
+WHERE citext_hash(v)::bit(32) != citext_hash_extended(v, 0)::bit(32)
+ OR citext_hash(v)::bit(32) = citext_hash_extended(v, 1)::bit(32);
+
+-- Do some tests using a table and index.
+
+CREATE TEMP TABLE try (
+ name citext PRIMARY KEY
+);
+
+INSERT INTO try (name)
+VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');
+
+SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â';
+SELECT name, 'a' = name AS t FROM try where name = 'a';
+SELECT name, 'A' = name AS "eq_A" FROM try WHERE name <> 'â';
+SELECT name, 'A' = name AS t FROM try where name = 'A';
+SELECT name, 'A' = name AS t FROM try where name = 'A';
+
+-- expected failures on duplicate key
+INSERT INTO try (name) VALUES ('a');
+INSERT INTO try (name) VALUES ('A');
+INSERT INTO try (name) VALUES ('aB');
+
+-- Make sure that citext_smaller() and citext_larger() work properly.
+SELECT citext_smaller( 'ab'::citext, 'ac'::citext ) = 'ab' AS t;
+SELECT citext_smaller( 'ABC'::citext, 'bbbb'::citext ) = 'ABC' AS t;
+SELECT citext_smaller( 'aardvark'::citext, 'Aaba'::citext ) = 'Aaba' AS t;
+SELECT citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ) = 'AARDVARK' AS t;
+
+SELECT citext_larger( 'ab'::citext, 'ac'::citext ) = 'ac' AS t;
+SELECT citext_larger( 'ABC'::citext, 'bbbb'::citext ) = 'bbbb' AS t;
+SELECT citext_larger( 'aardvark'::citext, 'Aaba'::citext ) = 'aardvark' AS t;
+
+-- Test aggregate functions and sort ordering
+
+CREATE TEMP TABLE srt (
+ name CITEXT
+);
+
+INSERT INTO srt (name)
+VALUES ('abb'),
+ ('ABA'),
+ ('ABC'),
+ ('abd');
+
+CREATE INDEX srt_name ON srt (name);
+
+-- Check the min() and max() aggregates, with and without index.
+set enable_seqscan = off;
+SELECT MIN(name) AS "ABA" FROM srt;
+SELECT MAX(name) AS abd FROM srt;
+reset enable_seqscan;
+set enable_indexscan = off;
+SELECT MIN(name) AS "ABA" FROM srt;
+SELECT MAX(name) AS abd FROM srt;
+reset enable_indexscan;
+
+-- Check sorting likewise
+set enable_seqscan = off;
+SELECT name FROM srt ORDER BY name;
+reset enable_seqscan;
+set enable_indexscan = off;
+SELECT name FROM srt ORDER BY name;
+reset enable_indexscan;
+
+-- Test assignment casts.
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::text;
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::varchar;
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::bpchar;
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA';
+SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::citext;
+
+-- LIKE should be case-insensitive
+SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name;
+SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name;
+SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name;
+SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name;
+
+-- ~~ should be case-insensitive
+SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name;
+SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name;
+SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name;
+SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name;
+
+-- ~ should be case-insensitive
+SELECT name FROM srt WHERE name ~ '^a' ORDER BY name;
+SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name;
+SELECT name FROM srt WHERE name ~ '^A' ORDER BY name;
+SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name;
+
+-- SIMILAR TO should be case-insensitive.
+SELECT name FROM srt WHERE name SIMILAR TO '%a.*';
+SELECT name FROM srt WHERE name SIMILAR TO '%A.*';
+
+-- Explicit casts.
+SELECT true::citext = 'true' AS t;
+SELECT 'true'::citext::boolean = true AS t;
+
+SELECT 4::citext = '4' AS t;
+SELECT 4::int4::citext = '4' AS t;
+SELECT '4'::citext::int4 = 4 AS t;
+SELECT 4::integer::citext = '4' AS t;
+SELECT '4'::citext::integer = 4 AS t;
+
+SELECT 4::int8::citext = '4' AS t;
+SELECT '4'::citext::int8 = 4 AS t;
+SELECT 4::bigint::citext = '4' AS t;
+SELECT '4'::citext::bigint = 4 AS t;
+
+SELECT 4::int2::citext = '4' AS t;
+SELECT '4'::citext::int2 = 4 AS t;
+SELECT 4::smallint::citext = '4' AS t;
+SELECT '4'::citext::smallint = 4 AS t;
+
+SELECT 4.0::numeric = '4.0' AS t;
+SELECT '4.0'::citext::numeric = 4.0 AS t;
+SELECT 4.0::decimal = '4.0' AS t;
+SELECT '4.0'::citext::decimal = 4.0 AS t;
+
+SELECT 4.0::real = '4.0' AS t;
+SELECT '4.0'::citext::real = 4.0 AS t;
+SELECT 4.0::float4 = '4.0' AS t;
+SELECT '4.0'::citext::float4 = 4.0 AS t;
+
+SELECT 4.0::double precision = '4.0' AS t;
+SELECT '4.0'::citext::double precision = 4.0 AS t;
+SELECT 4.0::float8 = '4.0' AS t;
+SELECT '4.0'::citext::float8 = 4.0 AS t;
+
+SELECT 'foo'::name::citext = 'foo' AS t;
+SELECT 'foo'::citext::name = 'foo'::name AS t;
+
+SELECT 'f'::char::citext = 'f' AS t;
+SELECT 'f'::citext::char = 'f'::char AS t;
+
+SELECT 'f'::"char"::citext = 'f' AS t;
+SELECT 'f'::citext::"char" = 'f'::"char" AS t;
+
+SELECT '100'::money::citext = '$100.00' AS t;
+SELECT '100'::citext::money = '100'::money AS t;
+
+SELECT 'a'::char::citext = 'a' AS t;
+SELECT 'a'::citext::char = 'a'::char AS t;
+
+SELECT 'foo'::varchar::citext = 'foo' AS t;
+SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
+
+SELECT 'foo'::text::citext = 'foo' AS t;
+SELECT 'foo'::citext::text = 'foo'::text AS t;
+
+SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t;
+SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t;
+
+SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t;
+SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
+
+SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
+SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
+
+SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
+SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
+SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
+SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t;
+
+SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
+SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
+
+SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t;
+SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t;
+
+SELECT '04:05:06'::time::citext = '04:05:06' AS t;
+SELECT '04:05:06'::citext::time = '04:05:06'::time AS t;
+SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t;
+SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t;
+
+SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t;
+SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t;
+SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t;
+SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t;
+SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t;
+SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t;
+
+SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t;
+SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t;
+
+SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t;
+SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t;
+
+SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t;
+SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t;
+
+SELECT '101'::bit::citext = '101'::bit::text AS t;
+SELECT '101'::citext::bit = '101'::text::bit AS t;
+SELECT '101'::bit varying::citext = '101'::bit varying::text AS t;
+SELECT '101'::citext::bit varying = '101'::text::bit varying AS t;
+SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t;
+SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t;
+SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t;
+SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t;
+SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t;
+SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t;
+
+CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
+SELECT 'sad'::mood::citext = 'sad' AS t;
+SELECT 'sad'::citext::mood = 'sad'::mood AS t;
+
+-- Assignment casts.
+CREATE TABLE caster (
+ citext citext,
+ text text,
+ varchar varchar,
+ bpchar bpchar,
+ char char,
+ chr "char",
+ name name,
+ bytea bytea,
+ boolean boolean,
+ float4 float4,
+ float8 float8,
+ numeric numeric,
+ int8 int8,
+ int4 int4,
+ int2 int2,
+ cidr cidr,
+ inet inet,
+ macaddr macaddr,
+ money money,
+ timestamp timestamp,
+ timestamptz timestamptz,
+ interval interval,
+ date date,
+ time time,
+ timetz timetz,
+ point point,
+ lseg lseg,
+ box box,
+ path path,
+ polygon polygon,
+ circle circle,
+ bit bit,
+ bitv bit varying,
+ tsvector tsvector,
+ tsquery tsquery,
+ uuid uuid
+);
+
+INSERT INTO caster (text) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::text);
+
+INSERT INTO caster (varchar) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::varchar);
+INSERT INTO caster (varchar) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::varchar);
+
+INSERT INTO caster (bpchar) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::bpchar);
+INSERT INTO caster (bpchar) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::bpchar);
+
+INSERT INTO caster (char) VALUES ('f'::text);
+INSERT INTO caster (text) VALUES ('f'::char);
+INSERT INTO caster (char) VALUES ('f'::citext);
+INSERT INTO caster (citext) VALUES ('f'::char);
+
+INSERT INTO caster (chr) VALUES ('f'::text);
+INSERT INTO caster (text) VALUES ('f'::"char");
+INSERT INTO caster (chr) VALUES ('f'::citext); -- requires cast
+INSERT INTO caster (chr) VALUES ('f'::citext::text);
+INSERT INTO caster (citext) VALUES ('f'::"char");
+
+INSERT INTO caster (name) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::name);
+INSERT INTO caster (name) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::name);
+
+-- Cannot cast to bytea on assignment.
+INSERT INTO caster (bytea) VALUES ('foo'::text);
+INSERT INTO caster (text) VALUES ('foo'::bytea);
+INSERT INTO caster (bytea) VALUES ('foo'::citext);
+INSERT INTO caster (citext) VALUES ('foo'::bytea);
+
+-- Cannot cast to boolean on assignment.
+INSERT INTO caster (boolean) VALUES ('t'::text);
+INSERT INTO caster (text) VALUES ('t'::boolean);
+INSERT INTO caster (boolean) VALUES ('t'::citext);
+INSERT INTO caster (citext) VALUES ('t'::boolean);
+
+-- Cannot cast to float8 on assignment.
+INSERT INTO caster (float8) VALUES ('12.42'::text);
+INSERT INTO caster (text) VALUES ('12.42'::float8);
+INSERT INTO caster (float8) VALUES ('12.42'::citext);
+INSERT INTO caster (citext) VALUES ('12.42'::float8);
+
+-- Cannot cast to float4 on assignment.
+INSERT INTO caster (float4) VALUES ('12.42'::text);
+INSERT INTO caster (text) VALUES ('12.42'::float4);
+INSERT INTO caster (float4) VALUES ('12.42'::citext);
+INSERT INTO caster (citext) VALUES ('12.42'::float4);
+
+-- Cannot cast to numeric on assignment.
+INSERT INTO caster (numeric) VALUES ('12.42'::text);
+INSERT INTO caster (text) VALUES ('12.42'::numeric);
+INSERT INTO caster (numeric) VALUES ('12.42'::citext);
+INSERT INTO caster (citext) VALUES ('12.42'::numeric);
+
+-- Cannot cast to int8 on assignment.
+INSERT INTO caster (int8) VALUES ('12'::text);
+INSERT INTO caster (text) VALUES ('12'::int8);
+INSERT INTO caster (int8) VALUES ('12'::citext);
+INSERT INTO caster (citext) VALUES ('12'::int8);
+
+-- Cannot cast to int4 on assignment.
+INSERT INTO caster (int4) VALUES ('12'::text);
+INSERT INTO caster (text) VALUES ('12'::int4);
+INSERT INTO caster (int4) VALUES ('12'::citext);
+INSERT INTO caster (citext) VALUES ('12'::int4);
+
+-- Cannot cast to int2 on assignment.
+INSERT INTO caster (int2) VALUES ('12'::text);
+INSERT INTO caster (text) VALUES ('12'::int2);
+INSERT INTO caster (int2) VALUES ('12'::citext);
+INSERT INTO caster (citext) VALUES ('12'::int2);
+
+-- Cannot cast to cidr on assignment.
+INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text);
+INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr);
+INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext);
+INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr);
+
+-- Cannot cast to inet on assignment.
+INSERT INTO caster (inet) VALUES ('192.168.100.128'::text);
+INSERT INTO caster (text) VALUES ('192.168.100.128'::inet);
+INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext);
+INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet);
+
+-- Cannot cast to macaddr on assignment.
+INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text);
+INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr);
+INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext);
+INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr);
+
+-- Cannot cast to money on assignment.
+INSERT INTO caster (money) VALUES ('12'::text);
+INSERT INTO caster (text) VALUES ('12'::money);
+INSERT INTO caster (money) VALUES ('12'::citext);
+INSERT INTO caster (citext) VALUES ('12'::money);
+
+-- Cannot cast to timestamp on assignment.
+INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text);
+INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp);
+INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext);
+INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp);
+
+-- Cannot cast to timestamptz on assignment.
+INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text);
+INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz);
+INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext);
+INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz);
+
+-- Cannot cast to interval on assignment.
+INSERT INTO caster (interval) VALUES ('1 hour'::text);
+INSERT INTO caster (text) VALUES ('1 hour'::interval);
+INSERT INTO caster (interval) VALUES ('1 hour'::citext);
+INSERT INTO caster (citext) VALUES ('1 hour'::interval);
+
+-- Cannot cast to date on assignment.
+INSERT INTO caster (date) VALUES ('1999-01-08'::text);
+INSERT INTO caster (text) VALUES ('1999-01-08'::date);
+INSERT INTO caster (date) VALUES ('1999-01-08'::citext);
+INSERT INTO caster (citext) VALUES ('1999-01-08'::date);
+
+-- Cannot cast to time on assignment.
+INSERT INTO caster (time) VALUES ('04:05:06'::text);
+INSERT INTO caster (text) VALUES ('04:05:06'::time);
+INSERT INTO caster (time) VALUES ('04:05:06'::citext);
+INSERT INTO caster (citext) VALUES ('04:05:06'::time);
+
+-- Cannot cast to timetz on assignment.
+INSERT INTO caster (timetz) VALUES ('04:05:06'::text);
+INSERT INTO caster (text) VALUES ('04:05:06'::timetz);
+INSERT INTO caster (timetz) VALUES ('04:05:06'::citext);
+INSERT INTO caster (citext) VALUES ('04:05:06'::timetz);
+
+-- Cannot cast to point on assignment.
+INSERT INTO caster (point) VALUES ('( 1 , 1)'::text);
+INSERT INTO caster (text) VALUES ('( 1 , 1)'::point);
+INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext);
+INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point);
+
+-- Cannot cast to lseg on assignment.
+INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text);
+INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
+INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext);
+INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
+
+-- Cannot cast to box on assignment.
+INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text);
+INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box);
+INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext);
+INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box);
+
+-- Cannot cast to path on assignment.
+INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text);
+INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path);
+INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext);
+INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path);
+
+-- Cannot cast to polygon on assignment.
+INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text);
+INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon);
+INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext);
+INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon);
+
+-- Cannot cast to circle on assignment.
+INSERT INTO caster (circle) VALUES ('((0,0),2)'::text);
+INSERT INTO caster (text) VALUES ('((0,0),2)'::circle);
+INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext);
+INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle);
+
+-- Cannot cast to bit on assignment.
+INSERT INTO caster (bit) VALUES ('101'::text);
+INSERT INTO caster (text) VALUES ('101'::bit);
+INSERT INTO caster (bit) VALUES ('101'::citext);
+INSERT INTO caster (citext) VALUES ('101'::bit);
+
+-- Cannot cast to bit varying on assignment.
+INSERT INTO caster (bitv) VALUES ('101'::text);
+INSERT INTO caster (text) VALUES ('101'::bit varying);
+INSERT INTO caster (bitv) VALUES ('101'::citext);
+INSERT INTO caster (citext) VALUES ('101'::bit varying);
+
+-- Cannot cast to tsvector on assignment.
+INSERT INTO caster (tsvector) VALUES ('the fat cat'::text);
+INSERT INTO caster (text) VALUES ('the fat cat'::tsvector);
+INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext);
+INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector);
+
+-- Cannot cast to tsquery on assignment.
+INSERT INTO caster (tsquery) VALUES ('fat & rat'::text);
+INSERT INTO caster (text) VALUES ('fat & rat'::tsquery);
+INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext);
+INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery);
+
+-- Cannot cast to uuid on assignment.
+INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);
+INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
+INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext);
+INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
+
+-- Table 9-5. SQL String Functions and Operators
+SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat;
+SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat;
+SELECT 42 || ': value'::citext ='42: value' AS int_concat;
+SELECT bit_length('jose'::citext) = 32 AS t;
+SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt;
+SELECT textlen( name ) = textlen( name::text ) AS t FROM srt;
+SELECT char_length( name ) = char_length( name::text ) AS t FROM srt;
+SELECT lower( name ) = lower( name::text ) AS t FROM srt;
+SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt;
+SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt;
+SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt;
+
+SELECT substr('alphabet'::citext, 3) = 'phabet' AS t;
+SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t;
+
+SELECT substring('alphabet'::citext, 3) = 'phabet' AS t;
+SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t;
+SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t;
+SELECT substring('Thomas'::citext from 2) = 'homas' AS t;
+SELECT substring('Thomas'::citext from '...$') = 'mas' AS t;
+SELECT substring('Thomas'::citext similar '%#"o_a#"_' escape '#') = 'oma' AS t;
+
+SELECT trim(' trim '::citext) = 'trim' AS t;
+SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t;
+SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
+SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
+
+SELECT upper( name ) = upper( name::text ) AS t FROM srt;
+
+-- Table 9-6. Other String Functions.
+SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
+
+SELECT btrim(' trim'::citext ) = 'trim' AS t;
+SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t;
+SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t;
+SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t;
+SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t;
+
+-- chr() takes an int and returns text.
+-- convert() and convert_from take bytea and return text.
+
+SELECT convert_from( name::bytea, 'SQL_ASCII' ) = convert_from( name::text::bytea, 'SQL_ASCII' ) AS t FROM srt;
+SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t;
+-- encode() takes bytea and returns text.
+SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t;
+SELECT length( name ) = length( name::text ) AS t FROM srt;
+
+SELECT lpad('hi'::citext, 5 ) = ' hi' AS t;
+SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t;
+SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t;
+SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t;
+
+SELECT ltrim(' trim'::citext ) = 'trim' AS t;
+SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t;
+SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t;
+SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t;
+
+SELECT md5( name ) = md5( name::text ) AS t FROM srt;
+-- pg_client_encoding() takes no args and returns name.
+SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
+SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
+
+SELECT regexp_match('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_match('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+-- c forces case-sensitive
+SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no result";
+-- g is not allowed
+SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error";
+
+SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
+-- c forces case-sensitive
+SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no rows";
+-- g allows multiple output rows
+SELECT regexp_matches('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g'::citext) AS "two rows";
+
+SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t;
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t;
+SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t;
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t;
+-- c forces case-sensitive
+SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t;
+
+SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t;
+SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t;
+SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
+SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t;
+SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
+
+-- c forces case-sensitive
+SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
+
+SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words;
+SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words;
+SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words;
+SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words;
+-- c forces case-sensitive
+SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word;
+
+SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
+
+SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
+SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
+SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t;
+SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
+SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
+SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
+SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
+
+SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t;
+SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t;
+SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t;
+SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t;
+
+SELECT rtrim('trim '::citext ) = 'trim' AS t;
+SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t;
+SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t;
+SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t;
+
+SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
+SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
+SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
+SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
+
+SELECT strpos('high'::citext, 'gh' ) = 3 AS t;
+SELECT strpos('high', 'gh'::citext) = 3 AS t;
+SELECT strpos('high'::citext, 'gh'::citext) = 3 AS t;
+SELECT strpos('high'::citext, 'GH' ) = 3 AS t;
+SELECT strpos('high', 'GH'::citext) = 3 AS t;
+SELECT strpos('high'::citext, 'GH'::citext) = 3 AS t;
+
+-- to_ascii() does not support UTF-8.
+-- to_hex() takes a numeric argument.
+SELECT substr('alphabet', 3, 2) = 'ph' AS t;
+SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
+SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
+SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
+
+-- Table 9-20. Formatting Functions
+SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY')
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext)
+ = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
+
+SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext)
+ = to_number('12,454.8-', '99G999D9S') AS t;
+SELECT to_number('12,454.8-'::citext, '99G999D9S')
+ = to_number('12,454.8-', '99G999D9S') AS t;
+SELECT to_number('12,454.8-', '99G999D9S'::citext)
+ = to_number('12,454.8-', '99G999D9S') AS t;
+
+SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY')
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext)
+ = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
+
+-- Try assigning function results to a column.
+SELECT COUNT(*) = 8::bigint AS t FROM try;
+INSERT INTO try
+VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ),
+ ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timestamptz
+ ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ),
+ ( to_char( current_date, '999') ),
+ ( to_char( 125::int, '999') ),
+ ( to_char( 127::int4, '999') ),
+ ( to_char( 126::int8, '999') ),
+ ( to_char( 128.8::real, '999D9') ),
+ ( to_char( 125.7::float4, '999D9') ),
+ ( to_char( 125.9::float8, '999D9') ),
+ ( to_char( -125.8::numeric, '999D99S') );
+
+SELECT COUNT(*) = 19::bigint AS t FROM try;
+
+SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
+SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;
+
+-- Ensure correct behavior for citext with materialized views.
+CREATE TABLE citext_table (
+ id serial primary key,
+ name citext
+);
+INSERT INTO citext_table (name)
+ VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
+CREATE MATERIALIZED VIEW citext_matview AS
+ SELECT * FROM citext_table;
+CREATE UNIQUE INDEX citext_matview_id
+ ON citext_matview (id);
+SELECT *
+ FROM citext_matview m
+ FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+ WHERE t.id IS NULL OR m.id IS NULL;
+UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
+SELECT *
+ FROM citext_matview m
+ FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+ WHERE t.id IS NULL OR m.id IS NULL;
+REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
+SELECT * FROM citext_matview ORDER BY id;
+
+-- test citext_pattern_cmp() function explicitly.
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true;
+SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true;
+SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true;
+SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true;
+SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::citext) < 0 AS true;
+
+-- test operator functions
+-- lt
+SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true;
+SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true;
+SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true;
+SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false;
+-- le
+SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true;
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false;
+-- gt
+SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false;
+SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false;
+SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false;
+SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true;
+-- ge
+SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false;
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true;
+
+-- Test ~<~ and ~<=~
+SELECT 'a'::citext ~<~ 'B'::citext AS t;
+SELECT 'b'::citext ~<~ 'A'::citext AS f;
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins.
+SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins.
+
+SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins.
+SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins.
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins.
+SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins.
+
+SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins.
+SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins.
diff --git a/contrib/citext/sql/citext_utf8.sql b/contrib/citext/sql/citext_utf8.sql
new file mode 100644
index 0000000..d068000
--- /dev/null
+++ b/contrib/citext/sql/citext_utf8.sql
@@ -0,0 +1,51 @@
+/*
+ * This test must be run in a database with UTF-8 encoding
+ * and a Unicode-aware locale.
+ */
+
+SELECT getdatabaseencoding() <> 'UTF8' OR
+ current_setting('lc_ctype') = 'C'
+ AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+set client_encoding = utf8;
+
+-- CREATE EXTENSION IF NOT EXISTS citext;
+
+-- Multibyte sanity tests.
+SELECT 'À'::citext = 'À'::citext AS t;
+SELECT 'À'::citext = 'à'::citext AS t;
+SELECT 'À'::text = 'à'::text AS f; -- text wins.
+SELECT 'À'::citext <> 'B'::citext AS t;
+
+-- Test combining characters making up canonically equivalent strings.
+SELECT 'Ä'::text <> 'Ä'::text AS t;
+SELECT 'Ä'::citext <> 'Ä'::citext AS t;
+
+-- Test the Turkish dotted I. The lowercase is a single byte while the
+-- uppercase is multibyte. This is why the comparison code can't be optimized
+-- to compare string lengths.
+SELECT 'i'::citext = 'İ'::citext AS t;
+
+-- Regression.
+SELECT 'láska'::citext <> 'laská'::citext AS t;
+
+SELECT 'Ask Bjørn Hansen'::citext = 'Ask Bjørn Hansen'::citext AS t;
+SELECT 'Ask Bjørn Hansen'::citext = 'ASK BJØRN HANSEN'::citext AS t;
+SELECT 'Ask Bjørn Hansen'::citext <> 'Ask Bjorn Hansen'::citext AS t;
+SELECT 'Ask Bjørn Hansen'::citext <> 'ASK BJORN HANSEN'::citext AS t;
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjørn Hansen'::citext) = 0 AS t;
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) = 0 AS t;
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) = 0 AS t;
+SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) > 0 AS t;
+SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) < 0 AS t;
+
+-- Test ~<~ and ~<=~
+SELECT 'à'::citext ~<~ 'À'::citext AS f;
+SELECT 'à'::citext ~<=~ 'À'::citext AS t;
+
+-- Test ~>~ and ~>=~
+SELECT 'à'::citext ~>~ 'À'::citext AS f;
+SELECT 'à'::citext ~>=~ 'À'::citext AS t;
diff --git a/contrib/citext/sql/create_index_acl.sql b/contrib/citext/sql/create_index_acl.sql
new file mode 100644
index 0000000..10b5225
--- /dev/null
+++ b/contrib/citext/sql/create_index_acl.sql
@@ -0,0 +1,88 @@
+-- Each DefineIndex() ACL check uses either the original userid or the table
+-- owner userid; see its header comment. Here, confirm that DefineIndex()
+-- uses its original userid where necessary. The test works by creating
+-- indexes that refer to as many sorts of objects as possible, with the table
+-- owner having as few applicable privileges as possible. (The privileges.sql
+-- regress_sro_user tests look for the opposite defect; they confirm that
+-- DefineIndex() uses the table owner userid where necessary.)
+
+SET allow_in_place_tablespaces = true;
+CREATE TABLESPACE regress_create_idx_tblspace LOCATION '';
+RESET allow_in_place_tablespaces;
+
+BEGIN;
+CREATE ROLE regress_minimal;
+CREATE SCHEMA s;
+CREATE EXTENSION citext SCHEMA s;
+-- Revoke all conceivably-relevant ACLs within the extension. The system
+-- doesn't check all these ACLs, but this will provide some coverage if that
+-- ever changes.
+REVOKE ALL ON TYPE s.citext FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_lt FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_le FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_eq FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_ge FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_gt FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.citext_pattern_cmp FROM PUBLIC;
+-- Functions sufficient for making an index column that has the side effect of
+-- changing search_path at expression planning time.
+CREATE FUNCTION public.setter() RETURNS bool VOLATILE
+ LANGUAGE SQL AS $$SET search_path = s; SELECT true$$;
+CREATE FUNCTION s.const() RETURNS bool IMMUTABLE
+ LANGUAGE SQL AS $$SELECT public.setter()$$;
+CREATE FUNCTION s.index_this_expr(s.citext, bool) RETURNS s.citext IMMUTABLE
+ LANGUAGE SQL AS $$SELECT $1$$;
+REVOKE ALL ON FUNCTION public.setter FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.const FROM PUBLIC;
+REVOKE ALL ON FUNCTION s.index_this_expr FROM PUBLIC;
+-- Even for an empty table, expression planning calls s.const & public.setter.
+GRANT EXECUTE ON FUNCTION public.setter TO regress_minimal;
+GRANT EXECUTE ON FUNCTION s.const TO regress_minimal;
+-- Function for index predicate.
+CREATE FUNCTION s.index_row_if(s.citext) RETURNS bool IMMUTABLE
+ LANGUAGE SQL AS $$SELECT $1 IS NOT NULL$$;
+REVOKE ALL ON FUNCTION s.index_row_if FROM PUBLIC;
+-- Even for an empty table, CREATE INDEX checks ii_Predicate permissions.
+GRANT EXECUTE ON FUNCTION s.index_row_if TO regress_minimal;
+-- Non-extension, non-function objects.
+CREATE COLLATION s.coll (LOCALE="C");
+CREATE TABLE s.x (y s.citext);
+ALTER TABLE s.x OWNER TO regress_minimal;
+-- Empty-table DefineIndex()
+CREATE UNIQUE INDEX u0rows ON s.x USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll s.citext_pattern_ops)
+ TABLESPACE regress_create_idx_tblspace
+ WHERE s.index_row_if(y);
+ALTER TABLE s.x ADD CONSTRAINT e0rows EXCLUDE USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll WITH s.=)
+ USING INDEX TABLESPACE regress_create_idx_tblspace
+ WHERE (s.index_row_if(y));
+-- Make the table nonempty.
+INSERT INTO s.x VALUES ('foo'), ('bar');
+-- If the INSERT runs the planner on index expressions, a search_path change
+-- survives. As of 2022-06, the INSERT reuses a cached plan. It does so even
+-- under debug_discard_caches, since each index is new-in-transaction. If
+-- future work changes a cache lifecycle, this RESET may become necessary.
+RESET search_path;
+-- For a nonempty table, owner needs permissions throughout ii_Expressions.
+GRANT EXECUTE ON FUNCTION s.index_this_expr TO regress_minimal;
+CREATE UNIQUE INDEX u2rows ON s.x USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll s.citext_pattern_ops)
+ TABLESPACE regress_create_idx_tblspace
+ WHERE s.index_row_if(y);
+ALTER TABLE s.x ADD CONSTRAINT e2rows EXCLUDE USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE s.coll WITH s.=)
+ USING INDEX TABLESPACE regress_create_idx_tblspace
+ WHERE (s.index_row_if(y));
+-- Shall not find s.coll via search_path, despite the s.const->public.setter
+-- call having set search_path=s during expression planning. Suppress the
+-- message itself, which depends on the database encoding.
+\set VERBOSITY sqlstate
+ALTER TABLE s.x ADD CONSTRAINT underqualified EXCLUDE USING btree
+ ((s.index_this_expr(y, s.const())) COLLATE coll WITH s.=)
+ USING INDEX TABLESPACE regress_create_idx_tblspace
+ WHERE (s.index_row_if(y));
+\set VERBOSITY default
+ROLLBACK;
+
+DROP TABLESPACE regress_create_idx_tblspace;