2 -- Test citext datatype
5 CREATE EXTENSION citext;
7 -- Test the operators and indexing functions
10 SELECT 'a'::citext = 'a'::citext AS t;
11 SELECT 'a'::citext = 'A'::citext AS t;
12 SELECT 'a'::citext = 'A'::text AS f; -- text wins the discussion
13 SELECT 'a'::citext = 'b'::citext AS f;
14 SELECT 'a'::citext = 'ab'::citext AS f;
15 SELECT 'a'::citext <> 'ab'::citext AS t;
17 -- Multibyte sanity tests. Uncomment to run.
18 -- SELECT 'À'::citext = 'À'::citext AS t;
19 -- SELECT 'À'::citext = 'à'::citext AS t;
20 -- SELECT 'À'::text = 'à'::text AS f; -- text wins.
21 -- SELECT 'À'::citext <> 'B'::citext AS t;
23 -- Test combining characters making up canonically equivalent strings.
24 -- SELECT 'Ä'::text <> 'Ä'::text AS t;
25 -- SELECT 'Ä'::citext <> 'Ä'::citext AS t;
27 -- Test the Turkish dotted I. The lowercase is a single byte while the
28 -- uppercase is multibyte. This is why the comparison code can't be optimized
29 -- to compare string lengths.
30 -- SELECT 'i'::citext = 'İ'::citext AS t;
33 -- SELECT 'láska'::citext <> 'laská'::citext AS t;
35 -- SELECT 'Ask Bjørn Hansen'::citext = 'Ask Bjørn Hansen'::citext AS t;
36 -- SELECT 'Ask Bjørn Hansen'::citext = 'ASK BJØRN HANSEN'::citext AS t;
37 -- SELECT 'Ask Bjørn Hansen'::citext <> 'Ask Bjorn Hansen'::citext AS t;
38 -- SELECT 'Ask Bjørn Hansen'::citext <> 'ASK BJORN HANSEN'::citext AS t;
39 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS zero;
40 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) AS zero;
41 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) AS zero;
42 -- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) AS positive;
43 -- SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS negative;
46 SELECT 'B'::citext > 'a'::citext AS t;
47 SELECT 'b'::citext > 'A'::citext AS t;
48 SELECT 'B'::citext > 'b'::citext AS f;
49 SELECT 'B'::citext >= 'b'::citext AS t;
52 SELECT 'a'::citext < 'B'::citext AS t;
53 SELECT 'a'::citext <= 'B'::citext AS t;
55 -- Test implicit casting. citext casts to text, but not vice-versa.
56 SELECT 'a'::citext = 'a'::text AS t;
57 SELECT 'A'::text <> 'a'::citext AS t;
59 SELECT 'B'::citext < 'a'::text AS t; -- text wins.
60 SELECT 'B'::citext <= 'a'::text AS t; -- text wins.
62 SELECT 'a'::citext > 'B'::text AS t; -- text wins.
63 SELECT 'a'::citext >= 'B'::text AS t; -- text wins.
65 -- Test implicit casting. citext casts to varchar, but not vice-versa.
66 SELECT 'a'::citext = 'a'::varchar AS t;
67 SELECT 'A'::varchar <> 'a'::citext AS t;
69 SELECT 'B'::citext < 'a'::varchar AS t; -- varchar wins.
70 SELECT 'B'::citext <= 'a'::varchar AS t; -- varchar wins.
72 SELECT 'a'::citext > 'B'::varchar AS t; -- varchar wins.
73 SELECT 'a'::citext >= 'B'::varchar AS t; -- varchar wins.
75 -- A couple of longer examlpes to ensure that we don't get any issues with bad
76 -- conversions to char[] in the c code. Yes, I did do this.
78 SELECT 'aardvark'::citext = 'aardvark'::citext AS t;
79 SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
81 -- Check the citext_cmp() function explicitly.
82 SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
83 SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
84 SELECT citext_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
85 SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true;
87 -- Do some tests using a table and index.
89 CREATE TEMP TABLE try (
90 name citext PRIMARY KEY
93 INSERT INTO try (name)
94 VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');
96 SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â';
97 SELECT name, 'a' = name AS t FROM try where name = 'a';
98 SELECT name, 'A' = name AS "eq_A" FROM try WHERE name <> 'â';
99 SELECT name, 'A' = name AS t FROM try where name = 'A';
100 SELECT name, 'A' = name AS t FROM try where name = 'A';
102 -- expected failures on duplicate key
103 INSERT INTO try (name) VALUES ('a');
104 INSERT INTO try (name) VALUES ('A');
105 INSERT INTO try (name) VALUES ('aB');
107 -- Make sure that citext_smaller() and citext_lager() work properly.
108 SELECT citext_smaller( 'aa'::citext, 'ab'::citext ) = 'aa' AS t;
109 SELECT citext_smaller( 'AAAA'::citext, 'bbbb'::citext ) = 'AAAA' AS t;
110 SELECT citext_smaller( 'aardvark'::citext, 'Aaba'::citext ) = 'Aaba' AS t;
111 SELECT citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ) = 'AARDVARK' AS t;
113 SELECT citext_larger( 'aa'::citext, 'ab'::citext ) = 'ab' AS t;
114 SELECT citext_larger( 'AAAA'::citext, 'bbbb'::citext ) = 'bbbb' AS t;
115 SELECT citext_larger( 'aardvark'::citext, 'Aaba'::citext ) = 'aardvark' AS t;
117 -- Test aggregate functions and sort ordering
119 CREATE TEMP TABLE srt (
123 INSERT INTO srt (name)
130 CREATE INDEX srt_name ON srt (name);
132 -- Check the min() and max() aggregates, with and without index.
133 set enable_seqscan = off;
134 SELECT MIN(name) AS "AAA" FROM srt;
135 SELECT MAX(name) AS abd FROM srt;
136 reset enable_seqscan;
137 set enable_indexscan = off;
138 SELECT MIN(name) AS "AAA" FROM srt;
139 SELECT MAX(name) AS abd FROM srt;
140 reset enable_indexscan;
142 -- Check sorting likewise
143 set enable_seqscan = off;
144 SELECT name FROM srt ORDER BY name;
145 reset enable_seqscan;
146 set enable_indexscan = off;
147 SELECT name FROM srt ORDER BY name;
148 reset enable_indexscan;
150 -- Test assignment casts.
151 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::text;
152 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::varchar;
153 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::bpchar;
154 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA';
155 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::citext;
157 -- LIKE should be case-insensitive
158 SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name;
159 SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name;
160 SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name;
161 SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name;
163 -- ~~ should be case-insensitive
164 SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name;
165 SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name;
166 SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name;
167 SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name;
169 -- ~ should be case-insensitive
170 SELECT name FROM srt WHERE name ~ '^a' ORDER BY name;
171 SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name;
172 SELECT name FROM srt WHERE name ~ '^A' ORDER BY name;
173 SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name;
175 -- SIMILAR TO should be case-insensitive.
176 SELECT name FROM srt WHERE name SIMILAR TO '%a.*';
177 SELECT name FROM srt WHERE name SIMILAR TO '%A.*';
180 SELECT true::citext = 'true' AS t;
181 SELECT 'true'::citext::boolean = true AS t;
183 SELECT 4::citext = '4' AS t;
184 SELECT 4::int4::citext = '4' AS t;
185 SELECT '4'::citext::int4 = 4 AS t;
186 SELECT 4::integer::citext = '4' AS t;
187 SELECT '4'::citext::integer = 4 AS t;
189 SELECT 4::int8::citext = '4' AS t;
190 SELECT '4'::citext::int8 = 4 AS t;
191 SELECT 4::bigint::citext = '4' AS t;
192 SELECT '4'::citext::bigint = 4 AS t;
194 SELECT 4::int2::citext = '4' AS t;
195 SELECT '4'::citext::int2 = 4 AS t;
196 SELECT 4::smallint::citext = '4' AS t;
197 SELECT '4'::citext::smallint = 4 AS t;
199 SELECT 4.0::numeric = '4.0' AS t;
200 SELECT '4.0'::citext::numeric = 4.0 AS t;
201 SELECT 4.0::decimal = '4.0' AS t;
202 SELECT '4.0'::citext::decimal = 4.0 AS t;
204 SELECT 4.0::real = '4.0' AS t;
205 SELECT '4.0'::citext::real = 4.0 AS t;
206 SELECT 4.0::float4 = '4.0' AS t;
207 SELECT '4.0'::citext::float4 = 4.0 AS t;
209 SELECT 4.0::double precision = '4.0' AS t;
210 SELECT '4.0'::citext::double precision = 4.0 AS t;
211 SELECT 4.0::float8 = '4.0' AS t;
212 SELECT '4.0'::citext::float8 = 4.0 AS t;
214 SELECT 'foo'::name::citext = 'foo' AS t;
215 SELECT 'foo'::citext::name = 'foo'::name AS t;
217 SELECT 'f'::char::citext = 'f' AS t;
218 SELECT 'f'::citext::char = 'f'::char AS t;
220 SELECT 'f'::"char"::citext = 'f' AS t;
221 SELECT 'f'::citext::"char" = 'f'::"char" AS t;
223 SELECT '100'::money::citext = '$100.00' AS t;
224 SELECT '100'::citext::money = '100'::money AS t;
226 SELECT 'a'::char::citext = 'a' AS t;
227 SELECT 'a'::citext::char = 'a'::char AS t;
229 SELECT 'foo'::varchar::citext = 'foo' AS t;
230 SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
232 SELECT 'foo'::text::citext = 'foo' AS t;
233 SELECT 'foo'::citext::text = 'foo'::text AS t;
235 SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t;
236 SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t;
238 SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t;
239 SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
241 SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
242 SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
244 SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
245 SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
246 SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
247 SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t;
249 SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
250 SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
252 SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t;
253 SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t;
255 SELECT '04:05:06'::time::citext = '04:05:06' AS t;
256 SELECT '04:05:06'::citext::time = '04:05:06'::time AS t;
257 SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t;
258 SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t;
260 SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t;
261 SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t;
262 SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t;
263 SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t;
264 SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t;
265 SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t;
267 SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t;
268 SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t;
270 SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t;
271 SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t;
273 SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t;
274 SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t;
276 SELECT '101'::bit::citext = '101'::bit::text AS t;
277 SELECT '101'::citext::bit = '101'::text::bit AS t;
278 SELECT '101'::bit varying::citext = '101'::bit varying::text AS t;
279 SELECT '101'::citext::bit varying = '101'::text::bit varying AS t;
280 SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t;
281 SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t;
282 SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t;
283 SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t;
284 SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t;
285 SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t;
287 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
288 SELECT 'sad'::mood::citext = 'sad' AS t;
289 SELECT 'sad'::citext::mood = 'sad'::mood AS t;
292 CREATE TABLE caster (
313 timestamptz timestamptz,
331 INSERT INTO caster (text) VALUES ('foo'::citext);
332 INSERT INTO caster (citext) VALUES ('foo'::text);
334 INSERT INTO caster (varchar) VALUES ('foo'::text);
335 INSERT INTO caster (text) VALUES ('foo'::varchar);
336 INSERT INTO caster (varchar) VALUES ('foo'::citext);
337 INSERT INTO caster (citext) VALUES ('foo'::varchar);
339 INSERT INTO caster (bpchar) VALUES ('foo'::text);
340 INSERT INTO caster (text) VALUES ('foo'::bpchar);
341 INSERT INTO caster (bpchar) VALUES ('foo'::citext);
342 INSERT INTO caster (citext) VALUES ('foo'::bpchar);
344 INSERT INTO caster (char) VALUES ('f'::text);
345 INSERT INTO caster (text) VALUES ('f'::char);
346 INSERT INTO caster (char) VALUES ('f'::citext);
347 INSERT INTO caster (citext) VALUES ('f'::char);
349 INSERT INTO caster (chr) VALUES ('f'::text);
350 INSERT INTO caster (text) VALUES ('f'::"char");
351 INSERT INTO caster (chr) VALUES ('f'::citext);
352 INSERT INTO caster (citext) VALUES ('f'::"char");
354 INSERT INTO caster (name) VALUES ('foo'::text);
355 INSERT INTO caster (text) VALUES ('foo'::name);
356 INSERT INTO caster (name) VALUES ('foo'::citext);
357 INSERT INTO caster (citext) VALUES ('foo'::name);
359 -- Cannot cast to bytea on assignment.
360 INSERT INTO caster (bytea) VALUES ('foo'::text);
361 INSERT INTO caster (text) VALUES ('foo'::bytea);
362 INSERT INTO caster (bytea) VALUES ('foo'::citext);
363 INSERT INTO caster (citext) VALUES ('foo'::bytea);
365 -- Cannot cast to boolean on assignment.
366 INSERT INTO caster (boolean) VALUES ('t'::text);
367 INSERT INTO caster (text) VALUES ('t'::boolean);
368 INSERT INTO caster (boolean) VALUES ('t'::citext);
369 INSERT INTO caster (citext) VALUES ('t'::boolean);
371 -- Cannot cast to float8 on assignment.
372 INSERT INTO caster (float8) VALUES ('12.42'::text);
373 INSERT INTO caster (text) VALUES ('12.42'::float8);
374 INSERT INTO caster (float8) VALUES ('12.42'::citext);
375 INSERT INTO caster (citext) VALUES ('12.42'::float8);
377 -- Cannot cast to float4 on assignment.
378 INSERT INTO caster (float4) VALUES ('12.42'::text);
379 INSERT INTO caster (text) VALUES ('12.42'::float4);
380 INSERT INTO caster (float4) VALUES ('12.42'::citext);
381 INSERT INTO caster (citext) VALUES ('12.42'::float4);
383 -- Cannot cast to numeric on assignment.
384 INSERT INTO caster (numeric) VALUES ('12.42'::text);
385 INSERT INTO caster (text) VALUES ('12.42'::numeric);
386 INSERT INTO caster (numeric) VALUES ('12.42'::citext);
387 INSERT INTO caster (citext) VALUES ('12.42'::numeric);
389 -- Cannot cast to int8 on assignment.
390 INSERT INTO caster (int8) VALUES ('12'::text);
391 INSERT INTO caster (text) VALUES ('12'::int8);
392 INSERT INTO caster (int8) VALUES ('12'::citext);
393 INSERT INTO caster (citext) VALUES ('12'::int8);
395 -- Cannot cast to int4 on assignment.
396 INSERT INTO caster (int4) VALUES ('12'::text);
397 INSERT INTO caster (text) VALUES ('12'::int4);
398 INSERT INTO caster (int4) VALUES ('12'::citext);
399 INSERT INTO caster (citext) VALUES ('12'::int4);
401 -- Cannot cast to int2 on assignment.
402 INSERT INTO caster (int2) VALUES ('12'::text);
403 INSERT INTO caster (text) VALUES ('12'::int2);
404 INSERT INTO caster (int2) VALUES ('12'::citext);
405 INSERT INTO caster (citext) VALUES ('12'::int2);
407 -- Cannot cast to cidr on assignment.
408 INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text);
409 INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr);
410 INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext);
411 INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr);
413 -- Cannot cast to inet on assignment.
414 INSERT INTO caster (inet) VALUES ('192.168.100.128'::text);
415 INSERT INTO caster (text) VALUES ('192.168.100.128'::inet);
416 INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext);
417 INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet);
419 -- Cannot cast to macaddr on assignment.
420 INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text);
421 INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr);
422 INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext);
423 INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr);
425 -- Cannot cast to money on assignment.
426 INSERT INTO caster (money) VALUES ('12'::text);
427 INSERT INTO caster (text) VALUES ('12'::money);
428 INSERT INTO caster (money) VALUES ('12'::citext);
429 INSERT INTO caster (citext) VALUES ('12'::money);
431 -- Cannot cast to timestamp on assignment.
432 INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text);
433 INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp);
434 INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext);
435 INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp);
437 -- Cannot cast to timestamptz on assignment.
438 INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text);
439 INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz);
440 INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext);
441 INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz);
443 -- Cannot cast to interval on assignment.
444 INSERT INTO caster (interval) VALUES ('1 hour'::text);
445 INSERT INTO caster (text) VALUES ('1 hour'::interval);
446 INSERT INTO caster (interval) VALUES ('1 hour'::citext);
447 INSERT INTO caster (citext) VALUES ('1 hour'::interval);
449 -- Cannot cast to date on assignment.
450 INSERT INTO caster (date) VALUES ('1999-01-08'::text);
451 INSERT INTO caster (text) VALUES ('1999-01-08'::date);
452 INSERT INTO caster (date) VALUES ('1999-01-08'::citext);
453 INSERT INTO caster (citext) VALUES ('1999-01-08'::date);
455 -- Cannot cast to time on assignment.
456 INSERT INTO caster (time) VALUES ('04:05:06'::text);
457 INSERT INTO caster (text) VALUES ('04:05:06'::time);
458 INSERT INTO caster (time) VALUES ('04:05:06'::citext);
459 INSERT INTO caster (citext) VALUES ('04:05:06'::time);
461 -- Cannot cast to timetz on assignment.
462 INSERT INTO caster (timetz) VALUES ('04:05:06'::text);
463 INSERT INTO caster (text) VALUES ('04:05:06'::timetz);
464 INSERT INTO caster (timetz) VALUES ('04:05:06'::citext);
465 INSERT INTO caster (citext) VALUES ('04:05:06'::timetz);
467 -- Cannot cast to point on assignment.
468 INSERT INTO caster (point) VALUES ('( 1 , 1)'::text);
469 INSERT INTO caster (text) VALUES ('( 1 , 1)'::point);
470 INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext);
471 INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point);
473 -- Cannot cast to lseg on assignment.
474 INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text);
475 INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
476 INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext);
477 INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
479 -- Cannot cast to box on assignment.
480 INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text);
481 INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box);
482 INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext);
483 INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box);
485 -- Cannot cast to path on assignment.
486 INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text);
487 INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path);
488 INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext);
489 INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path);
491 -- Cannot cast to polygon on assignment.
492 INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text);
493 INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon);
494 INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext);
495 INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon);
497 -- Cannot cast to circle on assignment.
498 INSERT INTO caster (circle) VALUES ('((0,0),2)'::text);
499 INSERT INTO caster (text) VALUES ('((0,0),2)'::circle);
500 INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext);
501 INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle);
503 -- Cannot cast to bit on assignment.
504 INSERT INTO caster (bit) VALUES ('101'::text);
505 INSERT INTO caster (text) VALUES ('101'::bit);
506 INSERT INTO caster (bit) VALUES ('101'::citext);
507 INSERT INTO caster (citext) VALUES ('101'::bit);
509 -- Cannot cast to bit varying on assignment.
510 INSERT INTO caster (bitv) VALUES ('101'::text);
511 INSERT INTO caster (text) VALUES ('101'::bit varying);
512 INSERT INTO caster (bitv) VALUES ('101'::citext);
513 INSERT INTO caster (citext) VALUES ('101'::bit varying);
515 -- Cannot cast to tsvector on assignment.
516 INSERT INTO caster (tsvector) VALUES ('the fat cat'::text);
517 INSERT INTO caster (text) VALUES ('the fat cat'::tsvector);
518 INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext);
519 INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector);
521 -- Cannot cast to tsquery on assignment.
522 INSERT INTO caster (tsquery) VALUES ('fat & rat'::text);
523 INSERT INTO caster (text) VALUES ('fat & rat'::tsquery);
524 INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext);
525 INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery);
527 -- Cannot cast to uuid on assignment.
528 INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);
529 INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
530 INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext);
531 INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
533 -- Table 9-5. SQL String Functions and Operators
534 SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat;
535 SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat;
536 SELECT 42 || ': value'::citext ='42: value' AS int_concat;
537 SELECT bit_length('jose'::citext) = 32 AS t;
538 SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt;
539 SELECT textlen( name ) = textlen( name::text ) AS t FROM srt;
540 SELECT char_length( name ) = char_length( name::text ) AS t FROM srt;
541 SELECT lower( name ) = lower( name::text ) AS t FROM srt;
542 SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt;
543 SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt;
544 SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt;
546 SELECT substr('alphabet'::citext, 3) = 'phabet' AS t;
547 SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t;
549 SELECT substring('alphabet'::citext, 3) = 'phabet' AS t;
550 SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t;
551 SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t;
552 SELECT substring('Thomas'::citext from 2) = 'homas' AS t;
553 SELECT substring('Thomas'::citext from '...$') = 'mas' AS t;
554 SELECT substring('Thomas'::citext from '%#"o_a#"_' for '#') = 'oma' AS t;
556 SELECT trim(' trim '::citext) = 'trim' AS t;
557 SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t;
558 SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
559 SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t;
561 SELECT upper( name ) = upper( name::text ) AS t FROM srt;
563 -- Table 9-6. Other String Functions.
564 SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
566 SELECT btrim(' trim'::citext ) = 'trim' AS t;
567 SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t;
568 SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t;
569 SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t;
570 SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t;
572 -- chr() takes an int and returns text.
573 -- convert() and convert_from take bytea and return text.
575 SELECT convert_to( name, 'ISO-8859-1' ) = convert_to( name::text, 'ISO-8859-1' ) AS t FROM srt;
576 SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t;
577 -- encode() takes bytea and returns text.
578 SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t;
579 SELECT length( name ) = length( name::text ) AS t FROM srt;
581 SELECT lpad('hi'::citext, 5 ) = ' hi' AS t;
582 SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t;
583 SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t;
584 SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t;
586 SELECT ltrim(' trim'::citext ) = 'trim' AS t;
587 SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t;
588 SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t;
589 SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t;
591 SELECT md5( name ) = md5( name::text ) AS t FROM srt;
592 -- pg_client_encoding() takes no args and returns name.
593 SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
594 SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
596 SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
597 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
598 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
599 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
600 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
601 SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
602 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
603 -- c forces case-sensitive
604 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "null";
606 SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t;
607 SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t;
608 SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t;
609 SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t;
610 -- c forces case-sensitive
611 SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t;
613 SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t;
614 SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t;
615 SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
616 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
617 SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t;
618 SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
619 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
621 -- c forces case-sensitive
622 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
624 SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words;
625 SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words;
626 SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words;
627 SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words;
628 -- c forces case-sensitive
629 SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word;
631 SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
633 SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
634 SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
635 SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t;
636 SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
637 SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
638 SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
639 SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
640 SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
641 SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
643 SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t;
644 SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t;
645 SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t;
646 SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t;
648 SELECT rtrim('trim '::citext ) = 'trim' AS t;
649 SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t;
650 SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t;
651 SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t;
653 SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
654 SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
655 SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
656 SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
658 SELECT strpos('high'::citext, 'ig' ) = 2 AS t;
659 SELECT strpos('high', 'ig'::citext) = 2 AS t;
660 SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t;
661 SELECT strpos('high'::citext, 'IG' ) = 2 AS t;
662 SELECT strpos('high', 'IG'::citext) = 2 AS t;
663 SELECT strpos('high'::citext, 'IG'::citext) = 2 AS t;
665 -- to_ascii() does not support UTF-8.
666 -- to_hex() takes a numeric argument.
667 SELECT substr('alphabet', 3, 2) = 'ph' AS t;
668 SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
669 SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
670 SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
671 SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
673 -- Table 9-20. Formatting Functions
674 SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
675 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
676 SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY')
677 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
678 SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext)
679 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t;
681 SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext)
682 = to_number('12,454.8-', '99G999D9S') AS t;
683 SELECT to_number('12,454.8-'::citext, '99G999D9S')
684 = to_number('12,454.8-', '99G999D9S') AS t;
685 SELECT to_number('12,454.8-', '99G999D9S'::citext)
686 = to_number('12,454.8-', '99G999D9S') AS t;
688 SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
689 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
690 SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY')
691 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
692 SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext)
693 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t;
695 -- Try assigning function results to a column.
696 SELECT COUNT(*) = 8::bigint AS t FROM try;
698 VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ),
699 ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timetamptz
700 ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ),
701 ( to_char( current_date, '999') ),
702 ( to_char( 125::int, '999') ),
703 ( to_char( 127::int4, '999') ),
704 ( to_char( 126::int8, '999') ),
705 ( to_char( 128.8::real, '999D9') ),
706 ( to_char( 125.7::float4, '999D9') ),
707 ( to_char( 125.9::float8, '999D9') ),
708 ( to_char( -125.8::numeric, '999D99S') );
710 SELECT COUNT(*) = 19::bigint AS t FROM try;
712 SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
713 SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;
715 -- Ensure correct behavior for citext with materialized views.
716 CREATE TABLE citext_table (
717 id serial primary key,
720 INSERT INTO citext_table (name)
721 VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
722 CREATE MATERIALIZED VIEW citext_matview AS
723 SELECT * FROM citext_table;
724 CREATE UNIQUE INDEX citext_matview_id
725 ON citext_matview (id);
727 FROM citext_matview m
728 FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
729 WHERE t.id IS NULL OR m.id IS NULL;
730 UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
732 FROM citext_matview m
733 FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
734 WHERE t.id IS NULL OR m.id IS NULL;
735 REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
736 SELECT * FROM citext_matview ORDER BY id;