diff options
Diffstat (limited to 'contrib/citext')
| -rw-r--r-- | contrib/citext/.gitignore | 4 | ||||
| -rw-r--r-- | contrib/citext/Makefile | 25 | ||||
| -rw-r--r-- | contrib/citext/citext--1.0--1.1.sql | 21 | ||||
| -rw-r--r-- | contrib/citext/citext--1.1--1.2.sql | 68 | ||||
| -rw-r--r-- | contrib/citext/citext--1.2--1.3.sql | 21 | ||||
| -rw-r--r-- | contrib/citext/citext--1.3--1.4.sql | 12 | ||||
| -rw-r--r-- | contrib/citext/citext--1.4--1.5.sql | 88 | ||||
| -rw-r--r-- | contrib/citext/citext--1.4.sql | 501 | ||||
| -rw-r--r-- | contrib/citext/citext--1.5--1.6.sql | 12 | ||||
| -rw-r--r-- | contrib/citext/citext.c | 408 | ||||
| -rw-r--r-- | contrib/citext/citext.control | 6 | ||||
| -rw-r--r-- | contrib/citext/expected/citext.out | 2717 | ||||
| -rw-r--r-- | contrib/citext/expected/citext_1.out | 2717 | ||||
| -rw-r--r-- | contrib/citext/expected/create_index_acl.out | 78 | ||||
| -rw-r--r-- | contrib/citext/sql/citext.sql | 844 | ||||
| -rw-r--r-- | contrib/citext/sql/create_index_acl.sql | 79 |
16 files changed, 7601 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..cae93d9 --- /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 + +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..ec99aae --- /dev/null +++ b/contrib/citext/expected/citext.out @@ -0,0 +1,2717 @@ +-- +-- 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) + +-- Multibyte sanity tests. Uncomment to run. +-- 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) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) AS positive; +-- SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS negative; +-- 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); +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') ), -- timetamptz + ( 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) + +-- Multi-byte tests below are disabled like the sanity tests above. +-- Uncomment to run them. +-- 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 'à'::citext ~<~ 'À'::citext AS f; +SELECT 'a'::citext ~<=~ 'B'::citext AS t; + t +--- + t +(1 row) + +SELECT 'a'::citext ~<=~ 'A'::citext AS t; + t +--- + t +(1 row) + +-- SELECT 'à'::citext ~<=~ 'À'::citext AS t; +-- 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 'à'::citext ~>~ 'À'::citext AS f; +SELECT 'B'::citext ~>~ 'b'::citext AS f; + f +--- + f +(1 row) + +SELECT 'B'::citext ~>=~ 'b'::citext AS t; + t +--- + t +(1 row) + +-- SELECT 'à'::citext ~>=~ 'À'::citext AS t; +-- 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..75fd08b --- /dev/null +++ b/contrib/citext/expected/citext_1.out @@ -0,0 +1,2717 @@ +-- +-- 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) + +-- Multibyte sanity tests. Uncomment to run. +-- 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) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) AS positive; +-- SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS negative; +-- 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); +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') ), -- timetamptz + ( 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) + +-- Multi-byte tests below are disabled like the sanity tests above. +-- Uncomment to run them. +-- 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 'à'::citext ~<~ 'À'::citext AS f; +SELECT 'a'::citext ~<=~ 'B'::citext AS t; + t +--- + t +(1 row) + +SELECT 'a'::citext ~<=~ 'A'::citext AS t; + t +--- + t +(1 row) + +-- SELECT 'à'::citext ~<=~ 'À'::citext AS t; +-- 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 'à'::citext ~>~ 'À'::citext AS f; +SELECT 'B'::citext ~>~ 'b'::citext AS f; + f +--- + f +(1 row) + +SELECT 'B'::citext ~>=~ 'b'::citext AS t; + t +--- + t +(1 row) + +-- SELECT 'à'::citext ~>=~ 'À'::citext AS t; +-- 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/create_index_acl.out b/contrib/citext/expected/create_index_acl.out new file mode 100644 index 0000000..f35f60b --- /dev/null +++ b/contrib/citext/expected/create_index_acl.out @@ -0,0 +1,78 @@ +-- 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.) +-- Don't override tablespaces; this version lacks 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) + 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.=) + 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) + 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.=) + 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.=) + WHERE (s.index_row_if(y)); +ERROR: 42704 +\set VERBOSITY default +ROLLBACK; diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql new file mode 100644 index 0000000..10232f5 --- /dev/null +++ b/contrib/citext/sql/citext.sql @@ -0,0 +1,844 @@ +-- +-- 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; + +-- Multibyte sanity tests. Uncomment to run. +-- 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) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) AS zero; +-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) AS positive; +-- SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS negative; + +-- 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); +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') ), -- timetamptz + ( 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; + +-- Multi-byte tests below are disabled like the sanity tests above. +-- Uncomment to run them. + +-- Test ~<~ and ~<=~ +SELECT 'a'::citext ~<~ 'B'::citext AS t; +SELECT 'b'::citext ~<~ 'A'::citext AS f; +-- SELECT 'à'::citext ~<~ 'À'::citext AS f; +SELECT 'a'::citext ~<=~ 'B'::citext AS t; +SELECT 'a'::citext ~<=~ 'A'::citext AS t; +-- SELECT 'à'::citext ~<=~ 'À'::citext AS t; + +-- Test ~>~ and ~>=~ +SELECT 'B'::citext ~>~ 'a'::citext AS t; +SELECT 'b'::citext ~>~ 'A'::citext AS t; +-- SELECT 'à'::citext ~>~ 'À'::citext AS f; +SELECT 'B'::citext ~>~ 'b'::citext AS f; +SELECT 'B'::citext ~>=~ 'b'::citext AS t; +-- SELECT 'à'::citext ~>=~ 'À'::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/create_index_acl.sql b/contrib/citext/sql/create_index_acl.sql new file mode 100644 index 0000000..a5f4e6b --- /dev/null +++ b/contrib/citext/sql/create_index_acl.sql @@ -0,0 +1,79 @@ +-- 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.) + +-- Don't override tablespaces; this version lacks 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) + 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.=) + 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) + 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.=) + 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.=) + WHERE (s.index_row_if(y)); +\set VERBOSITY default +ROLLBACK; |