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 -- Check the min() and max() aggregates, with and without index.
 131 set enable_seqscan = off;
 132 SELECT MIN(name) AS "AAA" FROM srt;
 133 SELECT MAX(name) AS abd FROM srt;
 134 reset enable_seqscan;
 135 set enable_indexscan = off;
 136 SELECT MIN(name) AS "AAA" FROM srt;
 137 SELECT MAX(name) AS abd FROM srt;
 138 reset enable_indexscan;
 139
 140 -- Check sorting likewise
 141 set enable_seqscan = off;
 142 SELECT name FROM srt ORDER BY name;
 143 reset enable_seqscan;
 144 set enable_indexscan = off;
 145 SELECT name FROM srt ORDER BY name;
 146 reset enable_indexscan;
 147
 148 -- Test assignment casts.
 149 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::text;
 150 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::varchar;
 151 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::bpchar;
 152 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA';
 153 SELECT LOWER(name) as aaa FROM srt WHERE name = 'AAA'::citext;
 154
 155 -- LIKE should be case-insensitive
 156 SELECT name FROM srt WHERE name     LIKE '%a%' ORDER BY name;
 157 SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name;
 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
 161 -- ~~ should be case-insensitive
 162 SELECT name FROM srt WHERE name ~~  '%a%' ORDER BY name;
 163 SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name;
 164 SELECT name FROM srt WHERE name ~~  '%A%' ORDER BY name;
 165 SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name;
 166
 167 -- ~ should be case-insensitive
 168 SELECT name FROM srt WHERE name ~  '^a' ORDER BY name;
 169 SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name;
 170 SELECT name FROM srt WHERE name ~  '^A' ORDER BY name;
 171 SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name;
 172
 173 -- SIMILAR TO should be case-insensitive.
 174 SELECT name FROM srt WHERE name SIMILAR TO '%a.*';
 175 SELECT name FROM srt WHERE name SIMILAR TO '%A.*';
 176
 177 -- Explicit casts.
 178 SELECT true::citext = 'true' AS t;
 179 SELECT 'true'::citext::boolean = true AS t;
 180
 181 SELECT 4::citext = '4' AS t;
 182 SELECT 4::int4::citext = '4' AS t;
 183 SELECT '4'::citext::int4 = 4 AS t;
 184 SELECT 4::integer::citext = '4' AS t;
 185 SELECT '4'::citext::integer = 4 AS t;
 186
 187 SELECT 4::int8::citext = '4' AS t;
 188 SELECT '4'::citext::int8 = 4 AS t;
 189 SELECT 4::bigint::citext = '4' AS t;
 190 SELECT '4'::citext::bigint = 4 AS t;
 191
 192 SELECT 4::int2::citext = '4' AS t;
 193 SELECT '4'::citext::int2 = 4 AS t;
 194 SELECT 4::smallint::citext = '4' AS t;
 195 SELECT '4'::citext::smallint = 4 AS t;
 196
 197 SELECT 4.0::numeric = '4.0' AS t;
 198 SELECT '4.0'::citext::numeric = 4.0 AS t;
 199 SELECT 4.0::decimal = '4.0' AS t;
 200 SELECT '4.0'::citext::decimal = 4.0 AS t;
 201
 202 SELECT 4.0::real = '4.0' AS t;
 203 SELECT '4.0'::citext::real = 4.0 AS t;
 204 SELECT 4.0::float4 = '4.0' AS t;
 205 SELECT '4.0'::citext::float4 = 4.0 AS t;
 206
 207 SELECT 4.0::double precision = '4.0' AS t;
 208 SELECT '4.0'::citext::double precision = 4.0 AS t;
 209 SELECT 4.0::float8 = '4.0' AS t;
 210 SELECT '4.0'::citext::float8 = 4.0 AS t;
 211
 212 SELECT 'foo'::name::citext = 'foo' AS t;
 213 SELECT 'foo'::citext::name = 'foo'::name AS t;
 214
 215 SELECT 'f'::char::citext = 'f' AS t;
 216 SELECT 'f'::citext::char = 'f'::char AS t;
 217
 218 SELECT 'f'::"char"::citext = 'f' AS t;
 219 SELECT 'f'::citext::"char" = 'f'::"char" AS t;
 220
 221 SELECT '100'::money::citext = '$100.00' AS t;
 222 SELECT '100'::citext::money = '100'::money AS t;
 223
 224 SELECT 'a'::char::citext = 'a' AS t;
 225 SELECT 'a'::citext::char = 'a'::char AS t;
 226
 227 SELECT 'foo'::varchar::citext = 'foo' AS t;
 228 SELECT 'foo'::citext::varchar = 'foo'::varchar AS t;
 229
 230 SELECT 'foo'::text::citext = 'foo' AS t;
 231 SELECT 'foo'::citext::text = 'foo'::text AS t;
 232
 233 SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t;
 234 SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t;
 235
 236 SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t;
 237 SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
 238
 239 SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
 240 SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
 241
 242 SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
 243 SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
 244 SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
 245 SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t;
 246
 247 SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t;
 248 SELECT '1 hour'::citext::interval = '1 hour'::interval AS t;
 249
 250 SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t;
 251 SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t;
 252
 253 SELECT '04:05:06'::time::citext = '04:05:06' AS t;
 254 SELECT '04:05:06'::citext::time = '04:05:06'::time AS t;
 255 SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t;
 256 SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t;
 257
 258 SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t;
 259 SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t;
 260 SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t;
 261 SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t;
 262 SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t;
 263 SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t;
 264
 265 SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t;
 266 SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t;
 267
 268 SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t;
 269 SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t;
 270
 271 SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t;
 272 SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t;
 273
 274 SELECT '101'::bit::citext = '101'::bit::text AS t;
 275 SELECT '101'::citext::bit = '101'::text::bit AS t;
 276 SELECT '101'::bit varying::citext = '101'::bit varying::text AS t;
 277 SELECT '101'::citext::bit varying = '101'::text::bit varying AS t;
 278 SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t;
 279 SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t;
 280 SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t;
 281 SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t;
 282 SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t;
 283 SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t;
 284
 285 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
 286 SELECT 'sad'::mood::citext = 'sad' AS t;
 287 SELECT 'sad'::citext::mood = 'sad'::mood AS t;
 288
 289 -- Assignment casts.
 290 CREATE TABLE caster (
 291     citext      citext,
 292     text        text,
 293     varchar     varchar,
 294     bpchar      bpchar,
 295     char        char,
 296     chr         "char",
 297     name        name,
 298     bytea       bytea,
 299     boolean     boolean,
 300     float4      float4,
 301     float8      float8,
 302     numeric     numeric,
 303     int8        int8,
 304     int4        int4,
 305     int2        int2,
 306     cidr        cidr,
 307     inet        inet,
 308     macaddr     macaddr,
 309     money       money,
 310     timestamp   timestamp,
 311     timestamptz timestamptz,
 312     interval    interval,
 313     date        date,
 314     time        time,
 315     timetz      timetz,
 316     point       point,
 317     lseg        lseg,
 318     box         box,
 319     path        path,
 320     polygon     polygon,
 321     circle      circle,
 322     bit         bit,
 323     bitv        bit varying,
 324     tsvector    tsvector,
 325     tsquery     tsquery,
 326     uuid        uuid
 327 );
 328
 329 INSERT INTO caster (text)          VALUES ('foo'::citext);
 330 INSERT INTO caster (citext)        VALUES ('foo'::text);
 331
 332 INSERT INTO caster (varchar)       VALUES ('foo'::text);
 333 INSERT INTO caster (text)          VALUES ('foo'::varchar);
 334 INSERT INTO caster (varchar)       VALUES ('foo'::citext);
 335 INSERT INTO caster (citext)        VALUES ('foo'::varchar);
 336
 337 INSERT INTO caster (bpchar)        VALUES ('foo'::text);
 338 INSERT INTO caster (text)          VALUES ('foo'::bpchar);
 339 INSERT INTO caster (bpchar)        VALUES ('foo'::citext);
 340 INSERT INTO caster (citext)        VALUES ('foo'::bpchar);
 341
 342 INSERT INTO caster (char)          VALUES ('f'::text);
 343 INSERT INTO caster (text)          VALUES ('f'::char);
 344 INSERT INTO caster (char)          VALUES ('f'::citext);
 345 INSERT INTO caster (citext)        VALUES ('f'::char);
 346
 347 INSERT INTO caster (chr)           VALUES ('f'::text);
 348 INSERT INTO caster (text)          VALUES ('f'::"char");
 349 INSERT INTO caster (chr)           VALUES ('f'::citext);
 350 INSERT INTO caster (citext)        VALUES ('f'::"char");
 351
 352 INSERT INTO caster (name)          VALUES ('foo'::text);
 353 INSERT INTO caster (text)          VALUES ('foo'::name);
 354 INSERT INTO caster (name)          VALUES ('foo'::citext);
 355 INSERT INTO caster (citext)        VALUES ('foo'::name);
 356
 357 -- Cannot cast to bytea on assignment.
 358 INSERT INTO caster (bytea)         VALUES ('foo'::text);
 359 INSERT INTO caster (text)          VALUES ('foo'::bytea);
 360 INSERT INTO caster (bytea)         VALUES ('foo'::citext);
 361 INSERT INTO caster (citext)        VALUES ('foo'::bytea);
 362
 363 -- Cannot cast to boolean on assignment.
 364 INSERT INTO caster (boolean)       VALUES ('t'::text);
 365 INSERT INTO caster (text)          VALUES ('t'::boolean);
 366 INSERT INTO caster (boolean)       VALUES ('t'::citext);
 367 INSERT INTO caster (citext)        VALUES ('t'::boolean);
 368
 369 -- Cannot cast to float8 on assignment.
 370 INSERT INTO caster (float8)        VALUES ('12.42'::text);
 371 INSERT INTO caster (text)          VALUES ('12.42'::float8);
 372 INSERT INTO caster (float8)        VALUES ('12.42'::citext);
 373 INSERT INTO caster (citext)        VALUES ('12.42'::float8);
 374
 375 -- Cannot cast to float4 on assignment.
 376 INSERT INTO caster (float4)        VALUES ('12.42'::text);
 377 INSERT INTO caster (text)          VALUES ('12.42'::float4);
 378 INSERT INTO caster (float4)        VALUES ('12.42'::citext);
 379 INSERT INTO caster (citext)        VALUES ('12.42'::float4);
 380
 381 -- Cannot cast to numeric on assignment.
 382 INSERT INTO caster (numeric)       VALUES ('12.42'::text);
 383 INSERT INTO caster (text)          VALUES ('12.42'::numeric);
 384 INSERT INTO caster (numeric)       VALUES ('12.42'::citext);
 385 INSERT INTO caster (citext)        VALUES ('12.42'::numeric);
 386
 387 -- Cannot cast to int8 on assignment.
 388 INSERT INTO caster (int8)          VALUES ('12'::text);
 389 INSERT INTO caster (text)          VALUES ('12'::int8);
 390 INSERT INTO caster (int8)          VALUES ('12'::citext);
 391 INSERT INTO caster (citext)        VALUES ('12'::int8);
 392
 393 -- Cannot cast to int4 on assignment.
 394 INSERT INTO caster (int4)          VALUES ('12'::text);
 395 INSERT INTO caster (text)          VALUES ('12'::int4);
 396 INSERT INTO caster (int4)          VALUES ('12'::citext);
 397 INSERT INTO caster (citext)        VALUES ('12'::int4);
 398
 399 -- Cannot cast to int2 on assignment.
 400 INSERT INTO caster (int2)          VALUES ('12'::text);
 401 INSERT INTO caster (text)          VALUES ('12'::int2);
 402 INSERT INTO caster (int2)          VALUES ('12'::citext);
 403 INSERT INTO caster (citext)        VALUES ('12'::int2);
 404
 405 -- Cannot cast to cidr on assignment.
 406 INSERT INTO caster (cidr)          VALUES ('192.168.100.128/25'::text);
 407 INSERT INTO caster (text)          VALUES ('192.168.100.128/25'::cidr);
 408 INSERT INTO caster (cidr)          VALUES ('192.168.100.128/25'::citext);
 409 INSERT INTO caster (citext)        VALUES ('192.168.100.128/25'::cidr);
 410
 411 -- Cannot cast to inet on assignment.
 412 INSERT INTO caster (inet)          VALUES ('192.168.100.128'::text);
 413 INSERT INTO caster (text)          VALUES ('192.168.100.128'::inet);
 414 INSERT INTO caster (inet)          VALUES ('192.168.100.128'::citext);
 415 INSERT INTO caster (citext)        VALUES ('192.168.100.128'::inet);
 416
 417 -- Cannot cast to macaddr on assignment.
 418 INSERT INTO caster (macaddr)       VALUES ('08:00:2b:01:02:03'::text);
 419 INSERT INTO caster (text)          VALUES ('08:00:2b:01:02:03'::macaddr);
 420 INSERT INTO caster (macaddr)       VALUES ('08:00:2b:01:02:03'::citext);
 421 INSERT INTO caster (citext)        VALUES ('08:00:2b:01:02:03'::macaddr);
 422
 423 -- Cannot cast to money on assignment.
 424 INSERT INTO caster (money)         VALUES ('12'::text);
 425 INSERT INTO caster (text)          VALUES ('12'::money);
 426 INSERT INTO caster (money)         VALUES ('12'::citext);
 427 INSERT INTO caster (citext)        VALUES ('12'::money);
 428
 429 -- Cannot cast to timestamp on assignment.
 430 INSERT INTO caster (timestamp)     VALUES ('1999-01-08 04:05:06'::text);
 431 INSERT INTO caster (text)          VALUES ('1999-01-08 04:05:06'::timestamp);
 432 INSERT INTO caster (timestamp)     VALUES ('1999-01-08 04:05:06'::citext);
 433 INSERT INTO caster (citext)        VALUES ('1999-01-08 04:05:06'::timestamp);
 434
 435 -- Cannot cast to timestamptz on assignment.
 436 INSERT INTO caster (timestamptz)   VALUES ('1999-01-08 04:05:06'::text);
 437 INSERT INTO caster (text)          VALUES ('1999-01-08 04:05:06'::timestamptz);
 438 INSERT INTO caster (timestamptz)   VALUES ('1999-01-08 04:05:06'::citext);
 439 INSERT INTO caster (citext)        VALUES ('1999-01-08 04:05:06'::timestamptz);
 440
 441 -- Cannot cast to interval on assignment.
 442 INSERT INTO caster (interval)      VALUES ('1 hour'::text);
 443 INSERT INTO caster (text)          VALUES ('1 hour'::interval);
 444 INSERT INTO caster (interval)      VALUES ('1 hour'::citext);
 445 INSERT INTO caster (citext)        VALUES ('1 hour'::interval);
 446
 447 -- Cannot cast to date on assignment.
 448 INSERT INTO caster (date)          VALUES ('1999-01-08'::text);
 449 INSERT INTO caster (text)          VALUES ('1999-01-08'::date);
 450 INSERT INTO caster (date)          VALUES ('1999-01-08'::citext);
 451 INSERT INTO caster (citext)        VALUES ('1999-01-08'::date);
 452
 453 -- Cannot cast to time on assignment.
 454 INSERT INTO caster (time)          VALUES ('04:05:06'::text);
 455 INSERT INTO caster (text)          VALUES ('04:05:06'::time);
 456 INSERT INTO caster (time)          VALUES ('04:05:06'::citext);
 457 INSERT INTO caster (citext)        VALUES ('04:05:06'::time);
 458
 459 -- Cannot cast to timetz on assignment.
 460 INSERT INTO caster (timetz)        VALUES ('04:05:06'::text);
 461 INSERT INTO caster (text)          VALUES ('04:05:06'::timetz);
 462 INSERT INTO caster (timetz)        VALUES ('04:05:06'::citext);
 463 INSERT INTO caster (citext)        VALUES ('04:05:06'::timetz);
 464
 465 -- Cannot cast to point on assignment.
 466 INSERT INTO caster (point)         VALUES ('( 1 , 1)'::text);
 467 INSERT INTO caster (text)          VALUES ('( 1 , 1)'::point);
 468 INSERT INTO caster (point)         VALUES ('( 1 , 1)'::citext);
 469 INSERT INTO caster (citext)        VALUES ('( 1 , 1)'::point);
 470
 471 -- Cannot cast to lseg on assignment.
 472 INSERT INTO caster (lseg)          VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text);
 473 INSERT INTO caster (text)          VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
 474 INSERT INTO caster (lseg)          VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext);
 475 INSERT INTO caster (citext)        VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg);
 476
 477 -- Cannot cast to box on assignment.
 478 INSERT INTO caster (box)           VALUES ('(0,0),(1,1)'::text);
 479 INSERT INTO caster (text)          VALUES ('(0,0),(1,1)'::box);
 480 INSERT INTO caster (box)           VALUES ('(0,0),(1,1)'::citext);
 481 INSERT INTO caster (citext)        VALUES ('(0,0),(1,1)'::box);
 482
 483 -- Cannot cast to path on assignment.
 484 INSERT INTO caster (path)          VALUES ('((0,0),(1,1),(2,0))'::text);
 485 INSERT INTO caster (text)          VALUES ('((0,0),(1,1),(2,0))'::path);
 486 INSERT INTO caster (path)          VALUES ('((0,0),(1,1),(2,0))'::citext);
 487 INSERT INTO caster (citext)        VALUES ('((0,0),(1,1),(2,0))'::path);
 488
 489 -- Cannot cast to polygon on assignment.
 490 INSERT INTO caster (polygon)       VALUES ('((0,0),(1,1))'::text);
 491 INSERT INTO caster (text)          VALUES ('((0,0),(1,1))'::polygon);
 492 INSERT INTO caster (polygon)       VALUES ('((0,0),(1,1))'::citext);
 493 INSERT INTO caster (citext)        VALUES ('((0,0),(1,1))'::polygon);
 494
 495 -- Cannot cast to circle on assignment.
 496 INSERT INTO caster (circle)        VALUES ('((0,0),2)'::text);
 497 INSERT INTO caster (text)          VALUES ('((0,0),2)'::circle);
 498 INSERT INTO caster (circle)        VALUES ('((0,0),2)'::citext);
 499 INSERT INTO caster (citext)        VALUES ('((0,0),2)'::circle);
 500
 501 -- Cannot cast to bit on assignment.
 502 INSERT INTO caster (bit)           VALUES ('101'::text);
 503 INSERT INTO caster (text)          VALUES ('101'::bit);
 504 INSERT INTO caster (bit)           VALUES ('101'::citext);
 505 INSERT INTO caster (citext)        VALUES ('101'::bit);
 506
 507 -- Cannot cast to bit varying on assignment.
 508 INSERT INTO caster (bitv)          VALUES ('101'::text);
 509 INSERT INTO caster (text)          VALUES ('101'::bit varying);
 510 INSERT INTO caster (bitv)          VALUES ('101'::citext);
 511 INSERT INTO caster (citext)        VALUES ('101'::bit varying);
 512
 513 -- Cannot cast to tsvector on assignment.
 514 INSERT INTO caster (tsvector)      VALUES ('the fat cat'::text);
 515 INSERT INTO caster (text)          VALUES ('the fat cat'::tsvector);
 516 INSERT INTO caster (tsvector)      VALUES ('the fat cat'::citext);
 517 INSERT INTO caster (citext)        VALUES ('the fat cat'::tsvector);
 518
 519 -- Cannot cast to tsquery on assignment.
 520 INSERT INTO caster (tsquery)       VALUES ('fat & rat'::text);
 521 INSERT INTO caster (text)          VALUES ('fat & rat'::tsquery);
 522 INSERT INTO caster (tsquery)       VALUES ('fat & rat'::citext);
 523 INSERT INTO caster (citext)        VALUES ('fat & rat'::tsquery);
 524
 525 -- Cannot cast to uuid on assignment.
 526 INSERT INTO caster (uuid)          VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);
 527 INSERT INTO caster (text)          VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
 528 INSERT INTO caster (uuid)          VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext);
 529 INSERT INTO caster (citext)        VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);
 530
 531 -- Table 9-5. SQL String Functions and Operators
 532 SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat;
 533 SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat;
 534 SELECT  42 || ': value'::citext ='42: value' AS int_concat;
 535 SELECT bit_length('jose'::citext) = 32 AS t;
 536 SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt;
 537 SELECT textlen( name ) = textlen( name::text ) AS t FROM srt;
 538 SELECT char_length( name ) = char_length( name::text ) AS t FROM srt;
 539 SELECT lower( name ) = lower( name::text ) AS t FROM srt;
 540 SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt;
 541 SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt;
 542 SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt;
 543
 544 SELECT substr('alphabet'::citext, 3)       = 'phabet' AS t;
 545 SELECT substr('alphabet'::citext, 3, 2)    = 'ph' AS t;
 546
 547 SELECT substring('alphabet'::citext, 3)    = 'phabet' AS t;
 548 SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t;
 549 SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t;
 550 SELECT substring('Thomas'::citext from 2) = 'homas' AS t;
 551 SELECT substring('Thomas'::citext from '...$') = 'mas' AS t;
 552 SELECT substring('Thomas'::citext from '%#"o_a#"_' for '#') = 'oma' AS t;
 553
 554 SELECT trim('    trim    '::citext)               = 'trim' AS t;
 555 SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t;
 556 SELECT trim('xxxxxxtrimxxxx'::text,  'x'::citext) = 'trim' AS t;
 557 SELECT trim('xxxxxtrimxxxx'::text,   'x'::citext) = 'trim' AS t;
 558
 559 SELECT upper( name ) = upper( name::text ) AS t FROM srt;
 560
 561 -- Table 9-6. Other String Functions.
 562 SELECT ascii( name ) = ascii( name::text ) AS t FROM srt;
 563
 564 SELECT btrim('    trim'::citext                   ) = 'trim' AS t;
 565 SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t;
 566 SELECT btrim('xyxtrimyyx'::citext,    'xy'::citext) = 'trim' AS t;
 567 SELECT btrim('xyxtrimyyx'::text,      'xy'::citext) = 'trim' AS t;
 568 SELECT btrim('xyxtrimyyx'::citext,    'xy'::text  ) = 'trim' AS t;
 569
 570 -- chr() takes an int and returns text.
 571 -- convert() and convert_from take bytea and return text.
 572
 573 SELECT convert_to( name, 'ISO-8859-1' ) = convert_to( name::text, 'ISO-8859-1' ) AS t FROM srt;
 574 SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t;
 575 -- encode() takes bytea and returns text.
 576 SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t;
 577 SELECT length( name ) = length( name::text ) AS t FROM srt;
 578
 579 SELECT lpad('hi'::citext, 5              ) = '   hi' AS t;
 580 SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t;
 581 SELECT lpad('hi'::text,   5, 'xy'::citext) = 'xyxhi' AS t;
 582 SELECT lpad('hi'::citext, 5, 'xy'::text  ) = 'xyxhi' AS t;
 583
 584 SELECT ltrim('    trim'::citext               ) = 'trim' AS t;
 585 SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t;
 586 SELECT ltrim('zzzytrim'::text,   'xyz'::citext) = 'trim' AS t;
 587 SELECT ltrim('zzzytrim'::citext, 'xyz'::text  ) = 'trim' AS t;
 588
 589 SELECT md5( name ) = md5( name::text ) AS t FROM srt;
 590 -- pg_client_encoding() takes no args and returns name.
 591 SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt;
 592 SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt;
 593
 594 SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t;
 595 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t;
 596 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t;
 597 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
 598 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t;
 599 SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t;
 600 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t;
 601 -- c forces case-sensitive
 602 SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "null";
 603
 604 SELECT regexp_replace('Thomas'::citext, '.[mN]a.',         'M') = 'ThM' AS t;
 605 SELECT regexp_replace('Thomas'::citext, '.[MN]A.',         'M') = 'ThM' AS t;
 606 SELECT regexp_replace('Thomas',         '.[MN]A.'::citext, 'M') = 'ThM' AS t;
 607 SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t;
 608 -- c forces case-sensitive
 609 SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t;
 610
 611 SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t;
 612 SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t;
 613 SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
 614 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t;
 615 SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t;
 616 SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
 617 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t;
 618
 619 -- c forces case-sensitive
 620 SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t;
 621
 622 SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words;
 623 SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words;
 624 SELECT regexp_split_to_table('helloTworld',         't'::citext) AS words;
 625 SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words;
 626 -- c forces case-sensitive
 627 SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word;
 628
 629 SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t;
 630
 631 SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t;
 632 SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t;
 633 SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t;
 634 SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
 635 SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
 636 SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
 637 SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t;
 638 SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
 639 SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t;
 640
 641 SELECT rpad('hi'::citext, 5              ) = 'hi   ' AS t;
 642 SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t;
 643 SELECT rpad('hi'::text,   5, 'xy'::citext) = 'hixyx' AS t;
 644 SELECT rpad('hi'::citext, 5, 'xy'::text  ) = 'hixyx' AS t;
 645
 646 SELECT rtrim('trim    '::citext             ) = 'trim' AS t;
 647 SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t;
 648 SELECT rtrim('trimxxxx'::text,   'x'::citext) = 'trim' AS t;
 649 SELECT rtrim('trimxxxx'::text,   'x'::text  ) = 'trim' AS t;
 650
 651 SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t;
 652 SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t;
 653 SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t;
 654 SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t;
 655
 656 SELECT strpos('high'::citext, 'ig'        ) = 2 AS t;
 657 SELECT strpos('high',         'ig'::citext) = 2 AS t;
 658 SELECT strpos('high'::citext, 'ig'::citext) = 2 AS t;
 659 SELECT strpos('high'::citext, 'IG'        ) = 2 AS t;
 660 SELECT strpos('high',         'IG'::citext) = 2 AS t;
 661 SELECT strpos('high'::citext, 'IG'::citext) = 2 AS t;
 662
 663 -- to_ascii() does not support UTF-8.
 664 -- to_hex() takes a numeric argument.
 665 SELECT substr('alphabet', 3, 2) = 'ph' AS t;
 666 SELECT translate('abcdefabcdef'::citext, 'cd',         'XX') = 'abXXefabXXef' AS t;
 667 SELECT translate('abcdefabcdef'::citext, 'CD',         'XX') = 'abXXefabXXef' AS t;
 668 SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
 669 SELECT translate('abcdefabcdef',         'CD'::citext, 'XX') = 'abXXefabXXef' AS t;
 670
 671 -- Table 9-20. Formatting Functions
 672 SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
 673      = to_date('05 Dec 2000',         'DD Mon YYYY') AS t;
 674 SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY')
 675      = to_date('05 Dec 2000',         'DD Mon YYYY') AS t;
 676 SELECT to_date('05 Dec 2000',         'DD Mon YYYY'::citext)
 677      = to_date('05 Dec 2000',         'DD Mon YYYY') AS t;
 678
 679 SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext)
 680      = to_number('12,454.8-',         '99G999D9S') AS t;
 681 SELECT to_number('12,454.8-'::citext, '99G999D9S')
 682      = to_number('12,454.8-',         '99G999D9S') AS t;
 683 SELECT to_number('12,454.8-',         '99G999D9S'::citext)
 684      = to_number('12,454.8-',         '99G999D9S') AS t;
 685
 686 SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext)
 687      = to_timestamp('05 Dec 2000',         'DD Mon YYYY') AS t;
 688 SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY')
 689      = to_timestamp('05 Dec 2000',         'DD Mon YYYY') AS t;
 690 SELECT to_timestamp('05 Dec 2000',         'DD Mon YYYY'::citext)
 691      = to_timestamp('05 Dec 2000',         'DD Mon YYYY') AS t;
 692
 693 -- Try assigning function results to a column.
 694 SELECT COUNT(*) = 8::bigint AS t FROM try;
 695 INSERT INTO try
 696 VALUES ( to_char(  now()::timestamp,          'HH12:MI:SS') ),
 697        ( to_char(  now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timetamptz
 698        ( to_char(  '15h 2m 12s'::interval,    'HH24:MI:SS') ),
 699        ( to_char(  current_date,              '999') ),
 700        ( to_char(  125::int,                  '999') ),
 701        ( to_char(  127::int4,                 '999') ),
 702        ( to_char(  126::int8,                 '999') ),
 703        ( to_char(  128.8::real,               '999D9') ),
 704        ( to_char(  125.7::float4,             '999D9') ),
 705        ( to_char(  125.9::float8,             '999D9') ),
 706        ( to_char( -125.8::numeric,            '999D99S') );
 707
 708 SELECT COUNT(*) = 19::bigint AS t FROM try;
 709
 710 SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
 711 SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;