From 0579a2cadd170edd1eaaa7539e9a4ecc8aa0c597 Mon Sep 17 00:00:00 2001 From: tgl Date: Fri, 5 Sep 2008 18:25:17 +0000 Subject: [PATCH] Add some additional casts and regression tests for the citext data type. David Wheeler --- contrib/citext/citext.sql.in | 98 +- contrib/citext/expected/citext.out | 1238 +++++++++++++++++- contrib/citext/expected/citext_1.out | 2294 ++++++++++++++++++++++++++++++++++ contrib/citext/sql/citext.sql | 422 ++++++- contrib/citext/uninstall_citext.sql | 16 + 5 files changed, 3979 insertions(+), 89 deletions(-) create mode 100644 contrib/citext/expected/citext_1.out diff --git a/contrib/citext/citext.sql.in b/contrib/citext/citext.sql.in index 7fe13ea8bb..e848d8f3af 100644 --- a/contrib/citext/citext.sql.in +++ b/contrib/citext/citext.sql.in @@ -22,22 +22,22 @@ CREATE TYPE citext; CREATE OR REPLACE FUNCTION citextin(cstring) RETURNS citext AS 'textin' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION citextout(citext) RETURNS cstring AS 'textout' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION citextrecv(internal) RETURNS citext AS 'textrecv' -LANGUAGE 'internal' STABLE STRICT; +LANGUAGE internal STABLE STRICT; CREATE OR REPLACE FUNCTION citextsend(citext) RETURNS bytea AS 'textsend' -LANGUAGE 'internal' STABLE STRICT; +LANGUAGE internal STABLE STRICT; -- -- The type itself. @@ -56,13 +56,24 @@ CREATE TYPE citext ( ); -- --- A single cast function, since bpchar needs to have its whitespace trimmed --- before it's cast to citext. +-- Type casting functions for those situations where the I/O casts don't +-- automatically kick in. -- + CREATE OR REPLACE FUNCTION citext(bpchar) RETURNS citext AS 'rtrim1' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; + +CREATE OR REPLACE FUNCTION citext(boolean) +RETURNS citext +AS 'booltext' +LANGUAGE internal IMMUTABLE STRICT; + +CREATE OR REPLACE FUNCTION citext(inet) +RETURNS citext +AS 'network_show' +LANGUAGE internal IMMUTABLE STRICT; -- -- Implicit and assignment type casts. @@ -73,7 +84,9 @@ 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 (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. @@ -243,19 +256,19 @@ CREATE AGGREGATE max(citext) ( CREATE OR REPLACE FUNCTION texticlike(citext, citext) RETURNS bool AS 'texticlike' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION texticnlike(citext, citext) RETURNS bool AS 'texticnlike' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION texticregexeq(citext, citext) RETURNS bool AS 'texticregexeq' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION texticregexne(citext, citext) RETURNS bool AS 'texticregexne' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OPERATOR ~ ( PROCEDURE = texticregexeq, @@ -335,19 +348,19 @@ CREATE OPERATOR !~~* ( CREATE OR REPLACE FUNCTION texticlike(citext, text) RETURNS bool AS 'texticlike' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION texticnlike(citext, text) RETURNS bool AS 'texticnlike' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION texticregexeq(citext, text) RETURNS bool AS 'texticregexeq' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION texticregexne(citext, text) RETURNS bool AS 'texticregexne' -LANGUAGE 'internal' IMMUTABLE STRICT; +LANGUAGE internal IMMUTABLE STRICT; CREATE OPERATOR ~ ( PROCEDURE = texticregexeq, @@ -420,3 +433,56 @@ CREATE OPERATOR !~~* ( RESTRICT = icnlikesel, JOIN = icnlikejoinsel ); + +-- +-- Matching citext in string comparison functions. +-- XXX TODO Ideally these would be implemented in C. +-- + +CREATE OR REPLACE FUNCTION regexp_matches( citext, citext ) RETURNS TEXT[] AS $$ + SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' ); +$$ LANGUAGE SQL IMMUTABLE STRICT; + +CREATE OR REPLACE FUNCTION regexp_matches( citext, citext, text ) RETURNS 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; + +CREATE OR REPLACE 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; diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out index 9a60b93490..52f9046b89 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -413,7 +413,7 @@ SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::citext; aaa (1 row) --- LIKE shoudl be case-insensitive +-- LIKE should be case-insensitive SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name; name ---------- @@ -535,6 +535,948 @@ SELECT name FROM srt WHERE name SIMILAR TO '%A.*'; aba (2 rows) +-- 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 'foo'::bytea::citext = 'foo' AS t; + t +--- + t +(1 row) + +SELECT 'foo'::citext::bytea = 'foo'::bytea 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 '

foo

'::xml::citext = '

foo

' AS t; + t +--- + t +(1 row) + +SELECT '

foo

'::citext::xml::text = '

foo

'::xml::text 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, + name name, + bytea bytea, + boolean boolean, + float4 float4, + float8 float8, + numeric numeric, + int8 int8, + int4 int4, + int2 int2, + cidr cidr, + inet inet, + macaddr macaddr, + xml xml, + 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 (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 xml on assignment. +INSERT INTO caster (xml) VALUES ('

foo

'::text); +ERROR: column "xml" is of type xml but expression is of type text +LINE 1: INSERT INTO caster (xml) VALUES ('

foo

'::tex... + ^ +HINT: You will need to rewrite or cast the expression. +INSERT INTO caster (text) VALUES ('

foo

'::xml); +INSERT INTO caster (xml) VALUES ('

foo

'::citext); +ERROR: column "xml" is of type xml but expression is of type citext +LINE 1: INSERT INTO caster (xml) VALUES ('

foo

'::cit... + ^ +HINT: You will need to rewrite or cast the expression. +INSERT INTO caster (citext) VALUES ('

foo

'::xml); +-- 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 @@ -873,7 +1815,75 @@ SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', t (1 row) -SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t; +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 "null"; + null +------ + +(1 row) + +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 @@ -885,6 +1895,49 @@ SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', ' 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 ------- @@ -892,6 +1945,34 @@ SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words; 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 --- @@ -904,6 +1985,54 @@ SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS 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 --- @@ -958,78 +2087,90 @@ SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t; t (1 row) -SELECT strpos('high'::citext, 'ig' ) = 2 AS t; +SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t; t --- t (1 row) -SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t; +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' 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; +SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t; t --- t (1 row) -SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t; +SELECT strpos('high'::citext, 'ig' ) = 2 AS t; t --- t (1 row) --- TODO These functions should work case-insensitively, but don't. -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS "t TODO"; - t TODO --------- -(0 rows) +SELECT strpos('high', 'ig'::citext) = 2 AS t; + t +--- + t +(1 row) -SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'THM' AS "t TODO"; - t TODO --------- - f +SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t; + t +--- + t (1 row) -SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS "t TODO"; - t TODO --------- - f +SELECT strpos('high'::citext, 'IG' ) = 2 AS t; + t +--- + t (1 row) -SELECT regexp_split_to_table('helloTworld'::citext, 't') AS "words TODO"; - words TODO -------------- - helloTworld +SELECT strpos('high', 'IG'::citext) = 2 AS t; + t +--- + t (1 row) -SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS "t TODO"; - t TODO --------- - f +SELECT strpos('high'::citext, 'IG'::citext) = 2 AS t; + t +--- + t (1 row) -SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS "t TODO"; - t TODO --------- - f +-- 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 strpos('high'::citext, 'IG'::citext) = 2 AS "t TODO"; - t TODO --------- - f +SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t; + t +--- + t (1 row) -SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS "t TODO"; - t TODO --------- - f +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 @@ -1131,7 +2272,7 @@ SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt; t (5 rows) -SELECT like_escape( name::text, ''::citext ) =like_escape( name::text, '' ) AS t FROM srt; +SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt; t --- t @@ -1141,16 +2282,3 @@ SELECT like_escape( name::text, ''::citext ) =like_escape( name::text, '' ) AS t t (5 rows) ---- citext should work as source or destination of I/O conversion casts -SELECT cidr( '192.168.1.2'::citext ) = cidr( '192.168.1.2'::text ) AS "t"; - t ---- - t -(1 row) - -SELECT '192.168.1.2'::cidr::citext = '192.168.1.2'::cidr::text AS "t"; - 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 0000000000..b95435b3b3 --- /dev/null +++ b/contrib/citext/expected/citext_1.out @@ -0,0 +1,2294 @@ +-- +-- Test citext datatype +-- +-- +-- first, define the datatype. Turn off echoing so that expected file +-- does not depend on contents of citext.sql. +-- +SET client_min_messages = warning; +\set ECHO none +-- 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 examlpes 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) AS one; + one +----- + 1 +(1 row) + +-- Do some tests using a table and index. +CREATE TEMP TABLE try ( + name citext PRIMARY KEY +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "try_pkey" for table "try" +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" +INSERT INTO try (name) VALUES ('A'); +ERROR: duplicate key value violates unique constraint "try_pkey" +INSERT INTO try (name) VALUES ('aB'); +ERROR: duplicate key value violates unique constraint "try_pkey" +-- Make sure that citext_smaller() and citext_lager() work properly. +SELECT citext_smaller( 'aa'::citext, 'ab'::citext ) = 'aa' AS t; + t +--- + t +(1 row) + +SELECT citext_smaller( 'AAAA'::citext, 'bbbb'::citext ) = 'AAAA' 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( 'aa'::citext, 'ab'::citext ) = 'ab' AS t; + t +--- + t +(1 row) + +SELECT citext_larger( 'AAAA'::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 ('aardvark'), + ('AAA'), + ('aba'), + ('ABC'), + ('abd'); +-- Check the min() and max() aggregates, with and without index. +set enable_seqscan = off; +SELECT MIN(name) AS "AAA" FROM srt; + AAA +----- + AAA +(1 row) + +SELECT MAX(name) AS abd FROM srt; + abd +----- + abd +(1 row) + +reset enable_seqscan; +set enable_indexscan = off; +SELECT MIN(name) AS "AAA" FROM srt; + AAA +----- + AAA +(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 +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +reset enable_seqscan; +set enable_indexscan = off; +SELECT name FROM srt ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +reset enable_indexscan; +-- Test assignment casts. +SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::text; + aaa +----- + aaa +(1 row) + +SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::varchar; + aaa +----- + aaa +(1 row) + +SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::bpchar; + aaa +----- + aaa +(1 row) + +SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'; + aaa +----- + aaa +(1 row) + +SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::citext; + aaa +----- + aaa +(1 row) + +-- LIKE should be case-insensitive +SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name; + name +---------- + AAA + aardvark +(2 rows) + +SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name; + name +---------- + AAA + aardvark +(2 rows) + +-- ~~ should be case-insensitive +SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name; + name +---------- + AAA + aardvark +(2 rows) + +SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name; + name +---------- + AAA + aardvark +(2 rows) + +-- ~ should be case-insensitive +SELECT name FROM srt WHERE name ~ '^a' ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name; + name +---------- + aardvark + ABC + abd +(3 rows) + +SELECT name FROM srt WHERE name ~ '^A' ORDER BY name; + name +---------- + AAA + aardvark + aba + ABC + abd +(5 rows) + +SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name; + name +---------- + aardvark + ABC + abd +(3 rows) + +-- SIMILAR TO should be case-insensitive. +SELECT name FROM srt WHERE name SIMILAR TO '%a.*'; + name +------ + AAA + aba +(2 rows) + +SELECT name FROM srt WHERE name SIMILAR TO '%A.*'; + name +------ + AAA + aba +(2 rows) + +-- 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 'foo'::bytea::citext = 'foo' AS t; + t +--- + t +(1 row) + +SELECT 'foo'::citext::bytea = 'foo'::bytea 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 '

foo

'::xml::citext = '

foo

' AS t; +ERROR: unsupported XML feature +LINE 1: SELECT '

foo

'::xml::citext = '

foo

' AS t; + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +SELECT '

foo

'::citext::xml::text = '

foo

'::xml::text AS t; +ERROR: unsupported XML feature +LINE 1: SELECT '

foo

'::citext::xml::text = '

foo

'::xml::... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +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, + name name, + bytea bytea, + boolean boolean, + float4 float4, + float8 float8, + numeric numeric, + int8 int8, + int4 int4, + int2 int2, + cidr cidr, + inet inet, + macaddr macaddr, + xml xml, + 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 (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 xml on assignment. +INSERT INTO caster (xml) VALUES ('

foo

'::text); +ERROR: column "xml" is of type xml but expression is of type text +LINE 1: INSERT INTO caster (xml) VALUES ('

foo

'::tex... + ^ +HINT: You will need to rewrite or cast the expression. +INSERT INTO caster (text) VALUES ('

foo

'::xml); +ERROR: unsupported XML feature +LINE 1: INSERT INTO caster (text) VALUES ('

foo

'::xml... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +INSERT INTO caster (xml) VALUES ('

foo

'::citext); +ERROR: column "xml" is of type xml but expression is of type citext +LINE 1: INSERT INTO caster (xml) VALUES ('

foo

'::cit... + ^ +HINT: You will need to rewrite or cast the expression. +INSERT INTO caster (citext) VALUES ('

foo

'::xml); +ERROR: unsupported XML feature +LINE 1: INSERT INTO caster (citext) VALUES ('

foo

'::xml... + ^ +DETAIL: This functionality requires the server to be built with libxml support. +HINT: You need to rebuild PostgreSQL using --with-libxml. +-- 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 + t +(5 rows) + +SELECT textlen( name ) = textlen( name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 rows) + +SELECT char_length( name ) = char_length( name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 rows) + +SELECT lower( name ) = lower( name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 rows) + +SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 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 + t +(5 rows) + +SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 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 from '%#"o_a#"_' for '#') = '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 + t +(5 rows) + +-- Table 9-6. Other String Functions. +SELECT ascii( name ) = ascii( name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 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_to( name, 'ISO-8859-1' ) = convert_to( name::text, 'ISO-8859-1' ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 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 + t +(5 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 + t +(5 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 + t +(5 rows) + +SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 rows) + +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 "null"; + null +------ + +(1 row) + +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, 'ig' ) = 2 AS t; + t +--- + t +(1 row) + +SELECT strpos('high', 'ig'::citext) = 2 AS t; + t +--- + t +(1 row) + +SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t; + t +--- + t +(1 row) + +SELECT strpos('high'::citext, 'IG' ) = 2 AS t; + t +--- + t +(1 row) + +SELECT strpos('high', 'IG'::citext) = 2 AS t; + t +--- + t +(1 row) + +SELECT strpos('high'::citext, 'IG'::citext) = 2 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 + t +(5 rows) + +SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt; + t +--- + t + t + t + t + t +(5 rows) + diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql index 8df3e93219..428f6e1cfc 100644 --- a/contrib/citext/sql/citext.sql +++ b/contrib/citext/sql/citext.sql @@ -160,7 +160,7 @@ SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::bpchar; SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'; SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::citext; --- LIKE shoudl be case-insensitive +-- 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; @@ -182,6 +182,355 @@ SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name; 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 'foo'::bytea::citext = 'foo' AS t; +SELECT 'foo'::citext::bytea = 'foo'::bytea 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 '

foo

'::xml::citext = '

foo

' AS t; +SELECT '

foo

'::citext::xml::text = '

foo

'::xml::text 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, + name name, + bytea bytea, + boolean boolean, + float4 float4, + float8 float8, + numeric numeric, + int8 int8, + int4 int4, + int2 int2, + cidr cidr, + inet inet, + macaddr macaddr, + xml xml, + 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 (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 xml on assignment. +INSERT INTO caster (xml) VALUES ('

foo

'::text); +INSERT INTO caster (text) VALUES ('

foo

'::xml); +INSERT INTO caster (xml) VALUES ('

foo

'::citext); +INSERT INTO caster (citext) VALUES ('

foo

'::xml); + +-- 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; @@ -246,12 +595,51 @@ 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_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t; -SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' 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 "null"; + +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; @@ -264,22 +652,24 @@ 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, 'ig' ) = 2 AS t; +SELECT strpos('high', 'ig'::citext) = 2 AS t; SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t; +SELECT strpos('high'::citext, 'IG' ) = 2 AS t; +SELECT strpos('high', 'IG'::citext) = 2 AS t; +SELECT strpos('high'::citext, 'IG'::citext) = 2 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; - --- TODO These functions should work case-insensitively, but don't. -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS "t TODO"; -SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'THM' AS "t TODO"; -SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS "t TODO"; -SELECT regexp_split_to_table('helloTworld'::citext, 't') AS "words TODO"; -SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS "t TODO"; -SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS "t TODO"; -SELECT strpos('high'::citext, 'IG'::citext) = 2 AS "t TODO"; -SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS "t TODO"; +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) @@ -321,8 +711,4 @@ VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ), 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; - ---- citext should work as source or destination of I/O conversion casts -SELECT cidr( '192.168.1.2'::citext ) = cidr( '192.168.1.2'::text ) AS "t"; -SELECT '192.168.1.2'::cidr::citext = '192.168.1.2'::cidr::text AS "t"; +SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt; diff --git a/contrib/citext/uninstall_citext.sql b/contrib/citext/uninstall_citext.sql index d0a2cb9631..9f786599cb 100644 --- a/contrib/citext/uninstall_citext.sql +++ b/contrib/citext/uninstall_citext.sql @@ -40,8 +40,12 @@ DROP CAST (citext AS bpchar); DROP CAST (text AS citext); DROP CAST (varchar AS citext); DROP CAST (bpchar AS citext); +DROP CAST (boolean AS citext); +DROP CAST (inet AS citext); DROP FUNCTION citext(bpchar); +DROP FUNCTION citext(boolean); +DROP FUNCTION citext(inet); DROP FUNCTION citext_eq(citext, citext); DROP FUNCTION citext_ne(citext, citext); DROP FUNCTION citext_lt(citext, citext); @@ -60,5 +64,17 @@ DROP FUNCTION texticlike(citext, text); DROP FUNCTION texticnlike(citext, text); DROP FUNCTION texticregexeq(citext, text); DROP FUNCTION texticregexne(citext, text); +DROP FUNCTION regexp_matches( citext, citext ); +DROP FUNCTION regexp_matches( citext, citext, text ); +DROP FUNCTION regexp_replace( citext, citext, text ); +DROP FUNCTION regexp_replace( citext, citext, text, text ); +DROP FUNCTION regexp_split_to_array( citext, citext ); +DROP FUNCTION regexp_split_to_array( citext, citext, text ); +DROP FUNCTION regexp_split_to_table( citext, citext ); +DROP FUNCTION regexp_split_to_table( citext, citext, text ); +DROP FUNCTION strpos( citext, citext ); +DROP FUNCTION replace( citext, citext, citext ); +DROP FUNCTION split_part( citext, citext, int ); +DROP FUNCTION translate( citext, citext, text ); DROP TYPE citext CASCADE; -- 2.11.4.GIT