granicus.if.org Git - postgresql/blob - contrib/citext/sql/citext.sql

   1 --
   2 --  Test citext datatype
   3 --
   4
   5 CREATE EXTENSION citext;
   6
   7 -- Test the operators and indexing functions
   8
   9 -- Test = and <>.
  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;
  16
  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;
  22
  23 -- Test combining characters making up canonically equivalent strings.
  24 -- SELECT 'Ä'::text   <> 'Ä'::text   AS t;
  25 -- SELECT 'Ä'::citext <> 'Ä'::citext AS t;
  26
  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;
  31
  32 -- Regression.
  33 -- SELECT 'láska'::citext <> 'laská'::citext AS t;
  34
  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;
  44
  45 -- Test > and >=
  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;
  50
  51 -- Test < and <=
  52 SELECT 'a'::citext <  'B'::citext AS t;
  53 SELECT 'a'::citext <= 'B'::citext AS t;
  54
  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;
  58
  59 SELECT 'B'::citext <  'a'::text AS t;  -- text wins.
  60 SELECT 'B'::citext <= 'a'::text AS t;  -- text wins.
  61
  62 SELECT 'a'::citext >  'B'::text AS t;  -- text wins.
  63 SELECT 'a'::citext >= 'B'::text AS t;  -- text wins.
  64
  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;
  68
  69 SELECT 'B'::citext <  'a'::varchar AS t;  -- varchar wins.
  70 SELECT 'B'::citext <= 'a'::varchar AS t;  -- varchar wins.
  71
  72 SELECT 'a'::citext >  'B'::varchar AS t;  -- varchar wins.
  73 SELECT 'a'::citext >= 'B'::varchar AS t;  -- varchar wins.
  74
  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.
  77
  78 SELECT 'aardvark'::citext = 'aardvark'::citext AS t;
  79 SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
  80
  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;
  86
  87 -- Do some tests using a table and index.
  88
  89 CREATE TEMP TABLE try (
  90    name citext PRIMARY KEY
  91 );
  92
  93 INSERT INTO try (name)
  94 VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');
  95
  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';
 101
 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');
 106
 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;
 112
 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;
 116
 117 -- Test aggregate functions and sort ordering
 118
 119 CREATE TEMP TABLE srt (
 120    name CITEXT
 121 );
 122
 123 INSERT INTO srt (name)
 124 VALUES ('aardvark'),
 125        ('AAA'),
 126        ('aba'),
 127        ('ABC'),
 128        ('abd');
 129
 130 CREATE INDEX srt_name ON srt (name);
 131
 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;
 141
 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;
 149
 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;
 156
 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;
 162
 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;
 168
 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;
 174
 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.*';
 178
 179 -- Explicit casts.
 180 SELECT true::citext = 'true' AS t;
 181 SELECT 'true'::citext::boolean = true AS t;
 182
 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;
 188
 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;
 193
 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;
 198
 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;
 203
 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;
 208
 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;
 213
 214 SELECT 'foo'::name::citext = 'foo' AS t;
 215 SELECT 'foo'::citext::name = 'foo'::name AS t;
 216
 217 SELECT 'f'::char::citext = 'f' AS t;
 218 SELECT 'f'::citext::char = 'f'::char AS t;
 219
 220 SELECT 'f'::"char"::citext = 'f' AS t;
 221 SELECT 'f'::citext::"char" = 'f'::"char" AS t;
 222
 223 SELECT '100'::money::citext = '$100.00' AS t;
 224 SELECT '100'::citext::money = '100'::money AS t;
 225
 226 SELECT 'a'::char::citext = 'a' AS t;
 227 SELECT 'a'::citext::char = 'a'::char AS t;
 228
 229 SELECT 'foo'::varchar::citext = 'foo' AS t;
 230 SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
 231
 232 SELECT 'foo'::text::citext = 'foo' AS t;
 233 SELECT 'foo'::citext::text = 'foo'::text AS t;
 234
 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;
 237
 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;
 240
 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;
 243
 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;
 248
 249 SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
 250 SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
 251
 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;
 254
 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;
 259
 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;
 266
 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;
 269
 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;
 272
 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;
 275
 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;
 286
 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;
 290
 291 -- Assignment casts.
 292 CREATE TABLE caster (
 293     citext      citext,
 294     text        text,
 295     varchar     varchar,
 296     bpchar      bpchar,
 297     char        char,
 298     chr         "char",
 299     name        name,
 300     bytea       bytea,
 301     boolean     boolean,
 302     float4      float4,
 303     float8      float8,
 304     numeric     numeric,
 305     int8        int8,
 306     int4        int4,
 307     int2        int2,
 308     cidr        cidr,
 309     inet        inet,
 310     macaddr     macaddr,
 311     money       money,
 312     timestamp   timestamp,
 313     timestamptz timestamptz,
 314     interval    interval,
 315     date        date,
 316     time        time,
 317     timetz      timetz,
 318     point       point,
 319     lseg        lseg,
 320     box         box,
 321     path        path,
 322     polygon     polygon,
 323     circle      circle,
 324     bit         bit,
 325     bitv        bit varying,
 326     tsvector    tsvector,
 327     tsquery     tsquery,
 328     uuid        uuid
 329 );
 330
 331 INSERT INTO caster (text)          VALUES ('foo'::citext);
 332 INSERT INTO caster (citext)        VALUES ('foo'::text);
 333
 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);
 338
 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);
 343
 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);
 348
 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");
 353
 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);
 358
 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);
 364
 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);
 370
 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);
 376
 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);
 382
 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);
 388
 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);
 394
 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);
 400
 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);
 406
 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);
 412
 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);
 418
 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);
 424
 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);
 430
 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);
 436
 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);
 442
 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);
 448
 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);
 454
 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);
 460
 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);
 466
 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);
 472
 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);
 478
 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);
 484
 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);
 490
 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);
 496
 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);
 502
 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);
 508
 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);
 514
 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);
 520
 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);
 526
 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);
 532
 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;
 545
 546 SELECT substr('alphabet'::citext, 3)       = 'phabet' AS t;
 547 SELECT substr('alphabet'::citext, 3, 2)    = 'ph' AS t;
 548
 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;
 555
 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;
 560
 561 SELECT upper( name ) = upper( name::text ) AS t FROM srt;
 562
 563 -- Table 9-6. Other String Functions.
 564 SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
 565
 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;
 571
 572 -- chr() takes an int and returns text.
 573 -- convert() and convert_from take bytea and return text.
 574
 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;
 580
 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;
 585
 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;
 590
 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;
 595
 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";
 605
 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;
 612
 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;
 620
 621 -- c forces case-sensitive
 622 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
 623
 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;
 630
 631 SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
 632
 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;
 642
 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;
 647
 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;
 652
 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;
 657
 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;
 664
 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;
 672
 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;
 680
 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;
 687
 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;
 694
 695 -- Try assigning function results to a column.
 696 SELECT COUNT(*) = 8::bigint AS t FROM try;
 697 INSERT INTO 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') );
 709
 710 SELECT COUNT(*) = 19::bigint AS t FROM try;
 711
 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;