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