2 -- Test citext datatype
4 CREATE EXTENSION citext;
5 -- Test the operators and indexing functions
7 SELECT 'a'::citext = 'a'::citext AS t;
13 SELECT 'a'::citext = 'A'::citext AS t;
19 SELECT 'a'::citext = 'A'::text AS f; -- text wins the discussion
25 SELECT 'a'::citext = 'b'::citext AS f;
31 SELECT 'a'::citext = 'ab'::citext AS f;
37 SELECT 'a'::citext <> 'ab'::citext AS t;
43 -- Multibyte sanity tests. Uncomment to run.
44 -- SELECT 'À'::citext = 'À'::citext AS t;
45 -- SELECT 'À'::citext = 'à'::citext AS t;
46 -- SELECT 'À'::text = 'à'::text AS f; -- text wins.
47 -- SELECT 'À'::citext <> 'B'::citext AS t;
48 -- Test combining characters making up canonically equivalent strings.
49 -- SELECT 'Ä'::text <> 'Ä'::text AS t;
50 -- SELECT 'Ä'::citext <> 'Ä'::citext AS t;
51 -- Test the Turkish dotted I. The lowercase is a single byte while the
52 -- uppercase is multibyte. This is why the comparison code can't be optimized
53 -- to compare string lengths.
54 -- SELECT 'i'::citext = 'İ'::citext AS t;
56 -- SELECT 'láska'::citext <> 'laská'::citext AS t;
57 -- SELECT 'Ask Bjørn Hansen'::citext = 'Ask Bjørn Hansen'::citext AS t;
58 -- SELECT 'Ask Bjørn Hansen'::citext = 'ASK BJØRN HANSEN'::citext AS t;
59 -- SELECT 'Ask Bjørn Hansen'::citext <> 'Ask Bjorn Hansen'::citext AS t;
60 -- SELECT 'Ask Bjørn Hansen'::citext <> 'ASK BJORN HANSEN'::citext AS t;
61 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS zero;
62 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) AS zero;
63 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) AS zero;
64 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) AS positive;
65 -- SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS negative;
67 SELECT 'B'::citext > 'a'::citext AS t;
73 SELECT 'b'::citext > 'A'::citext AS t;
79 SELECT 'B'::citext > 'b'::citext AS f;
85 SELECT 'B'::citext >= 'b'::citext AS t;
92 SELECT 'a'::citext < 'B'::citext AS t;
98 SELECT 'a'::citext <= 'B'::citext AS t;
104 -- Test implicit casting. citext casts to text, but not vice-versa.
105 SELECT 'a'::citext = 'a'::text AS t;
111 SELECT 'A'::text <> 'a'::citext AS t;
117 SELECT 'B'::citext < 'a'::text AS t; -- text wins.
123 SELECT 'B'::citext <= 'a'::text AS t; -- text wins.
129 SELECT 'a'::citext > 'B'::text AS t; -- text wins.
135 SELECT 'a'::citext >= 'B'::text AS t; -- text wins.
141 -- Test implicit casting. citext casts to varchar, but not vice-versa.
142 SELECT 'a'::citext = 'a'::varchar AS t;
148 SELECT 'A'::varchar <> 'a'::citext AS t;
154 SELECT 'B'::citext < 'a'::varchar AS t; -- varchar wins.
160 SELECT 'B'::citext <= 'a'::varchar AS t; -- varchar wins.
166 SELECT 'a'::citext > 'B'::varchar AS t; -- varchar wins.
172 SELECT 'a'::citext >= 'B'::varchar AS t; -- varchar wins.
178 -- A couple of longer examlpes to ensure that we don't get any issues with bad
179 -- conversions to char[] in the c code. Yes, I did do this.
180 SELECT 'aardvark'::citext = 'aardvark'::citext AS t;
186 SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
192 -- Check the citext_cmp() function explicitly.
193 SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
199 SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
205 SELECT citext_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
211 SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true;
217 -- Do some tests using a table and index.
218 CREATE TEMP TABLE try (
219 name citext PRIMARY KEY
221 INSERT INTO try (name)
222 VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');
223 SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â';
235 SELECT name, 'a' = name AS t FROM try where name = 'a';
241 SELECT name, 'A' = name AS "eq_A" FROM try WHERE name <> 'â';
253 SELECT name, 'A' = name AS t FROM try where name = 'A';
259 SELECT name, 'A' = name AS t FROM try where name = 'A';
265 -- expected failures on duplicate key
266 INSERT INTO try (name) VALUES ('a');
267 ERROR: duplicate key value violates unique constraint "try_pkey"
268 DETAIL: Key (name)=(a) already exists.
269 INSERT INTO try (name) VALUES ('A');
270 ERROR: duplicate key value violates unique constraint "try_pkey"
271 DETAIL: Key (name)=(A) already exists.
272 INSERT INTO try (name) VALUES ('aB');
273 ERROR: duplicate key value violates unique constraint "try_pkey"
274 DETAIL: Key (name)=(aB) already exists.
275 -- Make sure that citext_smaller() and citext_lager() work properly.
276 SELECT citext_smaller( 'aa'::citext, 'ab'::citext ) = 'aa' AS t;
282 SELECT citext_smaller( 'AAAA'::citext, 'bbbb'::citext ) = 'AAAA' AS t;
288 SELECT citext_smaller( 'aardvark'::citext, 'Aaba'::citext ) = 'Aaba' AS t;
294 SELECT citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ) = 'AARDVARK' AS t;
300 SELECT citext_larger( 'aa'::citext, 'ab'::citext ) = 'ab' AS t;
306 SELECT citext_larger( 'AAAA'::citext, 'bbbb'::citext ) = 'bbbb' AS t;
312 SELECT citext_larger( 'aardvark'::citext, 'Aaba'::citext ) = 'aardvark' AS t;
318 -- Test aggregate functions and sort ordering
319 CREATE TEMP TABLE srt (
322 INSERT INTO srt (name)
328 CREATE INDEX srt_name ON srt (name);
329 -- Check the min() and max() aggregates, with and without index.
330 set enable_seqscan = off;
331 SELECT MIN(name) AS "AAA" FROM srt;
337 SELECT MAX(name) AS abd FROM srt;
343 reset enable_seqscan;
344 set enable_indexscan = off;
345 SELECT MIN(name) AS "AAA" FROM srt;
351 SELECT MAX(name) AS abd FROM srt;
357 reset enable_indexscan;
358 -- Check sorting likewise
359 set enable_seqscan = off;
360 SELECT name FROM srt ORDER BY name;
370 reset enable_seqscan;
371 set enable_indexscan = off;
372 SELECT name FROM srt ORDER BY name;
382 reset enable_indexscan;
383 -- Test assignment casts.
384 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::text;
390 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::varchar;
396 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::bpchar;
402 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA';
408 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::citext;
414 -- LIKE should be case-insensitive
415 SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name;
425 SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name;
432 SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name;
442 SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name;
449 -- ~~ should be case-insensitive
450 SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name;
460 SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name;
467 SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name;
477 SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name;
484 -- ~ should be case-insensitive
485 SELECT name FROM srt WHERE name ~ '^a' ORDER BY name;
495 SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name;
503 SELECT name FROM srt WHERE name ~ '^A' ORDER BY name;
513 SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name;
521 -- SIMILAR TO should be case-insensitive.
522 SELECT name FROM srt WHERE name SIMILAR TO '%a.*';
529 SELECT name FROM srt WHERE name SIMILAR TO '%A.*';
537 SELECT true::citext = 'true' AS t;
543 SELECT 'true'::citext::boolean = true AS t;
549 SELECT 4::citext = '4' AS t;
555 SELECT 4::int4::citext = '4' AS t;
561 SELECT '4'::citext::int4 = 4 AS t;
567 SELECT 4::integer::citext = '4' AS t;
573 SELECT '4'::citext::integer = 4 AS t;
579 SELECT 4::int8::citext = '4' AS t;
585 SELECT '4'::citext::int8 = 4 AS t;
591 SELECT 4::bigint::citext = '4' AS t;
597 SELECT '4'::citext::bigint = 4 AS t;
603 SELECT 4::int2::citext = '4' AS t;
609 SELECT '4'::citext::int2 = 4 AS t;
615 SELECT 4::smallint::citext = '4' AS t;
621 SELECT '4'::citext::smallint = 4 AS t;
627 SELECT 4.0::numeric = '4.0' AS t;
633 SELECT '4.0'::citext::numeric = 4.0 AS t;
639 SELECT 4.0::decimal = '4.0' AS t;
645 SELECT '4.0'::citext::decimal = 4.0 AS t;
651 SELECT 4.0::real = '4.0' AS t;
657 SELECT '4.0'::citext::real = 4.0 AS t;
663 SELECT 4.0::float4 = '4.0' AS t;
669 SELECT '4.0'::citext::float4 = 4.0 AS t;
675 SELECT 4.0::double precision = '4.0' AS t;
681 SELECT '4.0'::citext::double precision = 4.0 AS t;
687 SELECT 4.0::float8 = '4.0' AS t;
693 SELECT '4.0'::citext::float8 = 4.0 AS t;
699 SELECT 'foo'::name::citext = 'foo' AS t;
705 SELECT 'foo'::citext::name = 'foo'::name AS t;
711 SELECT 'f'::char::citext = 'f' AS t;
717 SELECT 'f'::citext::char = 'f'::char AS t;
723 SELECT 'f'::"char"::citext = 'f' AS t;
729 SELECT 'f'::citext::"char" = 'f'::"char" AS t;
735 SELECT '100'::money::citext = '$100.00' AS t;
741 SELECT '100'::citext::money = '100'::money AS t;
747 SELECT 'a'::char::citext = 'a' AS t;
753 SELECT 'a'::citext::char = 'a'::char AS t;
759 SELECT 'foo'::varchar::citext = 'foo' AS t;
765 SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
771 SELECT 'foo'::text::citext = 'foo' AS t;
777 SELECT 'foo'::citext::text = 'foo'::text AS t;
783 SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t;
789 SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t;
795 SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t;
801 SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
807 SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
813 SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
819 SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
825 SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
831 SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
837 SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t;
843 SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
849 SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
855 SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t;
861 SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t;
867 SELECT '04:05:06'::time::citext = '04:05:06' AS t;
873 SELECT '04:05:06'::citext::time = '04:05:06'::time AS t;
879 SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t;
885 SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t;
891 SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t;
897 SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t;
903 SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t;
909 SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t;
915 SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t;
921 SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t;
927 SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t;
933 SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t;
939 SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t;
945 SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t;
951 SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t;
957 SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t;
963 SELECT '101'::bit::citext = '101'::bit::text AS t;
969 SELECT '101'::citext::bit = '101'::text::bit AS t;
975 SELECT '101'::bit varying::citext = '101'::bit varying::text AS t;
981 SELECT '101'::citext::bit varying = '101'::text::bit varying AS t;
987 SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t;
993 SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t;
999 SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t;
1005 SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t;
1011 SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t;
1017 SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t;
1023 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
1024 SELECT 'sad'::mood::citext = 'sad' AS t;
1030 SELECT 'sad'::citext::mood = 'sad'::mood AS t;
1036 -- Assignment casts.
1037 CREATE TABLE caster (
1057 timestamp timestamp,
1058 timestamptz timestamptz,
1075 INSERT INTO caster (text) VALUES ('foo'::citext);
1076 INSERT INTO caster (citext) VALUES ('foo'::text);
1077 INSERT INTO caster (varchar) VALUES ('foo'::text);
1078 INSERT INTO caster (text) VALUES ('foo'::varchar);
1079 INSERT INTO caster (varchar) VALUES ('foo'::citext);
1080 INSERT INTO caster (citext) VALUES ('foo'::varchar);
1081 INSERT INTO caster (bpchar) VALUES ('foo'::text);
1082 INSERT INTO caster (text) VALUES ('foo'::bpchar);
1083 INSERT INTO caster (bpchar) VALUES ('foo'::citext);
1084 INSERT INTO caster (citext) VALUES ('foo'::bpchar);
1085 INSERT INTO caster (char) VALUES ('f'::text);
1086 INSERT INTO caster (text) VALUES ('f'::char);
1087 INSERT INTO caster (char) VALUES ('f'::citext);
1088 INSERT INTO caster (citext) VALUES ('f'::char);
1089 INSERT INTO caster (chr) VALUES ('f'::text);
1090 INSERT INTO caster (text) VALUES ('f'::"char");
1091 INSERT INTO caster (chr) VALUES ('f'::citext);
1092 INSERT INTO caster (citext) VALUES ('f'::"char");
1093 INSERT INTO caster (name) VALUES ('foo'::text);
1094 INSERT INTO caster (text) VALUES ('foo'::name);
1095 INSERT INTO caster (name) VALUES ('foo'::citext);
1096 INSERT INTO caster (citext) VALUES ('foo'::name);
1097 -- Cannot cast to bytea on assignment.
1098 INSERT INTO caster (bytea) VALUES ('foo'::text);
1099 ERROR: column "bytea" is of type bytea but expression is of type text
1100 LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::text);
1102 HINT: You will need to rewrite or cast the expression.
1103 INSERT INTO caster (text) VALUES ('foo'::bytea);
1104 INSERT INTO caster (bytea) VALUES ('foo'::citext);
1105 ERROR: column "bytea" is of type bytea but expression is of type citext
1106 LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::citext);
1108 HINT: You will need to rewrite or cast the expression.
1109 INSERT INTO caster (citext) VALUES ('foo'::bytea);
1110 -- Cannot cast to boolean on assignment.
1111 INSERT INTO caster (boolean) VALUES ('t'::text);
1112 ERROR: column "boolean" is of type boolean but expression is of type text
1113 LINE 1: INSERT INTO caster (boolean) VALUES ('t'::text);
1115 HINT: You will need to rewrite or cast the expression.
1116 INSERT INTO caster (text) VALUES ('t'::boolean);
1117 INSERT INTO caster (boolean) VALUES ('t'::citext);
1118 ERROR: column "boolean" is of type boolean but expression is of type citext
1119 LINE 1: INSERT INTO caster (boolean) VALUES ('t'::citext);
1121 HINT: You will need to rewrite or cast the expression.
1122 INSERT INTO caster (citext) VALUES ('t'::boolean);
1123 -- Cannot cast to float8 on assignment.
1124 INSERT INTO caster (float8) VALUES ('12.42'::text);
1125 ERROR: column "float8" is of type double precision but expression is of type text
1126 LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::text);
1128 HINT: You will need to rewrite or cast the expression.
1129 INSERT INTO caster (text) VALUES ('12.42'::float8);
1130 INSERT INTO caster (float8) VALUES ('12.42'::citext);
1131 ERROR: column "float8" is of type double precision but expression is of type citext
1132 LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::citext);
1134 HINT: You will need to rewrite or cast the expression.
1135 INSERT INTO caster (citext) VALUES ('12.42'::float8);
1136 -- Cannot cast to float4 on assignment.
1137 INSERT INTO caster (float4) VALUES ('12.42'::text);
1138 ERROR: column "float4" is of type real but expression is of type text
1139 LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::text);
1141 HINT: You will need to rewrite or cast the expression.
1142 INSERT INTO caster (text) VALUES ('12.42'::float4);
1143 INSERT INTO caster (float4) VALUES ('12.42'::citext);
1144 ERROR: column "float4" is of type real but expression is of type citext
1145 LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::citext);
1147 HINT: You will need to rewrite or cast the expression.
1148 INSERT INTO caster (citext) VALUES ('12.42'::float4);
1149 -- Cannot cast to numeric on assignment.
1150 INSERT INTO caster (numeric) VALUES ('12.42'::text);
1151 ERROR: column "numeric" is of type numeric but expression is of type text
1152 LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::text);
1154 HINT: You will need to rewrite or cast the expression.
1155 INSERT INTO caster (text) VALUES ('12.42'::numeric);
1156 INSERT INTO caster (numeric) VALUES ('12.42'::citext);
1157 ERROR: column "numeric" is of type numeric but expression is of type citext
1158 LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::citext);
1160 HINT: You will need to rewrite or cast the expression.
1161 INSERT INTO caster (citext) VALUES ('12.42'::numeric);
1162 -- Cannot cast to int8 on assignment.
1163 INSERT INTO caster (int8) VALUES ('12'::text);
1164 ERROR: column "int8" is of type bigint but expression is of type text
1165 LINE 1: INSERT INTO caster (int8) VALUES ('12'::text);
1167 HINT: You will need to rewrite or cast the expression.
1168 INSERT INTO caster (text) VALUES ('12'::int8);
1169 INSERT INTO caster (int8) VALUES ('12'::citext);
1170 ERROR: column "int8" is of type bigint but expression is of type citext
1171 LINE 1: INSERT INTO caster (int8) VALUES ('12'::citext);
1173 HINT: You will need to rewrite or cast the expression.
1174 INSERT INTO caster (citext) VALUES ('12'::int8);
1175 -- Cannot cast to int4 on assignment.
1176 INSERT INTO caster (int4) VALUES ('12'::text);
1177 ERROR: column "int4" is of type integer but expression is of type text
1178 LINE 1: INSERT INTO caster (int4) VALUES ('12'::text);
1180 HINT: You will need to rewrite or cast the expression.
1181 INSERT INTO caster (text) VALUES ('12'::int4);
1182 INSERT INTO caster (int4) VALUES ('12'::citext);
1183 ERROR: column "int4" is of type integer but expression is of type citext
1184 LINE 1: INSERT INTO caster (int4) VALUES ('12'::citext);
1186 HINT: You will need to rewrite or cast the expression.
1187 INSERT INTO caster (citext) VALUES ('12'::int4);
1188 -- Cannot cast to int2 on assignment.
1189 INSERT INTO caster (int2) VALUES ('12'::text);
1190 ERROR: column "int2" is of type smallint but expression is of type text
1191 LINE 1: INSERT INTO caster (int2) VALUES ('12'::text);
1193 HINT: You will need to rewrite or cast the expression.
1194 INSERT INTO caster (text) VALUES ('12'::int2);
1195 INSERT INTO caster (int2) VALUES ('12'::citext);
1196 ERROR: column "int2" is of type smallint but expression is of type citext
1197 LINE 1: INSERT INTO caster (int2) VALUES ('12'::citext);
1199 HINT: You will need to rewrite or cast the expression.
1200 INSERT INTO caster (citext) VALUES ('12'::int2);
1201 -- Cannot cast to cidr on assignment.
1202 INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text);
1203 ERROR: column "cidr" is of type cidr but expression is of type text
1204 LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/...
1206 HINT: You will need to rewrite or cast the expression.
1207 INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr);
1208 INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext);
1209 ERROR: column "cidr" is of type cidr but expression is of type citext
1210 LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/...
1212 HINT: You will need to rewrite or cast the expression.
1213 INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr);
1214 -- Cannot cast to inet on assignment.
1215 INSERT INTO caster (inet) VALUES ('192.168.100.128'::text);
1216 ERROR: column "inet" is of type inet but expression is of type text
1217 LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'...
1219 HINT: You will need to rewrite or cast the expression.
1220 INSERT INTO caster (text) VALUES ('192.168.100.128'::inet);
1221 INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext);
1222 ERROR: column "inet" is of type inet but expression is of type citext
1223 LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'...
1225 HINT: You will need to rewrite or cast the expression.
1226 INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet);
1227 -- Cannot cast to macaddr on assignment.
1228 INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text);
1229 ERROR: column "macaddr" is of type macaddr but expression is of type text
1230 LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0...
1232 HINT: You will need to rewrite or cast the expression.
1233 INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr);
1234 INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext);
1235 ERROR: column "macaddr" is of type macaddr but expression is of type citext
1236 LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0...
1238 HINT: You will need to rewrite or cast the expression.
1239 INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr);
1240 -- Cannot cast to money on assignment.
1241 INSERT INTO caster (money) VALUES ('12'::text);
1242 ERROR: column "money" is of type money but expression is of type text
1243 LINE 1: INSERT INTO caster (money) VALUES ('12'::text);
1245 HINT: You will need to rewrite or cast the expression.
1246 INSERT INTO caster (text) VALUES ('12'::money);
1247 INSERT INTO caster (money) VALUES ('12'::citext);
1248 ERROR: column "money" is of type money but expression is of type citext
1249 LINE 1: INSERT INTO caster (money) VALUES ('12'::citext);
1251 HINT: You will need to rewrite or cast the expression.
1252 INSERT INTO caster (citext) VALUES ('12'::money);
1253 -- Cannot cast to timestamp on assignment.
1254 INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text);
1255 ERROR: column "timestamp" is of type timestamp without time zone but expression is of type text
1256 LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05...
1258 HINT: You will need to rewrite or cast the expression.
1259 INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp);
1260 INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext);
1261 ERROR: column "timestamp" is of type timestamp without time zone but expression is of type citext
1262 LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05...
1264 HINT: You will need to rewrite or cast the expression.
1265 INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp);
1266 -- Cannot cast to timestamptz on assignment.
1267 INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text);
1268 ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type text
1269 LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05...
1271 HINT: You will need to rewrite or cast the expression.
1272 INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz);
1273 INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext);
1274 ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type citext
1275 LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05...
1277 HINT: You will need to rewrite or cast the expression.
1278 INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz);
1279 -- Cannot cast to interval on assignment.
1280 INSERT INTO caster (interval) VALUES ('1 hour'::text);
1281 ERROR: column "interval" is of type interval but expression is of type text
1282 LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::text);
1284 HINT: You will need to rewrite or cast the expression.
1285 INSERT INTO caster (text) VALUES ('1 hour'::interval);
1286 INSERT INTO caster (interval) VALUES ('1 hour'::citext);
1287 ERROR: column "interval" is of type interval but expression is of type citext
1288 LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::citext)...
1290 HINT: You will need to rewrite or cast the expression.
1291 INSERT INTO caster (citext) VALUES ('1 hour'::interval);
1292 -- Cannot cast to date on assignment.
1293 INSERT INTO caster (date) VALUES ('1999-01-08'::text);
1294 ERROR: column "date" is of type date but expression is of type text
1295 LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::tex...
1297 HINT: You will need to rewrite or cast the expression.
1298 INSERT INTO caster (text) VALUES ('1999-01-08'::date);
1299 INSERT INTO caster (date) VALUES ('1999-01-08'::citext);
1300 ERROR: column "date" is of type date but expression is of type citext
1301 LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::cit...
1303 HINT: You will need to rewrite or cast the expression.
1304 INSERT INTO caster (citext) VALUES ('1999-01-08'::date);
1305 -- Cannot cast to time on assignment.
1306 INSERT INTO caster (time) VALUES ('04:05:06'::text);
1307 ERROR: column "time" is of type time without time zone but expression is of type text
1308 LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::text)...
1310 HINT: You will need to rewrite or cast the expression.
1311 INSERT INTO caster (text) VALUES ('04:05:06'::time);
1312 INSERT INTO caster (time) VALUES ('04:05:06'::citext);
1313 ERROR: column "time" is of type time without time zone but expression is of type citext
1314 LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::citex...
1316 HINT: You will need to rewrite or cast the expression.
1317 INSERT INTO caster (citext) VALUES ('04:05:06'::time);
1318 -- Cannot cast to timetz on assignment.
1319 INSERT INTO caster (timetz) VALUES ('04:05:06'::text);
1320 ERROR: column "timetz" is of type time with time zone but expression is of type text
1321 LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::text)...
1323 HINT: You will need to rewrite or cast the expression.
1324 INSERT INTO caster (text) VALUES ('04:05:06'::timetz);
1325 INSERT INTO caster (timetz) VALUES ('04:05:06'::citext);
1326 ERROR: column "timetz" is of type time with time zone but expression is of type citext
1327 LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::citex...
1329 HINT: You will need to rewrite or cast the expression.
1330 INSERT INTO caster (citext) VALUES ('04:05:06'::timetz);
1331 -- Cannot cast to point on assignment.
1332 INSERT INTO caster (point) VALUES ('( 1 , 1)'::text);
1333 ERROR: column "point" is of type point but expression is of type text
1334 LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::text)...
1336 HINT: You will need to rewrite or cast the expression.
1337 INSERT INTO caster (text) VALUES ('( 1 , 1)'::point);
1338 INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext);
1339 ERROR: column "point" is of type point but expression is of type citext
1340 LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::citex...
1342 HINT: You will need to rewrite or cast the expression.
1343 INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point);
1344 -- Cannot cast to lseg on assignment.
1345 INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text);
1346 ERROR: column "lseg" is of type lseg but expression is of type text
1347 LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ...
1349 HINT: You will need to rewrite or cast the expression.
1350 INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
1351 INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext);
1352 ERROR: column "lseg" is of type lseg but expression is of type citext
1353 LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ...
1355 HINT: You will need to rewrite or cast the expression.
1356 INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
1357 -- Cannot cast to box on assignment.
1358 INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text);
1359 ERROR: column "box" is of type box but expression is of type text
1360 LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::te...
1362 HINT: You will need to rewrite or cast the expression.
1363 INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box);
1364 INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext);
1365 ERROR: column "box" is of type box but expression is of type citext
1366 LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::ci...
1368 HINT: You will need to rewrite or cast the expression.
1369 INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box);
1370 -- Cannot cast to path on assignment.
1371 INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text);
1372 ERROR: column "path" is of type path but expression is of type text
1373 LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,...
1375 HINT: You will need to rewrite or cast the expression.
1376 INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path);
1377 INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext);
1378 ERROR: column "path" is of type path but expression is of type citext
1379 LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,...
1381 HINT: You will need to rewrite or cast the expression.
1382 INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path);
1383 -- Cannot cast to polygon on assignment.
1384 INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text);
1385 ERROR: column "polygon" is of type polygon but expression is of type text
1386 LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::...
1388 HINT: You will need to rewrite or cast the expression.
1389 INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon);
1390 INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext);
1391 ERROR: column "polygon" is of type polygon but expression is of type citext
1392 LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::...
1394 HINT: You will need to rewrite or cast the expression.
1395 INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon);
1396 -- Cannot cast to circle on assignment.
1397 INSERT INTO caster (circle) VALUES ('((0,0),2)'::text);
1398 ERROR: column "circle" is of type circle but expression is of type text
1399 LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::text...
1401 HINT: You will need to rewrite or cast the expression.
1402 INSERT INTO caster (text) VALUES ('((0,0),2)'::circle);
1403 INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext);
1404 ERROR: column "circle" is of type circle but expression is of type citext
1405 LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::cite...
1407 HINT: You will need to rewrite or cast the expression.
1408 INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle);
1409 -- Cannot cast to bit on assignment.
1410 INSERT INTO caster (bit) VALUES ('101'::text);
1411 ERROR: column "bit" is of type bit but expression is of type text
1412 LINE 1: INSERT INTO caster (bit) VALUES ('101'::text);
1414 HINT: You will need to rewrite or cast the expression.
1415 INSERT INTO caster (text) VALUES ('101'::bit);
1416 INSERT INTO caster (bit) VALUES ('101'::citext);
1417 ERROR: column "bit" is of type bit but expression is of type citext
1418 LINE 1: INSERT INTO caster (bit) VALUES ('101'::citext);
1420 HINT: You will need to rewrite or cast the expression.
1421 INSERT INTO caster (citext) VALUES ('101'::bit);
1422 -- Cannot cast to bit varying on assignment.
1423 INSERT INTO caster (bitv) VALUES ('101'::text);
1424 ERROR: column "bitv" is of type bit varying but expression is of type text
1425 LINE 1: INSERT INTO caster (bitv) VALUES ('101'::text);
1427 HINT: You will need to rewrite or cast the expression.
1428 INSERT INTO caster (text) VALUES ('101'::bit varying);
1429 INSERT INTO caster (bitv) VALUES ('101'::citext);
1430 ERROR: column "bitv" is of type bit varying but expression is of type citext
1431 LINE 1: INSERT INTO caster (bitv) VALUES ('101'::citext);
1433 HINT: You will need to rewrite or cast the expression.
1434 INSERT INTO caster (citext) VALUES ('101'::bit varying);
1435 -- Cannot cast to tsvector on assignment.
1436 INSERT INTO caster (tsvector) VALUES ('the fat cat'::text);
1437 ERROR: column "tsvector" is of type tsvector but expression is of type text
1438 LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::te...
1440 HINT: You will need to rewrite or cast the expression.
1441 INSERT INTO caster (text) VALUES ('the fat cat'::tsvector);
1442 INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext);
1443 ERROR: column "tsvector" is of type tsvector but expression is of type citext
1444 LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::ci...
1446 HINT: You will need to rewrite or cast the expression.
1447 INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector);
1448 -- Cannot cast to tsquery on assignment.
1449 INSERT INTO caster (tsquery) VALUES ('fat & rat'::text);
1450 ERROR: column "tsquery" is of type tsquery but expression is of type text
1451 LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::text...
1453 HINT: You will need to rewrite or cast the expression.
1454 INSERT INTO caster (text) VALUES ('fat & rat'::tsquery);
1455 INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext);
1456 ERROR: column "tsquery" is of type tsquery but expression is of type citext
1457 LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::cite...
1459 HINT: You will need to rewrite or cast the expression.
1460 INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery);
1461 -- Cannot cast to uuid on assignment.
1462 INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);
1463 ERROR: column "uuid" is of type uuid but expression is of type text
1464 LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e...
1466 HINT: You will need to rewrite or cast the expression.
1467 INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
1468 INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext);
1469 ERROR: column "uuid" is of type uuid but expression is of type citext
1470 LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e...
1472 HINT: You will need to rewrite or cast the expression.
1473 INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
1474 -- Table 9-5. SQL String Functions and Operators
1475 SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat;
1481 SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat;
1487 SELECT 42 || ': value'::citext ='42: value' AS int_concat;
1493 SELECT bit_length('jose'::citext) = 32 AS t;
1499 SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt;
1509 SELECT textlen( name ) = textlen( name::text ) AS t FROM srt;
1519 SELECT char_length( name ) = char_length( name::text ) AS t FROM srt;
1529 SELECT lower( name ) = lower( name::text ) AS t FROM srt;
1539 SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt;
1549 SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt;
1559 SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt;
1569 SELECT substr('alphabet'::citext, 3) = 'phabet' AS t;
1575 SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t;
1581 SELECT substring('alphabet'::citext, 3) = 'phabet' AS t;
1587 SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t;
1593 SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t;
1599 SELECT substring('Thomas'::citext from 2) = 'homas' AS t;
1605 SELECT substring('Thomas'::citext from '...$') = 'mas' AS t;
1611 SELECT substring('Thomas'::citext from '%#"o_a#"_' for '#') = 'oma' AS t;
1617 SELECT trim(' trim '::citext) = 'trim' AS t;
1623 SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t;
1629 SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
1635 SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
1641 SELECT upper( name ) = upper( name::text ) AS t FROM srt;
1651 -- Table 9-6. Other String Functions.
1652 SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
1662 SELECT btrim(' trim'::citext ) = 'trim' AS t;
1668 SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t;
1674 SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t;
1680 SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t;
1686 SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t;
1692 -- chr() takes an int and returns text.
1693 -- convert() and convert_from take bytea and return text.
1694 SELECT convert_to( name, 'ISO-8859-1' ) = convert_to( name::text, 'ISO-8859-1' ) AS t FROM srt;
1704 SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t;
1710 -- encode() takes bytea and returns text.
1711 SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t;
1717 SELECT length( name ) = length( name::text ) AS t FROM srt;
1727 SELECT lpad('hi'::citext, 5 ) = ' hi' AS t;
1733 SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t;
1739 SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t;
1745 SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t;
1751 SELECT ltrim(' trim'::citext ) = 'trim' AS t;
1757 SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t;
1763 SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t;
1769 SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t;
1775 SELECT md5( name ) = md5( name::text ) AS t FROM srt;
1785 -- pg_client_encoding() takes no args and returns name.
1786 SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
1796 SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
1806 SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
1812 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
1818 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
1824 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
1830 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
1836 SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
1842 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
1848 -- c forces case-sensitive
1849 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "null";
1855 SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t;
1861 SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t;
1867 SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t;
1873 SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t;
1879 -- c forces case-sensitive
1880 SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t;
1886 SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t;
1892 SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t;
1898 SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
1904 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
1910 SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t;
1916 SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
1922 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
1928 -- c forces case-sensitive
1929 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
1935 SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words;
1942 SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words;
1949 SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words;
1956 SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words;
1963 -- c forces case-sensitive
1964 SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word;
1970 SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
1976 SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
1982 SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
1988 SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t;
1994 SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
2000 SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
2006 SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
2012 SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
2018 SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
2024 SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
2030 SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t;
2036 SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t;
2042 SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t;
2048 SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t;
2054 SELECT rtrim('trim '::citext ) = 'trim' AS t;
2060 SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t;
2066 SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t;
2072 SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t;
2078 SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
2084 SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
2090 SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
2096 SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
2102 SELECT strpos('high'::citext, 'ig' ) = 2 AS t;
2108 SELECT strpos('high', 'ig'::citext) = 2 AS t;
2114 SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t;
2120 SELECT strpos('high'::citext, 'IG' ) = 2 AS t;
2126 SELECT strpos('high', 'IG'::citext) = 2 AS t;
2132 SELECT strpos('high'::citext, 'IG'::citext) = 2 AS t;
2138 -- to_ascii() does not support UTF-8.
2139 -- to_hex() takes a numeric argument.
2140 SELECT substr('alphabet', 3, 2) = 'ph' AS t;
2146 SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
2152 SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
2158 SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
2164 SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
2170 -- Table 9-20. Formatting Functions
2171 SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
2172 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
2178 SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY')
2179 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
2185 SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext)
2186 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
2192 SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext)
2193 = to_number('12,454.8-', '99G999D9S') AS t;
2199 SELECT to_number('12,454.8-'::citext, '99G999D9S')
2200 = to_number('12,454.8-', '99G999D9S') AS t;
2206 SELECT to_number('12,454.8-', '99G999D9S'::citext)
2207 = to_number('12,454.8-', '99G999D9S') AS t;
2213 SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
2214 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
2220 SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY')
2221 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
2227 SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext)
2228 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
2234 -- Try assigning function results to a column.
2235 SELECT COUNT(*) = 8::bigint AS t FROM try;
2242 VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ),
2243 ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timetamptz
2244 ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ),
2245 ( to_char( current_date, '999') ),
2246 ( to_char( 125::int, '999') ),
2247 ( to_char( 127::int4, '999') ),
2248 ( to_char( 126::int8, '999') ),
2249 ( to_char( 128.8::real, '999D9') ),
2250 ( to_char( 125.7::float4, '999D9') ),
2251 ( to_char( 125.9::float8, '999D9') ),
2252 ( to_char( -125.8::numeric, '999D99S') );
2253 SELECT COUNT(*) = 19::bigint AS t FROM try;
2259 SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
2269 SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;
2279 -- Ensure correct behavior for citext with materialized views.
2280 CREATE TABLE citext_table (
2281 id serial primary key,
2284 INSERT INTO citext_table (name)
2285 VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
2286 CREATE MATERIALIZED VIEW citext_matview AS
2287 SELECT * FROM citext_table;
2288 CREATE UNIQUE INDEX citext_matview_id
2289 ON citext_matview (id);
2291 FROM citext_matview m
2292 FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
2293 WHERE t.id IS NULL OR m.id IS NULL;
2294 id | name | id | name
2295 ----+------+----+------
2298 UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
2300 FROM citext_matview m
2301 FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
2302 WHERE t.id IS NULL OR m.id IS NULL;
2303 id | name | id | name
2304 ----+------+----+------
2309 REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
2310 SELECT * FROM citext_matview ORDER BY id;