contrib/citext/sql/citext.sql

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