diff -U3 /tmp/cirrus-ci-build/contrib/citext/expected/citext.out /tmp/cirrus-ci-build/build/testrun/citext/regress/results/citext.out --- /tmp/cirrus-ci-build/contrib/citext/expected/citext.out 2025-02-28 20:07:58.545749190 +0000 +++ /tmp/cirrus-ci-build/build/testrun/citext/regress/results/citext.out 2025-02-28 20:13:45.826879737 +0000 @@ -325,2360 +325,7 @@ -- Check the min() and max() aggregates, with and without index. set enable_seqscan = off; SELECT MIN(name) AS "ABA" FROM srt; - ABA ------ - ABA -(1 row) - -SELECT MAX(name) AS abd FROM srt; - abd ------ - abd -(1 row) - -reset enable_seqscan; -set enable_indexscan = off; -SELECT MIN(name) AS "ABA" FROM srt; - ABA ------ - ABA -(1 row) - -SELECT MAX(name) AS abd FROM srt; - abd ------ - abd -(1 row) - -reset enable_indexscan; --- Check sorting likewise -set enable_seqscan = off; -SELECT name FROM srt ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -reset enable_seqscan; -set enable_indexscan = off; -SELECT name FROM srt ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -reset enable_indexscan; --- Test assignment casts. -SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::text; - aba ------ - aba -(1 row) - -SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::varchar; - aba ------ - aba -(1 row) - -SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::bpchar; - aba ------ - aba -(1 row) - -SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'; - aba ------ - aba -(1 row) - -SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::citext; - aba ------ - aba -(1 row) - --- LIKE should be case-insensitive -SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name; - name ------- -(0 rows) - -SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name; - name ------- -(0 rows) - --- ~~ should be case-insensitive -SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name; - name ------- -(0 rows) - -SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name; - name ------- -(0 rows) - --- ~ should be case-insensitive -SELECT name FROM srt WHERE name ~ '^a' ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name; - name ------- - abb - ABC - abd -(3 rows) - -SELECT name FROM srt WHERE name ~ '^A' ORDER BY name; - name ------- - ABA - abb - ABC - abd -(4 rows) - -SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name; - name ------- - abb - ABC - abd -(3 rows) - --- SIMILAR TO should be case-insensitive. -SELECT name FROM srt WHERE name SIMILAR TO '%a.*'; - name ------- - ABA -(1 row) - -SELECT name FROM srt WHERE name SIMILAR TO '%A.*'; - name ------- - ABA -(1 row) - --- Explicit casts. -SELECT true::citext = 'true' AS t; - t ---- - t -(1 row) - -SELECT 'true'::citext::boolean = true AS t; - t ---- - t -(1 row) - -SELECT 4::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT 4::int4::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT '4'::citext::int4 = 4 AS t; - t ---- - t -(1 row) - -SELECT 4::integer::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT '4'::citext::integer = 4 AS t; - t ---- - t -(1 row) - -SELECT 4::int8::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT '4'::citext::int8 = 4 AS t; - t ---- - t -(1 row) - -SELECT 4::bigint::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT '4'::citext::bigint = 4 AS t; - t ---- - t -(1 row) - -SELECT 4::int2::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT '4'::citext::int2 = 4 AS t; - t ---- - t -(1 row) - -SELECT 4::smallint::citext = '4' AS t; - t ---- - t -(1 row) - -SELECT '4'::citext::smallint = 4 AS t; - t ---- - t -(1 row) - -SELECT 4.0::numeric = '4.0' AS t; - t ---- - t -(1 row) - -SELECT '4.0'::citext::numeric = 4.0 AS t; - t ---- - t -(1 row) - -SELECT 4.0::decimal = '4.0' AS t; - t ---- - t -(1 row) - -SELECT '4.0'::citext::decimal = 4.0 AS t; - t ---- - t -(1 row) - -SELECT 4.0::real = '4.0' AS t; - t ---- - t -(1 row) - -SELECT '4.0'::citext::real = 4.0 AS t; - t ---- - t -(1 row) - -SELECT 4.0::float4 = '4.0' AS t; - t ---- - t -(1 row) - -SELECT '4.0'::citext::float4 = 4.0 AS t; - t ---- - t -(1 row) - -SELECT 4.0::double precision = '4.0' AS t; - t ---- - t -(1 row) - -SELECT '4.0'::citext::double precision = 4.0 AS t; - t ---- - t -(1 row) - -SELECT 4.0::float8 = '4.0' AS t; - t ---- - t -(1 row) - -SELECT '4.0'::citext::float8 = 4.0 AS t; - t ---- - t -(1 row) - -SELECT 'foo'::name::citext = 'foo' AS t; - t ---- - t -(1 row) - -SELECT 'foo'::citext::name = 'foo'::name AS t; - t ---- - t -(1 row) - -SELECT 'f'::char::citext = 'f' AS t; - t ---- - t -(1 row) - -SELECT 'f'::citext::char = 'f'::char AS t; - t ---- - t -(1 row) - -SELECT 'f'::"char"::citext = 'f' AS t; - t ---- - t -(1 row) - -SELECT 'f'::citext::"char" = 'f'::"char" AS t; - t ---- - t -(1 row) - -SELECT '100'::money::citext = '$100.00' AS t; - t ---- - t -(1 row) - -SELECT '100'::citext::money = '100'::money AS t; - t ---- - t -(1 row) - -SELECT 'a'::char::citext = 'a' AS t; - t ---- - t -(1 row) - -SELECT 'a'::citext::char = 'a'::char AS t; - t ---- - t -(1 row) - -SELECT 'foo'::varchar::citext = 'foo' AS t; - t ---- - t -(1 row) - -SELECT 'foo'::citext::varchar = 'foo'::varchar AS t; - t ---- - t -(1 row) - -SELECT 'foo'::text::citext = 'foo' AS t; - t ---- - t -(1 row) - -SELECT 'foo'::citext::text = 'foo'::text AS t; - t ---- - t -(1 row) - -SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t; - t ---- - t -(1 row) - -SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t; - t ---- - t -(1 row) - -SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t; - t ---- - t -(1 row) - -SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t; - t ---- - t -(1 row) - -SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t; - t ---- - t -(1 row) - -SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t; - t ---- - t -(1 row) - -SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t; - t ---- - t -(1 row) - -SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t; - t ---- - t -(1 row) - -SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t; - t ---- - t -(1 row) - -SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t; - t ---- - t -(1 row) - -SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t; - t ---- - t -(1 row) - -SELECT '1 hour'::citext::interval = '1 hour'::interval AS t; - t ---- - t -(1 row) - -SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t; - t ---- - t -(1 row) - -SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t; - t ---- - t -(1 row) - -SELECT '04:05:06'::time::citext = '04:05:06' AS t; - t ---- - t -(1 row) - -SELECT '04:05:06'::citext::time = '04:05:06'::time AS t; - t ---- - t -(1 row) - -SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t; - t ---- - t -(1 row) - -SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t; - t ---- - t -(1 row) - -SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t; - t ---- - t -(1 row) - -SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t; - t ---- - t -(1 row) - -SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t; - t ---- - t -(1 row) - -SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t; - t ---- - t -(1 row) - -SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t; - t ---- - t -(1 row) - -SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t; - t ---- - t -(1 row) - -SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t; - t ---- - t -(1 row) - -SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t; - t ---- - t -(1 row) - -SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t; - t ---- - t -(1 row) - -SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t; - t ---- - t -(1 row) - -SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t; - t ---- - t -(1 row) - -SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t; - t ---- - t -(1 row) - -SELECT '101'::bit::citext = '101'::bit::text AS t; - t ---- - t -(1 row) - -SELECT '101'::citext::bit = '101'::text::bit AS t; - t ---- - t -(1 row) - -SELECT '101'::bit varying::citext = '101'::bit varying::text AS t; - t ---- - t -(1 row) - -SELECT '101'::citext::bit varying = '101'::text::bit varying AS t; - t ---- - t -(1 row) - -SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t; - t ---- - t -(1 row) - -SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t; - t ---- - t -(1 row) - -SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t; - t ---- - t -(1 row) - -SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t; - t ---- - t -(1 row) - -SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t; - t ---- - t -(1 row) - -SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t; - t ---- - t -(1 row) - -CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); -SELECT 'sad'::mood::citext = 'sad' AS t; - t ---- - t -(1 row) - -SELECT 'sad'::citext::mood = 'sad'::mood AS t; - t ---- - t -(1 row) - --- Assignment casts. -CREATE TABLE caster ( - citext citext, - text text, - varchar varchar, - bpchar bpchar, - char char, - chr "char", - name name, - bytea bytea, - boolean boolean, - float4 float4, - float8 float8, - numeric numeric, - int8 int8, - int4 int4, - int2 int2, - cidr cidr, - inet inet, - macaddr macaddr, - money money, - timestamp timestamp, - timestamptz timestamptz, - interval interval, - date date, - time time, - timetz timetz, - point point, - lseg lseg, - box box, - path path, - polygon polygon, - circle circle, - bit bit, - bitv bit varying, - tsvector tsvector, - tsquery tsquery, - uuid uuid -); -INSERT INTO caster (text) VALUES ('foo'::citext); -INSERT INTO caster (citext) VALUES ('foo'::text); -INSERT INTO caster (varchar) VALUES ('foo'::text); -INSERT INTO caster (text) VALUES ('foo'::varchar); -INSERT INTO caster (varchar) VALUES ('foo'::citext); -INSERT INTO caster (citext) VALUES ('foo'::varchar); -INSERT INTO caster (bpchar) VALUES ('foo'::text); -INSERT INTO caster (text) VALUES ('foo'::bpchar); -INSERT INTO caster (bpchar) VALUES ('foo'::citext); -INSERT INTO caster (citext) VALUES ('foo'::bpchar); -INSERT INTO caster (char) VALUES ('f'::text); -INSERT INTO caster (text) VALUES ('f'::char); -INSERT INTO caster (char) VALUES ('f'::citext); -INSERT INTO caster (citext) VALUES ('f'::char); -INSERT INTO caster (chr) VALUES ('f'::text); -INSERT INTO caster (text) VALUES ('f'::"char"); -INSERT INTO caster (chr) VALUES ('f'::citext); -- requires cast -ERROR: column "chr" is of type "char" but expression is of type citext -LINE 1: INSERT INTO caster (chr) VALUES ('f'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (chr) VALUES ('f'::citext::text); -INSERT INTO caster (citext) VALUES ('f'::"char"); -INSERT INTO caster (name) VALUES ('foo'::text); -INSERT INTO caster (text) VALUES ('foo'::name); -INSERT INTO caster (name) VALUES ('foo'::citext); -INSERT INTO caster (citext) VALUES ('foo'::name); --- Cannot cast to bytea on assignment. -INSERT INTO caster (bytea) VALUES ('foo'::text); -ERROR: column "bytea" is of type bytea but expression is of type text -LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('foo'::bytea); -INSERT INTO caster (bytea) VALUES ('foo'::citext); -ERROR: column "bytea" is of type bytea but expression is of type citext -LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('foo'::bytea); --- Cannot cast to boolean on assignment. -INSERT INTO caster (boolean) VALUES ('t'::text); -ERROR: column "boolean" is of type boolean but expression is of type text -LINE 1: INSERT INTO caster (boolean) VALUES ('t'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('t'::boolean); -INSERT INTO caster (boolean) VALUES ('t'::citext); -ERROR: column "boolean" is of type boolean but expression is of type citext -LINE 1: INSERT INTO caster (boolean) VALUES ('t'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('t'::boolean); --- Cannot cast to float8 on assignment. -INSERT INTO caster (float8) VALUES ('12.42'::text); -ERROR: column "float8" is of type double precision but expression is of type text -LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12.42'::float8); -INSERT INTO caster (float8) VALUES ('12.42'::citext); -ERROR: column "float8" is of type double precision but expression is of type citext -LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12.42'::float8); --- Cannot cast to float4 on assignment. -INSERT INTO caster (float4) VALUES ('12.42'::text); -ERROR: column "float4" is of type real but expression is of type text -LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12.42'::float4); -INSERT INTO caster (float4) VALUES ('12.42'::citext); -ERROR: column "float4" is of type real but expression is of type citext -LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12.42'::float4); --- Cannot cast to numeric on assignment. -INSERT INTO caster (numeric) VALUES ('12.42'::text); -ERROR: column "numeric" is of type numeric but expression is of type text -LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12.42'::numeric); -INSERT INTO caster (numeric) VALUES ('12.42'::citext); -ERROR: column "numeric" is of type numeric but expression is of type citext -LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12.42'::numeric); --- Cannot cast to int8 on assignment. -INSERT INTO caster (int8) VALUES ('12'::text); -ERROR: column "int8" is of type bigint but expression is of type text -LINE 1: INSERT INTO caster (int8) VALUES ('12'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12'::int8); -INSERT INTO caster (int8) VALUES ('12'::citext); -ERROR: column "int8" is of type bigint but expression is of type citext -LINE 1: INSERT INTO caster (int8) VALUES ('12'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12'::int8); --- Cannot cast to int4 on assignment. -INSERT INTO caster (int4) VALUES ('12'::text); -ERROR: column "int4" is of type integer but expression is of type text -LINE 1: INSERT INTO caster (int4) VALUES ('12'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12'::int4); -INSERT INTO caster (int4) VALUES ('12'::citext); -ERROR: column "int4" is of type integer but expression is of type citext -LINE 1: INSERT INTO caster (int4) VALUES ('12'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12'::int4); --- Cannot cast to int2 on assignment. -INSERT INTO caster (int2) VALUES ('12'::text); -ERROR: column "int2" is of type smallint but expression is of type text -LINE 1: INSERT INTO caster (int2) VALUES ('12'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12'::int2); -INSERT INTO caster (int2) VALUES ('12'::citext); -ERROR: column "int2" is of type smallint but expression is of type citext -LINE 1: INSERT INTO caster (int2) VALUES ('12'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12'::int2); --- Cannot cast to cidr on assignment. -INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text); -ERROR: column "cidr" is of type cidr but expression is of type text -LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr); -INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext); -ERROR: column "cidr" is of type cidr but expression is of type citext -LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr); --- Cannot cast to inet on assignment. -INSERT INTO caster (inet) VALUES ('192.168.100.128'::text); -ERROR: column "inet" is of type inet but expression is of type text -LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('192.168.100.128'::inet); -INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext); -ERROR: column "inet" is of type inet but expression is of type citext -LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet); --- Cannot cast to macaddr on assignment. -INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text); -ERROR: column "macaddr" is of type macaddr but expression is of type text -LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr); -INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext); -ERROR: column "macaddr" is of type macaddr but expression is of type citext -LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr); --- Cannot cast to money on assignment. -INSERT INTO caster (money) VALUES ('12'::text); -ERROR: column "money" is of type money but expression is of type text -LINE 1: INSERT INTO caster (money) VALUES ('12'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('12'::money); -INSERT INTO caster (money) VALUES ('12'::citext); -ERROR: column "money" is of type money but expression is of type citext -LINE 1: INSERT INTO caster (money) VALUES ('12'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('12'::money); --- Cannot cast to timestamp on assignment. -INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text); -ERROR: column "timestamp" is of type timestamp without time zone but expression is of type text -LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp); -INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext); -ERROR: column "timestamp" is of type timestamp without time zone but expression is of type citext -LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp); --- Cannot cast to timestamptz on assignment. -INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text); -ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type text -LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz); -INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext); -ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type citext -LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz); --- Cannot cast to interval on assignment. -INSERT INTO caster (interval) VALUES ('1 hour'::text); -ERROR: column "interval" is of type interval but expression is of type text -LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('1 hour'::interval); -INSERT INTO caster (interval) VALUES ('1 hour'::citext); -ERROR: column "interval" is of type interval but expression is of type citext -LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::citext)... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('1 hour'::interval); --- Cannot cast to date on assignment. -INSERT INTO caster (date) VALUES ('1999-01-08'::text); -ERROR: column "date" is of type date but expression is of type text -LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::tex... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('1999-01-08'::date); -INSERT INTO caster (date) VALUES ('1999-01-08'::citext); -ERROR: column "date" is of type date but expression is of type citext -LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::cit... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('1999-01-08'::date); --- Cannot cast to time on assignment. -INSERT INTO caster (time) VALUES ('04:05:06'::text); -ERROR: column "time" is of type time without time zone but expression is of type text -LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::text)... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('04:05:06'::time); -INSERT INTO caster (time) VALUES ('04:05:06'::citext); -ERROR: column "time" is of type time without time zone but expression is of type citext -LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::citex... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('04:05:06'::time); --- Cannot cast to timetz on assignment. -INSERT INTO caster (timetz) VALUES ('04:05:06'::text); -ERROR: column "timetz" is of type time with time zone but expression is of type text -LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::text)... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('04:05:06'::timetz); -INSERT INTO caster (timetz) VALUES ('04:05:06'::citext); -ERROR: column "timetz" is of type time with time zone but expression is of type citext -LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::citex... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('04:05:06'::timetz); --- Cannot cast to point on assignment. -INSERT INTO caster (point) VALUES ('( 1 , 1)'::text); -ERROR: column "point" is of type point but expression is of type text -LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::text)... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('( 1 , 1)'::point); -INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext); -ERROR: column "point" is of type point but expression is of type citext -LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::citex... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point); --- Cannot cast to lseg on assignment. -INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text); -ERROR: column "lseg" is of type lseg but expression is of type text -LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg); -INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext); -ERROR: column "lseg" is of type lseg but expression is of type citext -LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg); --- Cannot cast to box on assignment. -INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text); -ERROR: column "box" is of type box but expression is of type text -LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::te... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box); -INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext); -ERROR: column "box" is of type box but expression is of type citext -LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::ci... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box); --- Cannot cast to path on assignment. -INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text); -ERROR: column "path" is of type path but expression is of type text -LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path); -INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext); -ERROR: column "path" is of type path but expression is of type citext -LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path); --- Cannot cast to polygon on assignment. -INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text); -ERROR: column "polygon" is of type polygon but expression is of type text -LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon); -INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext); -ERROR: column "polygon" is of type polygon but expression is of type citext -LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon); --- Cannot cast to circle on assignment. -INSERT INTO caster (circle) VALUES ('((0,0),2)'::text); -ERROR: column "circle" is of type circle but expression is of type text -LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::text... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('((0,0),2)'::circle); -INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext); -ERROR: column "circle" is of type circle but expression is of type citext -LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::cite... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle); --- Cannot cast to bit on assignment. -INSERT INTO caster (bit) VALUES ('101'::text); -ERROR: column "bit" is of type bit but expression is of type text -LINE 1: INSERT INTO caster (bit) VALUES ('101'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('101'::bit); -INSERT INTO caster (bit) VALUES ('101'::citext); -ERROR: column "bit" is of type bit but expression is of type citext -LINE 1: INSERT INTO caster (bit) VALUES ('101'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('101'::bit); --- Cannot cast to bit varying on assignment. -INSERT INTO caster (bitv) VALUES ('101'::text); -ERROR: column "bitv" is of type bit varying but expression is of type text -LINE 1: INSERT INTO caster (bitv) VALUES ('101'::text); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('101'::bit varying); -INSERT INTO caster (bitv) VALUES ('101'::citext); -ERROR: column "bitv" is of type bit varying but expression is of type citext -LINE 1: INSERT INTO caster (bitv) VALUES ('101'::citext); - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('101'::bit varying); --- Cannot cast to tsvector on assignment. -INSERT INTO caster (tsvector) VALUES ('the fat cat'::text); -ERROR: column "tsvector" is of type tsvector but expression is of type text -LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::te... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('the fat cat'::tsvector); -INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext); -ERROR: column "tsvector" is of type tsvector but expression is of type citext -LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::ci... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector); --- Cannot cast to tsquery on assignment. -INSERT INTO caster (tsquery) VALUES ('fat & rat'::text); -ERROR: column "tsquery" is of type tsquery but expression is of type text -LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::text... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('fat & rat'::tsquery); -INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext); -ERROR: column "tsquery" is of type tsquery but expression is of type citext -LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::cite... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery); --- Cannot cast to uuid on assignment. -INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text); -ERROR: column "uuid" is of type uuid but expression is of type text -LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid); -INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext); -ERROR: column "uuid" is of type uuid but expression is of type citext -LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e... - ^ -HINT: You will need to rewrite or cast the expression. -INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid); --- Table 9-5. SQL String Functions and Operators -SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat; - citext_concat ---------------- - t -(1 row) - -SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat; - text_concat -------------- - t -(1 row) - -SELECT 42 || ': value'::citext ='42: value' AS int_concat; - int_concat ------------- - t -(1 row) - -SELECT bit_length('jose'::citext) = 32 AS t; - t ---- - t -(1 row) - -SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT textlen( name ) = textlen( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT char_length( name ) = char_length( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT lower( name ) = lower( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT substr('alphabet'::citext, 3) = 'phabet' AS t; - t ---- - t -(1 row) - -SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t; - t ---- - t -(1 row) - -SELECT substring('alphabet'::citext, 3) = 'phabet' AS t; - t ---- - t -(1 row) - -SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t; - t ---- - t -(1 row) - -SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t; - t ---- - t -(1 row) - -SELECT substring('Thomas'::citext from 2) = 'homas' AS t; - t ---- - t -(1 row) - -SELECT substring('Thomas'::citext from '...$') = 'mas' AS t; - t ---- - t -(1 row) - -SELECT substring('Thomas'::citext similar '%#"o_a#"_' escape '#') = 'oma' AS t; - t ---- - t -(1 row) - -SELECT trim(' trim '::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT upper( name ) = upper( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - --- Table 9-6. Other String Functions. -SELECT ascii( name ) = ascii( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT btrim(' trim'::citext ) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t; - t ---- - t -(1 row) - --- chr() takes an int and returns text. --- convert() and convert_from take bytea and return text. -SELECT convert_from( name::bytea, 'SQL_ASCII' ) = convert_from( name::text::bytea, 'SQL_ASCII' ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t; - t ---- - t -(1 row) - --- encode() takes bytea and returns text. -SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t; - t ---- - t -(1 row) - -SELECT length( name ) = length( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT lpad('hi'::citext, 5 ) = ' hi' AS t; - t ---- - t -(1 row) - -SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t; - t ---- - t -(1 row) - -SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t; - t ---- - t -(1 row) - -SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t; - t ---- - t -(1 row) - -SELECT ltrim(' trim'::citext ) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t; - t ---- - t -(1 row) - --- pg_client_encoding() takes no args and returns name. -SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT regexp_match('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_match('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - --- c forces case-sensitive -SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no result"; - no result ------------ - -(1 row) - --- g is not allowed -SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error"; -ERROR: regexp_match() does not support the "global" option -HINT: Use the regexp_matches function instead. -CONTEXT: SQL function "regexp_match" statement 1 -SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t; - t ---- - t -(1 row) - --- c forces case-sensitive -SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no rows"; - no rows ---------- -(0 rows) - --- g allows multiple output rows -SELECT regexp_matches('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g'::citext) AS "two rows"; - two rows -------------- - {bar,beque} - {bar,beque} -(2 rows) - -SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t; - t ---- - t -(1 row) - -SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t; - t ---- - t -(1 row) - -SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t; - t ---- - t -(1 row) - -SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t; - t ---- - t -(1 row) - --- c forces case-sensitive -SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t; - t ---- - t -(1 row) - --- c forces case-sensitive -SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t; - t ---- - t -(1 row) - -SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words; - words -------- - hello - world -(2 rows) - -SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words; - words -------- - hello - world -(2 rows) - -SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words; - words -------- - hello - world -(2 rows) - -SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words; - words -------- - hello - world -(2 rows) - --- c forces case-sensitive -SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word; - word -------------- - helloTworld -(1 row) - -SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t; - t ---- - t -(1 row) - -SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t; - t ---- - t -(1 row) - -SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t; - t ---- - t -(1 row) - -SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t; - t ---- - t -(1 row) - -SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t; - t ---- - t -(1 row) - -SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t; - t ---- - t -(1 row) - -SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t; - t ---- - t -(1 row) - -SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t; - t ---- - t -(1 row) - -SELECT rtrim('trim '::citext ) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t; - t ---- - t -(1 row) - -SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t; - t ---- - t -(1 row) - -SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t; - t ---- - t -(1 row) - -SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t; - t ---- - t -(1 row) - -SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t; - t ---- - t -(1 row) - -SELECT strpos('high'::citext, 'gh' ) = 3 AS t; - t ---- - t -(1 row) - -SELECT strpos('high', 'gh'::citext) = 3 AS t; - t ---- - t -(1 row) - -SELECT strpos('high'::citext, 'gh'::citext) = 3 AS t; - t ---- - t -(1 row) - -SELECT strpos('high'::citext, 'GH' ) = 3 AS t; - t ---- - t -(1 row) - -SELECT strpos('high', 'GH'::citext) = 3 AS t; - t ---- - t -(1 row) - -SELECT strpos('high'::citext, 'GH'::citext) = 3 AS t; - t ---- - t -(1 row) - --- to_ascii() does not support UTF-8. --- to_hex() takes a numeric argument. -SELECT substr('alphabet', 3, 2) = 'ph' AS t; - t ---- - t -(1 row) - -SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - -SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; - t ---- - t -(1 row) - --- Table 9-20. Formatting Functions -SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) - = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; - t ---- - t -(1 row) - -SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY') - = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; - t ---- - t -(1 row) - -SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext) - = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; - t ---- - t -(1 row) - -SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext) - = to_number('12,454.8-', '99G999D9S') AS t; - t ---- - t -(1 row) - -SELECT to_number('12,454.8-'::citext, '99G999D9S') - = to_number('12,454.8-', '99G999D9S') AS t; - t ---- - t -(1 row) - -SELECT to_number('12,454.8-', '99G999D9S'::citext) - = to_number('12,454.8-', '99G999D9S') AS t; - t ---- - t -(1 row) - -SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) - = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; - t ---- - t -(1 row) - -SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY') - = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; - t ---- - t -(1 row) - -SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext) - = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; - t ---- - t -(1 row) - --- Try assigning function results to a column. -SELECT COUNT(*) = 8::bigint AS t FROM try; - t ---- - t -(1 row) - -INSERT INTO try -VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ), - ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timestamptz - ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ), - ( to_char( current_date, '999') ), - ( to_char( 125::int, '999') ), - ( to_char( 127::int4, '999') ), - ( to_char( 126::int8, '999') ), - ( to_char( 128.8::real, '999D9') ), - ( to_char( 125.7::float4, '999D9') ), - ( to_char( 125.9::float8, '999D9') ), - ( to_char( -125.8::numeric, '999D99S') ); -SELECT COUNT(*) = 19::bigint AS t FROM try; - t ---- - t -(1 row) - -SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - -SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt; - t ---- - t - t - t - t -(4 rows) - --- Ensure correct behavior for citext with materialized views. -CREATE TABLE citext_table ( - id serial primary key, - name citext -); -INSERT INTO citext_table (name) - VALUES ('one'), ('two'), ('three'), (NULL), (NULL); -CREATE MATERIALIZED VIEW citext_matview AS - SELECT * FROM citext_table; -CREATE UNIQUE INDEX citext_matview_id - ON citext_matview (id); -SELECT * - FROM citext_matview m - FULL JOIN citext_table t ON (t.id = m.id AND t *= m) - WHERE t.id IS NULL OR m.id IS NULL; - id | name | id | name -----+------+----+------ -(0 rows) - -UPDATE citext_table SET name = 'Two' WHERE name = 'TWO'; -SELECT * - FROM citext_matview m - FULL JOIN citext_table t ON (t.id = m.id AND t *= m) - WHERE t.id IS NULL OR m.id IS NULL; - id | name | id | name -----+------+----+------ - | | 2 | Two - 2 | two | | -(2 rows) - -REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview; -SELECT * FROM citext_matview ORDER BY id; - id | name -----+------- - 1 | one - 2 | Two - 3 | three - 4 | - 5 | -(5 rows) - --- test citext_pattern_cmp() function explicitly. -SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero; - zero ------- - 0 -(1 row) - -SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero; - zero ------- - 0 -(1 row) - -SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero; - zero ------- - 0 -(1 row) - -SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::citext) < 0 AS true; - true ------- - t -(1 row) - --- test operator functions --- lt -SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false; - false -------- - f -(1 row) - --- le -SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false; - false -------- - f -(1 row) - --- gt -SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true; - true ------- - t -(1 row) - --- ge -SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false; - false -------- - f -(1 row) - -SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true; - true ------- - t -(1 row) - -SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true; - true ------- - t -(1 row) - --- Test ~<~ and ~<=~ -SELECT 'a'::citext ~<~ 'B'::citext AS t; - t ---- - t -(1 row) - -SELECT 'b'::citext ~<~ 'A'::citext AS f; - f ---- - f -(1 row) - -SELECT 'a'::citext ~<=~ 'B'::citext AS t; - t ---- - t -(1 row) - -SELECT 'a'::citext ~<=~ 'A'::citext AS t; - t ---- - t -(1 row) - --- Test ~>~ and ~>=~ -SELECT 'B'::citext ~>~ 'a'::citext AS t; - t ---- - t -(1 row) - -SELECT 'b'::citext ~>~ 'A'::citext AS t; - t ---- - t -(1 row) - -SELECT 'B'::citext ~>~ 'b'::citext AS f; - f ---- - f -(1 row) - -SELECT 'B'::citext ~>=~ 'b'::citext AS t; - t ---- - t -(1 row) - --- Test implicit casting. citext casts to text, but not vice-versa. -SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins. - t ---- - t -(1 row) - -SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins. - t ---- - t -(1 row) - -SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins. - t ---- - t -(1 row) - -SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins. - t ---- - t -(1 row) - --- Test implicit casting. citext casts to varchar, but not vice-versa. -SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins. - t ---- - t -(1 row) - -SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins. - t ---- - t -(1 row) - -SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins. - t ---- - t -(1 row) - -SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins. - t ---- - t -(1 row) - +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost