1-- 2-- Test citext datatype 3-- 4CREATE EXTENSION citext; 5-- Check whether any of our opclasses fail amvalidate 6SELECT amname, opcname 7FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod 8WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); 9 amname | opcname 10--------+--------- 11(0 rows) 12 13-- Test the operators and indexing functions 14-- Test = and <>. 15SELECT 'a'::citext = 'a'::citext AS t; 16 t 17--- 18 t 19(1 row) 20 21SELECT 'a'::citext = 'A'::citext AS t; 22 t 23--- 24 t 25(1 row) 26 27SELECT 'a'::citext = 'A'::text AS f; -- text wins the discussion 28 f 29--- 30 f 31(1 row) 32 33SELECT 'a'::citext = 'b'::citext AS f; 34 f 35--- 36 f 37(1 row) 38 39SELECT 'a'::citext = 'ab'::citext AS f; 40 f 41--- 42 f 43(1 row) 44 45SELECT 'a'::citext <> 'ab'::citext AS t; 46 t 47--- 48 t 49(1 row) 50 51-- Multibyte sanity tests. Uncomment to run. 52-- SELECT 'À'::citext = 'À'::citext AS t; 53-- SELECT 'À'::citext = 'à'::citext AS t; 54-- SELECT 'À'::text = 'à'::text AS f; -- text wins. 55-- SELECT 'À'::citext <> 'B'::citext AS t; 56-- Test combining characters making up canonically equivalent strings. 57-- SELECT 'Ä'::text <> 'Ä'::text AS t; 58-- SELECT 'Ä'::citext <> 'Ä'::citext AS t; 59-- Test the Turkish dotted I. The lowercase is a single byte while the 60-- uppercase is multibyte. This is why the comparison code can't be optimized 61-- to compare string lengths. 62-- SELECT 'i'::citext = 'İ'::citext AS t; 63-- Regression. 64-- SELECT 'láska'::citext <> 'laská'::citext AS t; 65-- SELECT 'Ask Bjørn Hansen'::citext = 'Ask Bjørn Hansen'::citext AS t; 66-- SELECT 'Ask Bjørn Hansen'::citext = 'ASK BJØRN HANSEN'::citext AS t; 67-- SELECT 'Ask Bjørn Hansen'::citext <> 'Ask Bjorn Hansen'::citext AS t; 68-- SELECT 'Ask Bjørn Hansen'::citext <> 'ASK BJORN HANSEN'::citext AS t; 69-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS zero; 70-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ask bjørn hansen'::citext) AS zero; 71-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'ASK BJØRN HANSEN'::citext) AS zero; 72-- SELECT citext_cmp('Ask Bjørn Hansen'::citext, 'Ask Bjorn Hansen'::citext) AS positive; 73-- SELECT citext_cmp('Ask Bjorn Hansen'::citext, 'Ask Bjørn Hansen'::citext) AS negative; 74-- Test > and >= 75SELECT 'B'::citext > 'a'::citext AS t; 76 t 77--- 78 t 79(1 row) 80 81SELECT 'b'::citext > 'A'::citext AS t; 82 t 83--- 84 t 85(1 row) 86 87SELECT 'B'::citext > 'b'::citext AS f; 88 f 89--- 90 f 91(1 row) 92 93SELECT 'B'::citext >= 'b'::citext AS t; 94 t 95--- 96 t 97(1 row) 98 99-- Test < and <= 100SELECT 'a'::citext < 'B'::citext AS t; 101 t 102--- 103 t 104(1 row) 105 106SELECT 'a'::citext <= 'B'::citext AS t; 107 t 108--- 109 t 110(1 row) 111 112-- Test implicit casting. citext casts to text, but not vice-versa. 113SELECT 'a'::citext = 'a'::text AS t; 114 t 115--- 116 t 117(1 row) 118 119SELECT 'A'::text <> 'a'::citext AS t; 120 t 121--- 122 t 123(1 row) 124 125SELECT 'B'::citext < 'a'::text AS t; -- text wins. 126 t 127--- 128 t 129(1 row) 130 131SELECT 'B'::citext <= 'a'::text AS t; -- text wins. 132 t 133--- 134 t 135(1 row) 136 137SELECT 'a'::citext > 'B'::text AS t; -- text wins. 138 t 139--- 140 t 141(1 row) 142 143SELECT 'a'::citext >= 'B'::text AS t; -- text wins. 144 t 145--- 146 t 147(1 row) 148 149-- Test implicit casting. citext casts to varchar, but not vice-versa. 150SELECT 'a'::citext = 'a'::varchar AS t; 151 t 152--- 153 t 154(1 row) 155 156SELECT 'A'::varchar <> 'a'::citext AS t; 157 t 158--- 159 t 160(1 row) 161 162SELECT 'B'::citext < 'a'::varchar AS t; -- varchar wins. 163 t 164--- 165 t 166(1 row) 167 168SELECT 'B'::citext <= 'a'::varchar AS t; -- varchar wins. 169 t 170--- 171 t 172(1 row) 173 174SELECT 'a'::citext > 'B'::varchar AS t; -- varchar wins. 175 t 176--- 177 t 178(1 row) 179 180SELECT 'a'::citext >= 'B'::varchar AS t; -- varchar wins. 181 t 182--- 183 t 184(1 row) 185 186-- A couple of longer examples to ensure that we don't get any issues with bad 187-- conversions to char[] in the c code. Yes, I did do this. 188SELECT 'aardvark'::citext = 'aardvark'::citext AS t; 189 t 190--- 191 t 192(1 row) 193 194SELECT 'aardvark'::citext = 'aardVark'::citext AS t; 195 t 196--- 197 t 198(1 row) 199 200-- Check the citext_cmp() function explicitly. 201SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero; 202 zero 203------ 204 0 205(1 row) 206 207SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) AS zero; 208 zero 209------ 210 0 211(1 row) 212 213SELECT citext_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero; 214 zero 215------ 216 0 217(1 row) 218 219SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true; 220 true 221------ 222 t 223(1 row) 224 225-- Check the citext_hash() and citext_hash_extended() function explicitly. 226SELECT v as value, citext_hash(v)::bit(32) as standard, 227 citext_hash_extended(v, 0)::bit(32) as extended0, 228 citext_hash_extended(v, 1)::bit(32) as extended1 229FROM (VALUES (NULL::citext), ('PostgreSQL'), ('eIpUEtqmY89'), ('AXKEJBTK'), 230 ('muop28x03'), ('yi3nm0d73')) x(v) 231WHERE citext_hash(v)::bit(32) != citext_hash_extended(v, 0)::bit(32) 232 OR citext_hash(v)::bit(32) = citext_hash_extended(v, 1)::bit(32); 233 value | standard | extended0 | extended1 234-------+----------+-----------+----------- 235(0 rows) 236 237-- Do some tests using a table and index. 238CREATE TEMP TABLE try ( 239 name citext PRIMARY KEY 240); 241INSERT INTO try (name) 242VALUES ('a'), ('ab'), ('â'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ'); 243SELECT name, 'a' = name AS eq_a FROM try WHERE name <> 'â'; 244 name | eq_a 245------+------ 246 a | t 247 ab | f 248 aba | f 249 b | f 250 ba | f 251 bab | f 252 AZ | f 253(7 rows) 254 255SELECT name, 'a' = name AS t FROM try where name = 'a'; 256 name | t 257------+--- 258 a | t 259(1 row) 260 261SELECT name, 'A' = name AS "eq_A" FROM try WHERE name <> 'â'; 262 name | eq_A 263------+------ 264 a | t 265 ab | f 266 aba | f 267 b | f 268 ba | f 269 bab | f 270 AZ | f 271(7 rows) 272 273SELECT name, 'A' = name AS t FROM try where name = 'A'; 274 name | t 275------+--- 276 a | t 277(1 row) 278 279SELECT name, 'A' = name AS t FROM try where name = 'A'; 280 name | t 281------+--- 282 a | t 283(1 row) 284 285-- expected failures on duplicate key 286INSERT INTO try (name) VALUES ('a'); 287ERROR: duplicate key value violates unique constraint "try_pkey" 288DETAIL: Key (name)=(a) already exists. 289INSERT INTO try (name) VALUES ('A'); 290ERROR: duplicate key value violates unique constraint "try_pkey" 291DETAIL: Key (name)=(A) already exists. 292INSERT INTO try (name) VALUES ('aB'); 293ERROR: duplicate key value violates unique constraint "try_pkey" 294DETAIL: Key (name)=(aB) already exists. 295-- Make sure that citext_smaller() and citext_larger() work properly. 296SELECT citext_smaller( 'ab'::citext, 'ac'::citext ) = 'ab' AS t; 297 t 298--- 299 t 300(1 row) 301 302SELECT citext_smaller( 'ABC'::citext, 'bbbb'::citext ) = 'ABC' AS t; 303 t 304--- 305 t 306(1 row) 307 308SELECT citext_smaller( 'aardvark'::citext, 'Aaba'::citext ) = 'Aaba' AS t; 309 t 310--- 311 t 312(1 row) 313 314SELECT citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ) = 'AARDVARK' AS t; 315 t 316--- 317 t 318(1 row) 319 320SELECT citext_larger( 'ab'::citext, 'ac'::citext ) = 'ac' AS t; 321 t 322--- 323 t 324(1 row) 325 326SELECT citext_larger( 'ABC'::citext, 'bbbb'::citext ) = 'bbbb' AS t; 327 t 328--- 329 t 330(1 row) 331 332SELECT citext_larger( 'aardvark'::citext, 'Aaba'::citext ) = 'aardvark' AS t; 333 t 334--- 335 t 336(1 row) 337 338-- Test aggregate functions and sort ordering 339CREATE TEMP TABLE srt ( 340 name CITEXT 341); 342INSERT INTO srt (name) 343VALUES ('abb'), 344 ('ABA'), 345 ('ABC'), 346 ('abd'); 347CREATE INDEX srt_name ON srt (name); 348-- Check the min() and max() aggregates, with and without index. 349set enable_seqscan = off; 350SELECT MIN(name) AS "ABA" FROM srt; 351 ABA 352----- 353 ABA 354(1 row) 355 356SELECT MAX(name) AS abd FROM srt; 357 abd 358----- 359 abd 360(1 row) 361 362reset enable_seqscan; 363set enable_indexscan = off; 364SELECT MIN(name) AS "ABA" FROM srt; 365 ABA 366----- 367 ABA 368(1 row) 369 370SELECT MAX(name) AS abd FROM srt; 371 abd 372----- 373 abd 374(1 row) 375 376reset enable_indexscan; 377-- Check sorting likewise 378set enable_seqscan = off; 379SELECT name FROM srt ORDER BY name; 380 name 381------ 382 ABA 383 abb 384 ABC 385 abd 386(4 rows) 387 388reset enable_seqscan; 389set enable_indexscan = off; 390SELECT name FROM srt ORDER BY name; 391 name 392------ 393 ABA 394 abb 395 ABC 396 abd 397(4 rows) 398 399reset enable_indexscan; 400-- Test assignment casts. 401SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::text; 402 aba 403----- 404 aba 405(1 row) 406 407SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::varchar; 408 aba 409----- 410 aba 411(1 row) 412 413SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::bpchar; 414 aba 415----- 416 aba 417(1 row) 418 419SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'; 420 aba 421----- 422 aba 423(1 row) 424 425SELECT LOWER(name) as aba FROM srt WHERE name = 'ABA'::citext; 426 aba 427----- 428 aba 429(1 row) 430 431-- LIKE should be case-insensitive 432SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name; 433 name 434------ 435 ABA 436 abb 437 ABC 438 abd 439(4 rows) 440 441SELECT name FROM srt WHERE name NOT LIKE '%b%' ORDER BY name; 442 name 443------ 444(0 rows) 445 446SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name; 447 name 448------ 449 ABA 450 abb 451 ABC 452 abd 453(4 rows) 454 455SELECT name FROM srt WHERE name NOT LIKE '%B%' ORDER BY name; 456 name 457------ 458(0 rows) 459 460-- ~~ should be case-insensitive 461SELECT name FROM srt WHERE name ~~ '%a%' ORDER BY name; 462 name 463------ 464 ABA 465 abb 466 ABC 467 abd 468(4 rows) 469 470SELECT name FROM srt WHERE name !~~ '%b%' ORDER BY name; 471 name 472------ 473(0 rows) 474 475SELECT name FROM srt WHERE name ~~ '%A%' ORDER BY name; 476 name 477------ 478 ABA 479 abb 480 ABC 481 abd 482(4 rows) 483 484SELECT name FROM srt WHERE name !~~ '%B%' ORDER BY name; 485 name 486------ 487(0 rows) 488 489-- ~ should be case-insensitive 490SELECT name FROM srt WHERE name ~ '^a' ORDER BY name; 491 name 492------ 493 ABA 494 abb 495 ABC 496 abd 497(4 rows) 498 499SELECT name FROM srt WHERE name !~ 'a$' ORDER BY name; 500 name 501------ 502 abb 503 ABC 504 abd 505(3 rows) 506 507SELECT name FROM srt WHERE name ~ '^A' ORDER BY name; 508 name 509------ 510 ABA 511 abb 512 ABC 513 abd 514(4 rows) 515 516SELECT name FROM srt WHERE name !~ 'A$' ORDER BY name; 517 name 518------ 519 abb 520 ABC 521 abd 522(3 rows) 523 524-- SIMILAR TO should be case-insensitive. 525SELECT name FROM srt WHERE name SIMILAR TO '%a.*'; 526 name 527------ 528 ABA 529(1 row) 530 531SELECT name FROM srt WHERE name SIMILAR TO '%A.*'; 532 name 533------ 534 ABA 535(1 row) 536 537-- Explicit casts. 538SELECT true::citext = 'true' AS t; 539 t 540--- 541 t 542(1 row) 543 544SELECT 'true'::citext::boolean = true AS t; 545 t 546--- 547 t 548(1 row) 549 550SELECT 4::citext = '4' AS t; 551 t 552--- 553 t 554(1 row) 555 556SELECT 4::int4::citext = '4' AS t; 557 t 558--- 559 t 560(1 row) 561 562SELECT '4'::citext::int4 = 4 AS t; 563 t 564--- 565 t 566(1 row) 567 568SELECT 4::integer::citext = '4' AS t; 569 t 570--- 571 t 572(1 row) 573 574SELECT '4'::citext::integer = 4 AS t; 575 t 576--- 577 t 578(1 row) 579 580SELECT 4::int8::citext = '4' AS t; 581 t 582--- 583 t 584(1 row) 585 586SELECT '4'::citext::int8 = 4 AS t; 587 t 588--- 589 t 590(1 row) 591 592SELECT 4::bigint::citext = '4' AS t; 593 t 594--- 595 t 596(1 row) 597 598SELECT '4'::citext::bigint = 4 AS t; 599 t 600--- 601 t 602(1 row) 603 604SELECT 4::int2::citext = '4' AS t; 605 t 606--- 607 t 608(1 row) 609 610SELECT '4'::citext::int2 = 4 AS t; 611 t 612--- 613 t 614(1 row) 615 616SELECT 4::smallint::citext = '4' AS t; 617 t 618--- 619 t 620(1 row) 621 622SELECT '4'::citext::smallint = 4 AS t; 623 t 624--- 625 t 626(1 row) 627 628SELECT 4.0::numeric = '4.0' AS t; 629 t 630--- 631 t 632(1 row) 633 634SELECT '4.0'::citext::numeric = 4.0 AS t; 635 t 636--- 637 t 638(1 row) 639 640SELECT 4.0::decimal = '4.0' AS t; 641 t 642--- 643 t 644(1 row) 645 646SELECT '4.0'::citext::decimal = 4.0 AS t; 647 t 648--- 649 t 650(1 row) 651 652SELECT 4.0::real = '4.0' AS t; 653 t 654--- 655 t 656(1 row) 657 658SELECT '4.0'::citext::real = 4.0 AS t; 659 t 660--- 661 t 662(1 row) 663 664SELECT 4.0::float4 = '4.0' AS t; 665 t 666--- 667 t 668(1 row) 669 670SELECT '4.0'::citext::float4 = 4.0 AS t; 671 t 672--- 673 t 674(1 row) 675 676SELECT 4.0::double precision = '4.0' AS t; 677 t 678--- 679 t 680(1 row) 681 682SELECT '4.0'::citext::double precision = 4.0 AS t; 683 t 684--- 685 t 686(1 row) 687 688SELECT 4.0::float8 = '4.0' AS t; 689 t 690--- 691 t 692(1 row) 693 694SELECT '4.0'::citext::float8 = 4.0 AS t; 695 t 696--- 697 t 698(1 row) 699 700SELECT 'foo'::name::citext = 'foo' AS t; 701 t 702--- 703 t 704(1 row) 705 706SELECT 'foo'::citext::name = 'foo'::name AS t; 707 t 708--- 709 t 710(1 row) 711 712SELECT 'f'::char::citext = 'f' AS t; 713 t 714--- 715 t 716(1 row) 717 718SELECT 'f'::citext::char = 'f'::char AS t; 719 t 720--- 721 t 722(1 row) 723 724SELECT 'f'::"char"::citext = 'f' AS t; 725 t 726--- 727 t 728(1 row) 729 730SELECT 'f'::citext::"char" = 'f'::"char" AS t; 731 t 732--- 733 t 734(1 row) 735 736SELECT '100'::money::citext = '$100.00' AS t; 737 t 738--- 739 t 740(1 row) 741 742SELECT '100'::citext::money = '100'::money AS t; 743 t 744--- 745 t 746(1 row) 747 748SELECT 'a'::char::citext = 'a' AS t; 749 t 750--- 751 t 752(1 row) 753 754SELECT 'a'::citext::char = 'a'::char AS t; 755 t 756--- 757 t 758(1 row) 759 760SELECT 'foo'::varchar::citext = 'foo' AS t; 761 t 762--- 763 t 764(1 row) 765 766SELECT 'foo'::citext::varchar = 'foo'::varchar AS t; 767 t 768--- 769 t 770(1 row) 771 772SELECT 'foo'::text::citext = 'foo' AS t; 773 t 774--- 775 t 776(1 row) 777 778SELECT 'foo'::citext::text = 'foo'::text AS t; 779 t 780--- 781 t 782(1 row) 783 784SELECT '192.168.100.128/25'::cidr::citext = '192.168.100.128/25' AS t; 785 t 786--- 787 t 788(1 row) 789 790SELECT '192.168.100.128/25'::citext::cidr = '192.168.100.128/25'::cidr AS t; 791 t 792--- 793 t 794(1 row) 795 796SELECT '192.168.100.128'::inet::citext = '192.168.100.128/32' AS t; 797 t 798--- 799 t 800(1 row) 801 802SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t; 803 t 804--- 805 t 806(1 row) 807 808SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t; 809 t 810--- 811 t 812(1 row) 813 814SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t; 815 t 816--- 817 t 818(1 row) 819 820SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t; 821 t 822--- 823 t 824(1 row) 825 826SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t; 827 t 828--- 829 t 830(1 row) 831 832SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t; 833 t 834--- 835 t 836(1 row) 837 838SELECT '1999-01-08 04:05:06'::citext::timestamptz = '1999-01-08 04:05:06'::timestamptz AS t; 839 t 840--- 841 t 842(1 row) 843 844SELECT '1 hour'::interval::citext = '1 hour'::interval::text AS t; 845 t 846--- 847 t 848(1 row) 849 850SELECT '1 hour'::citext::interval = '1 hour'::interval AS t; 851 t 852--- 853 t 854(1 row) 855 856SELECT '1999-01-08'::date::citext = '1999-01-08'::date::text AS t; 857 t 858--- 859 t 860(1 row) 861 862SELECT '1999-01-08'::citext::date = '1999-01-08'::date AS t; 863 t 864--- 865 t 866(1 row) 867 868SELECT '04:05:06'::time::citext = '04:05:06' AS t; 869 t 870--- 871 t 872(1 row) 873 874SELECT '04:05:06'::citext::time = '04:05:06'::time AS t; 875 t 876--- 877 t 878(1 row) 879 880SELECT '04:05:06'::timetz::citext = '04:05:06'::timetz::text AS t; 881 t 882--- 883 t 884(1 row) 885 886SELECT '04:05:06'::citext::timetz = '04:05:06'::timetz AS t; 887 t 888--- 889 t 890(1 row) 891 892SELECT '( 1 , 1)'::point::citext = '(1,1)' AS t; 893 t 894--- 895 t 896(1 row) 897 898SELECT '( 1 , 1)'::citext::point ~= '(1,1)'::point AS t; 899 t 900--- 901 t 902(1 row) 903 904SELECT '( 1 , 1 ) , ( 2 , 2 )'::lseg::citext = '[(1,1),(2,2)]' AS t; 905 t 906--- 907 t 908(1 row) 909 910SELECT '( 1 , 1 ) , ( 2 , 2 )'::citext::lseg = '[(1,1),(2,2)]'::lseg AS t; 911 t 912--- 913 t 914(1 row) 915 916SELECT '( 0 , 0 ) , ( 1 , 1 )'::box::citext = '(0,0),(1,1)'::box::text AS t; 917 t 918--- 919 t 920(1 row) 921 922SELECT '( 0 , 0 ) , ( 1 , 1 )'::citext::box ~= '(0,0),(1,1)'::text::box AS t; 923 t 924--- 925 t 926(1 row) 927 928SELECT '((0,0),(1,1),(2,0))'::path::citext = '((0,0),(1,1),(2,0))' AS t; 929 t 930--- 931 t 932(1 row) 933 934SELECT '((0,0),(1,1),(2,0))'::citext::path = '((0,0),(1,1),(2,0))'::path AS t; 935 t 936--- 937 t 938(1 row) 939 940SELECT '((0,0),(1,1))'::polygon::citext = '((0,0),(1,1))' AS t; 941 t 942--- 943 t 944(1 row) 945 946SELECT '((0,0),(1,1))'::citext::polygon ~= '((0,0),(1,1))'::polygon AS t; 947 t 948--- 949 t 950(1 row) 951 952SELECT '((0,0),2)'::circle::citext = '((0,0),2)'::circle::text AS t; 953 t 954--- 955 t 956(1 row) 957 958SELECT '((0,0),2)'::citext::circle ~= '((0,0),2)'::text::circle AS t; 959 t 960--- 961 t 962(1 row) 963 964SELECT '101'::bit::citext = '101'::bit::text AS t; 965 t 966--- 967 t 968(1 row) 969 970SELECT '101'::citext::bit = '101'::text::bit AS t; 971 t 972--- 973 t 974(1 row) 975 976SELECT '101'::bit varying::citext = '101'::bit varying::text AS t; 977 t 978--- 979 t 980(1 row) 981 982SELECT '101'::citext::bit varying = '101'::text::bit varying AS t; 983 t 984--- 985 t 986(1 row) 987 988SELECT 'a fat cat'::tsvector::citext = '''a'' ''cat'' ''fat''' AS t; 989 t 990--- 991 t 992(1 row) 993 994SELECT 'a fat cat'::citext::tsvector = 'a fat cat'::tsvector AS t; 995 t 996--- 997 t 998(1 row) 999 1000SELECT 'fat & rat'::tsquery::citext = '''fat'' & ''rat''' AS t; 1001 t 1002--- 1003 t 1004(1 row) 1005 1006SELECT 'fat & rat'::citext::tsquery = 'fat & rat'::tsquery AS t; 1007 t 1008--- 1009 t 1010(1 row) 1011 1012SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid::citext = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AS t; 1013 t 1014--- 1015 t 1016(1 row) 1017 1018SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext::uuid = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid AS t; 1019 t 1020--- 1021 t 1022(1 row) 1023 1024CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 1025SELECT 'sad'::mood::citext = 'sad' AS t; 1026 t 1027--- 1028 t 1029(1 row) 1030 1031SELECT 'sad'::citext::mood = 'sad'::mood AS t; 1032 t 1033--- 1034 t 1035(1 row) 1036 1037-- Assignment casts. 1038CREATE TABLE caster ( 1039 citext citext, 1040 text text, 1041 varchar varchar, 1042 bpchar bpchar, 1043 char char, 1044 chr "char", 1045 name name, 1046 bytea bytea, 1047 boolean boolean, 1048 float4 float4, 1049 float8 float8, 1050 numeric numeric, 1051 int8 int8, 1052 int4 int4, 1053 int2 int2, 1054 cidr cidr, 1055 inet inet, 1056 macaddr macaddr, 1057 money money, 1058 timestamp timestamp, 1059 timestamptz timestamptz, 1060 interval interval, 1061 date date, 1062 time time, 1063 timetz timetz, 1064 point point, 1065 lseg lseg, 1066 box box, 1067 path path, 1068 polygon polygon, 1069 circle circle, 1070 bit bit, 1071 bitv bit varying, 1072 tsvector tsvector, 1073 tsquery tsquery, 1074 uuid uuid 1075); 1076INSERT INTO caster (text) VALUES ('foo'::citext); 1077INSERT INTO caster (citext) VALUES ('foo'::text); 1078INSERT INTO caster (varchar) VALUES ('foo'::text); 1079INSERT INTO caster (text) VALUES ('foo'::varchar); 1080INSERT INTO caster (varchar) VALUES ('foo'::citext); 1081INSERT INTO caster (citext) VALUES ('foo'::varchar); 1082INSERT INTO caster (bpchar) VALUES ('foo'::text); 1083INSERT INTO caster (text) VALUES ('foo'::bpchar); 1084INSERT INTO caster (bpchar) VALUES ('foo'::citext); 1085INSERT INTO caster (citext) VALUES ('foo'::bpchar); 1086INSERT INTO caster (char) VALUES ('f'::text); 1087INSERT INTO caster (text) VALUES ('f'::char); 1088INSERT INTO caster (char) VALUES ('f'::citext); 1089INSERT INTO caster (citext) VALUES ('f'::char); 1090INSERT INTO caster (chr) VALUES ('f'::text); 1091INSERT INTO caster (text) VALUES ('f'::"char"); 1092INSERT INTO caster (chr) VALUES ('f'::citext); 1093INSERT INTO caster (citext) VALUES ('f'::"char"); 1094INSERT INTO caster (name) VALUES ('foo'::text); 1095INSERT INTO caster (text) VALUES ('foo'::name); 1096INSERT INTO caster (name) VALUES ('foo'::citext); 1097INSERT INTO caster (citext) VALUES ('foo'::name); 1098-- Cannot cast to bytea on assignment. 1099INSERT INTO caster (bytea) VALUES ('foo'::text); 1100ERROR: column "bytea" is of type bytea but expression is of type text 1101LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::text); 1102 ^ 1103HINT: You will need to rewrite or cast the expression. 1104INSERT INTO caster (text) VALUES ('foo'::bytea); 1105INSERT INTO caster (bytea) VALUES ('foo'::citext); 1106ERROR: column "bytea" is of type bytea but expression is of type citext 1107LINE 1: INSERT INTO caster (bytea) VALUES ('foo'::citext); 1108 ^ 1109HINT: You will need to rewrite or cast the expression. 1110INSERT INTO caster (citext) VALUES ('foo'::bytea); 1111-- Cannot cast to boolean on assignment. 1112INSERT INTO caster (boolean) VALUES ('t'::text); 1113ERROR: column "boolean" is of type boolean but expression is of type text 1114LINE 1: INSERT INTO caster (boolean) VALUES ('t'::text); 1115 ^ 1116HINT: You will need to rewrite or cast the expression. 1117INSERT INTO caster (text) VALUES ('t'::boolean); 1118INSERT INTO caster (boolean) VALUES ('t'::citext); 1119ERROR: column "boolean" is of type boolean but expression is of type citext 1120LINE 1: INSERT INTO caster (boolean) VALUES ('t'::citext); 1121 ^ 1122HINT: You will need to rewrite or cast the expression. 1123INSERT INTO caster (citext) VALUES ('t'::boolean); 1124-- Cannot cast to float8 on assignment. 1125INSERT INTO caster (float8) VALUES ('12.42'::text); 1126ERROR: column "float8" is of type double precision but expression is of type text 1127LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::text); 1128 ^ 1129HINT: You will need to rewrite or cast the expression. 1130INSERT INTO caster (text) VALUES ('12.42'::float8); 1131INSERT INTO caster (float8) VALUES ('12.42'::citext); 1132ERROR: column "float8" is of type double precision but expression is of type citext 1133LINE 1: INSERT INTO caster (float8) VALUES ('12.42'::citext); 1134 ^ 1135HINT: You will need to rewrite or cast the expression. 1136INSERT INTO caster (citext) VALUES ('12.42'::float8); 1137-- Cannot cast to float4 on assignment. 1138INSERT INTO caster (float4) VALUES ('12.42'::text); 1139ERROR: column "float4" is of type real but expression is of type text 1140LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::text); 1141 ^ 1142HINT: You will need to rewrite or cast the expression. 1143INSERT INTO caster (text) VALUES ('12.42'::float4); 1144INSERT INTO caster (float4) VALUES ('12.42'::citext); 1145ERROR: column "float4" is of type real but expression is of type citext 1146LINE 1: INSERT INTO caster (float4) VALUES ('12.42'::citext); 1147 ^ 1148HINT: You will need to rewrite or cast the expression. 1149INSERT INTO caster (citext) VALUES ('12.42'::float4); 1150-- Cannot cast to numeric on assignment. 1151INSERT INTO caster (numeric) VALUES ('12.42'::text); 1152ERROR: column "numeric" is of type numeric but expression is of type text 1153LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::text); 1154 ^ 1155HINT: You will need to rewrite or cast the expression. 1156INSERT INTO caster (text) VALUES ('12.42'::numeric); 1157INSERT INTO caster (numeric) VALUES ('12.42'::citext); 1158ERROR: column "numeric" is of type numeric but expression is of type citext 1159LINE 1: INSERT INTO caster (numeric) VALUES ('12.42'::citext); 1160 ^ 1161HINT: You will need to rewrite or cast the expression. 1162INSERT INTO caster (citext) VALUES ('12.42'::numeric); 1163-- Cannot cast to int8 on assignment. 1164INSERT INTO caster (int8) VALUES ('12'::text); 1165ERROR: column "int8" is of type bigint but expression is of type text 1166LINE 1: INSERT INTO caster (int8) VALUES ('12'::text); 1167 ^ 1168HINT: You will need to rewrite or cast the expression. 1169INSERT INTO caster (text) VALUES ('12'::int8); 1170INSERT INTO caster (int8) VALUES ('12'::citext); 1171ERROR: column "int8" is of type bigint but expression is of type citext 1172LINE 1: INSERT INTO caster (int8) VALUES ('12'::citext); 1173 ^ 1174HINT: You will need to rewrite or cast the expression. 1175INSERT INTO caster (citext) VALUES ('12'::int8); 1176-- Cannot cast to int4 on assignment. 1177INSERT INTO caster (int4) VALUES ('12'::text); 1178ERROR: column "int4" is of type integer but expression is of type text 1179LINE 1: INSERT INTO caster (int4) VALUES ('12'::text); 1180 ^ 1181HINT: You will need to rewrite or cast the expression. 1182INSERT INTO caster (text) VALUES ('12'::int4); 1183INSERT INTO caster (int4) VALUES ('12'::citext); 1184ERROR: column "int4" is of type integer but expression is of type citext 1185LINE 1: INSERT INTO caster (int4) VALUES ('12'::citext); 1186 ^ 1187HINT: You will need to rewrite or cast the expression. 1188INSERT INTO caster (citext) VALUES ('12'::int4); 1189-- Cannot cast to int2 on assignment. 1190INSERT INTO caster (int2) VALUES ('12'::text); 1191ERROR: column "int2" is of type smallint but expression is of type text 1192LINE 1: INSERT INTO caster (int2) VALUES ('12'::text); 1193 ^ 1194HINT: You will need to rewrite or cast the expression. 1195INSERT INTO caster (text) VALUES ('12'::int2); 1196INSERT INTO caster (int2) VALUES ('12'::citext); 1197ERROR: column "int2" is of type smallint but expression is of type citext 1198LINE 1: INSERT INTO caster (int2) VALUES ('12'::citext); 1199 ^ 1200HINT: You will need to rewrite or cast the expression. 1201INSERT INTO caster (citext) VALUES ('12'::int2); 1202-- Cannot cast to cidr on assignment. 1203INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::text); 1204ERROR: column "cidr" is of type cidr but expression is of type text 1205LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/... 1206 ^ 1207HINT: You will need to rewrite or cast the expression. 1208INSERT INTO caster (text) VALUES ('192.168.100.128/25'::cidr); 1209INSERT INTO caster (cidr) VALUES ('192.168.100.128/25'::citext); 1210ERROR: column "cidr" is of type cidr but expression is of type citext 1211LINE 1: INSERT INTO caster (cidr) VALUES ('192.168.100.128/... 1212 ^ 1213HINT: You will need to rewrite or cast the expression. 1214INSERT INTO caster (citext) VALUES ('192.168.100.128/25'::cidr); 1215-- Cannot cast to inet on assignment. 1216INSERT INTO caster (inet) VALUES ('192.168.100.128'::text); 1217ERROR: column "inet" is of type inet but expression is of type text 1218LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'... 1219 ^ 1220HINT: You will need to rewrite or cast the expression. 1221INSERT INTO caster (text) VALUES ('192.168.100.128'::inet); 1222INSERT INTO caster (inet) VALUES ('192.168.100.128'::citext); 1223ERROR: column "inet" is of type inet but expression is of type citext 1224LINE 1: INSERT INTO caster (inet) VALUES ('192.168.100.128'... 1225 ^ 1226HINT: You will need to rewrite or cast the expression. 1227INSERT INTO caster (citext) VALUES ('192.168.100.128'::inet); 1228-- Cannot cast to macaddr on assignment. 1229INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::text); 1230ERROR: column "macaddr" is of type macaddr but expression is of type text 1231LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0... 1232 ^ 1233HINT: You will need to rewrite or cast the expression. 1234INSERT INTO caster (text) VALUES ('08:00:2b:01:02:03'::macaddr); 1235INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:03'::citext); 1236ERROR: column "macaddr" is of type macaddr but expression is of type citext 1237LINE 1: INSERT INTO caster (macaddr) VALUES ('08:00:2b:01:02:0... 1238 ^ 1239HINT: You will need to rewrite or cast the expression. 1240INSERT INTO caster (citext) VALUES ('08:00:2b:01:02:03'::macaddr); 1241-- Cannot cast to money on assignment. 1242INSERT INTO caster (money) VALUES ('12'::text); 1243ERROR: column "money" is of type money but expression is of type text 1244LINE 1: INSERT INTO caster (money) VALUES ('12'::text); 1245 ^ 1246HINT: You will need to rewrite or cast the expression. 1247INSERT INTO caster (text) VALUES ('12'::money); 1248INSERT INTO caster (money) VALUES ('12'::citext); 1249ERROR: column "money" is of type money but expression is of type citext 1250LINE 1: INSERT INTO caster (money) VALUES ('12'::citext); 1251 ^ 1252HINT: You will need to rewrite or cast the expression. 1253INSERT INTO caster (citext) VALUES ('12'::money); 1254-- Cannot cast to timestamp on assignment. 1255INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::text); 1256ERROR: column "timestamp" is of type timestamp without time zone but expression is of type text 1257LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05... 1258 ^ 1259HINT: You will need to rewrite or cast the expression. 1260INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamp); 1261INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05:06'::citext); 1262ERROR: column "timestamp" is of type timestamp without time zone but expression is of type citext 1263LINE 1: INSERT INTO caster (timestamp) VALUES ('1999-01-08 04:05... 1264 ^ 1265HINT: You will need to rewrite or cast the expression. 1266INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamp); 1267-- Cannot cast to timestamptz on assignment. 1268INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::text); 1269ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type text 1270LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05... 1271 ^ 1272HINT: You will need to rewrite or cast the expression. 1273INSERT INTO caster (text) VALUES ('1999-01-08 04:05:06'::timestamptz); 1274INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05:06'::citext); 1275ERROR: column "timestamptz" is of type timestamp with time zone but expression is of type citext 1276LINE 1: INSERT INTO caster (timestamptz) VALUES ('1999-01-08 04:05... 1277 ^ 1278HINT: You will need to rewrite or cast the expression. 1279INSERT INTO caster (citext) VALUES ('1999-01-08 04:05:06'::timestamptz); 1280-- Cannot cast to interval on assignment. 1281INSERT INTO caster (interval) VALUES ('1 hour'::text); 1282ERROR: column "interval" is of type interval but expression is of type text 1283LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::text); 1284 ^ 1285HINT: You will need to rewrite or cast the expression. 1286INSERT INTO caster (text) VALUES ('1 hour'::interval); 1287INSERT INTO caster (interval) VALUES ('1 hour'::citext); 1288ERROR: column "interval" is of type interval but expression is of type citext 1289LINE 1: INSERT INTO caster (interval) VALUES ('1 hour'::citext)... 1290 ^ 1291HINT: You will need to rewrite or cast the expression. 1292INSERT INTO caster (citext) VALUES ('1 hour'::interval); 1293-- Cannot cast to date on assignment. 1294INSERT INTO caster (date) VALUES ('1999-01-08'::text); 1295ERROR: column "date" is of type date but expression is of type text 1296LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::tex... 1297 ^ 1298HINT: You will need to rewrite or cast the expression. 1299INSERT INTO caster (text) VALUES ('1999-01-08'::date); 1300INSERT INTO caster (date) VALUES ('1999-01-08'::citext); 1301ERROR: column "date" is of type date but expression is of type citext 1302LINE 1: INSERT INTO caster (date) VALUES ('1999-01-08'::cit... 1303 ^ 1304HINT: You will need to rewrite or cast the expression. 1305INSERT INTO caster (citext) VALUES ('1999-01-08'::date); 1306-- Cannot cast to time on assignment. 1307INSERT INTO caster (time) VALUES ('04:05:06'::text); 1308ERROR: column "time" is of type time without time zone but expression is of type text 1309LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::text)... 1310 ^ 1311HINT: You will need to rewrite or cast the expression. 1312INSERT INTO caster (text) VALUES ('04:05:06'::time); 1313INSERT INTO caster (time) VALUES ('04:05:06'::citext); 1314ERROR: column "time" is of type time without time zone but expression is of type citext 1315LINE 1: INSERT INTO caster (time) VALUES ('04:05:06'::citex... 1316 ^ 1317HINT: You will need to rewrite or cast the expression. 1318INSERT INTO caster (citext) VALUES ('04:05:06'::time); 1319-- Cannot cast to timetz on assignment. 1320INSERT INTO caster (timetz) VALUES ('04:05:06'::text); 1321ERROR: column "timetz" is of type time with time zone but expression is of type text 1322LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::text)... 1323 ^ 1324HINT: You will need to rewrite or cast the expression. 1325INSERT INTO caster (text) VALUES ('04:05:06'::timetz); 1326INSERT INTO caster (timetz) VALUES ('04:05:06'::citext); 1327ERROR: column "timetz" is of type time with time zone but expression is of type citext 1328LINE 1: INSERT INTO caster (timetz) VALUES ('04:05:06'::citex... 1329 ^ 1330HINT: You will need to rewrite or cast the expression. 1331INSERT INTO caster (citext) VALUES ('04:05:06'::timetz); 1332-- Cannot cast to point on assignment. 1333INSERT INTO caster (point) VALUES ('( 1 , 1)'::text); 1334ERROR: column "point" is of type point but expression is of type text 1335LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::text)... 1336 ^ 1337HINT: You will need to rewrite or cast the expression. 1338INSERT INTO caster (text) VALUES ('( 1 , 1)'::point); 1339INSERT INTO caster (point) VALUES ('( 1 , 1)'::citext); 1340ERROR: column "point" is of type point but expression is of type citext 1341LINE 1: INSERT INTO caster (point) VALUES ('( 1 , 1)'::citex... 1342 ^ 1343HINT: You will need to rewrite or cast the expression. 1344INSERT INTO caster (citext) VALUES ('( 1 , 1)'::point); 1345-- Cannot cast to lseg on assignment. 1346INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::text); 1347ERROR: column "lseg" is of type lseg but expression is of type text 1348LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ... 1349 ^ 1350HINT: You will need to rewrite or cast the expression. 1351INSERT INTO caster (text) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg); 1352INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::citext); 1353ERROR: column "lseg" is of type lseg but expression is of type citext 1354LINE 1: INSERT INTO caster (lseg) VALUES ('( 1 , 1 ) , ( 2 ... 1355 ^ 1356HINT: You will need to rewrite or cast the expression. 1357INSERT INTO caster (citext) VALUES ('( 1 , 1 ) , ( 2 , 2 )'::lseg); 1358-- Cannot cast to box on assignment. 1359INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::text); 1360ERROR: column "box" is of type box but expression is of type text 1361LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::te... 1362 ^ 1363HINT: You will need to rewrite or cast the expression. 1364INSERT INTO caster (text) VALUES ('(0,0),(1,1)'::box); 1365INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::citext); 1366ERROR: column "box" is of type box but expression is of type citext 1367LINE 1: INSERT INTO caster (box) VALUES ('(0,0),(1,1)'::ci... 1368 ^ 1369HINT: You will need to rewrite or cast the expression. 1370INSERT INTO caster (citext) VALUES ('(0,0),(1,1)'::box); 1371-- Cannot cast to path on assignment. 1372INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::text); 1373ERROR: column "path" is of type path but expression is of type text 1374LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,... 1375 ^ 1376HINT: You will need to rewrite or cast the expression. 1377INSERT INTO caster (text) VALUES ('((0,0),(1,1),(2,0))'::path); 1378INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,0))'::citext); 1379ERROR: column "path" is of type path but expression is of type citext 1380LINE 1: INSERT INTO caster (path) VALUES ('((0,0),(1,1),(2,... 1381 ^ 1382HINT: You will need to rewrite or cast the expression. 1383INSERT INTO caster (citext) VALUES ('((0,0),(1,1),(2,0))'::path); 1384-- Cannot cast to polygon on assignment. 1385INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::text); 1386ERROR: column "polygon" is of type polygon but expression is of type text 1387LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::... 1388 ^ 1389HINT: You will need to rewrite or cast the expression. 1390INSERT INTO caster (text) VALUES ('((0,0),(1,1))'::polygon); 1391INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::citext); 1392ERROR: column "polygon" is of type polygon but expression is of type citext 1393LINE 1: INSERT INTO caster (polygon) VALUES ('((0,0),(1,1))'::... 1394 ^ 1395HINT: You will need to rewrite or cast the expression. 1396INSERT INTO caster (citext) VALUES ('((0,0),(1,1))'::polygon); 1397-- Cannot cast to circle on assignment. 1398INSERT INTO caster (circle) VALUES ('((0,0),2)'::text); 1399ERROR: column "circle" is of type circle but expression is of type text 1400LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::text... 1401 ^ 1402HINT: You will need to rewrite or cast the expression. 1403INSERT INTO caster (text) VALUES ('((0,0),2)'::circle); 1404INSERT INTO caster (circle) VALUES ('((0,0),2)'::citext); 1405ERROR: column "circle" is of type circle but expression is of type citext 1406LINE 1: INSERT INTO caster (circle) VALUES ('((0,0),2)'::cite... 1407 ^ 1408HINT: You will need to rewrite or cast the expression. 1409INSERT INTO caster (citext) VALUES ('((0,0),2)'::circle); 1410-- Cannot cast to bit on assignment. 1411INSERT INTO caster (bit) VALUES ('101'::text); 1412ERROR: column "bit" is of type bit but expression is of type text 1413LINE 1: INSERT INTO caster (bit) VALUES ('101'::text); 1414 ^ 1415HINT: You will need to rewrite or cast the expression. 1416INSERT INTO caster (text) VALUES ('101'::bit); 1417INSERT INTO caster (bit) VALUES ('101'::citext); 1418ERROR: column "bit" is of type bit but expression is of type citext 1419LINE 1: INSERT INTO caster (bit) VALUES ('101'::citext); 1420 ^ 1421HINT: You will need to rewrite or cast the expression. 1422INSERT INTO caster (citext) VALUES ('101'::bit); 1423-- Cannot cast to bit varying on assignment. 1424INSERT INTO caster (bitv) VALUES ('101'::text); 1425ERROR: column "bitv" is of type bit varying but expression is of type text 1426LINE 1: INSERT INTO caster (bitv) VALUES ('101'::text); 1427 ^ 1428HINT: You will need to rewrite or cast the expression. 1429INSERT INTO caster (text) VALUES ('101'::bit varying); 1430INSERT INTO caster (bitv) VALUES ('101'::citext); 1431ERROR: column "bitv" is of type bit varying but expression is of type citext 1432LINE 1: INSERT INTO caster (bitv) VALUES ('101'::citext); 1433 ^ 1434HINT: You will need to rewrite or cast the expression. 1435INSERT INTO caster (citext) VALUES ('101'::bit varying); 1436-- Cannot cast to tsvector on assignment. 1437INSERT INTO caster (tsvector) VALUES ('the fat cat'::text); 1438ERROR: column "tsvector" is of type tsvector but expression is of type text 1439LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::te... 1440 ^ 1441HINT: You will need to rewrite or cast the expression. 1442INSERT INTO caster (text) VALUES ('the fat cat'::tsvector); 1443INSERT INTO caster (tsvector) VALUES ('the fat cat'::citext); 1444ERROR: column "tsvector" is of type tsvector but expression is of type citext 1445LINE 1: INSERT INTO caster (tsvector) VALUES ('the fat cat'::ci... 1446 ^ 1447HINT: You will need to rewrite or cast the expression. 1448INSERT INTO caster (citext) VALUES ('the fat cat'::tsvector); 1449-- Cannot cast to tsquery on assignment. 1450INSERT INTO caster (tsquery) VALUES ('fat & rat'::text); 1451ERROR: column "tsquery" is of type tsquery but expression is of type text 1452LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::text... 1453 ^ 1454HINT: You will need to rewrite or cast the expression. 1455INSERT INTO caster (text) VALUES ('fat & rat'::tsquery); 1456INSERT INTO caster (tsquery) VALUES ('fat & rat'::citext); 1457ERROR: column "tsquery" is of type tsquery but expression is of type citext 1458LINE 1: INSERT INTO caster (tsquery) VALUES ('fat & rat'::cite... 1459 ^ 1460HINT: You will need to rewrite or cast the expression. 1461INSERT INTO caster (citext) VALUES ('fat & rat'::tsquery); 1462-- Cannot cast to uuid on assignment. 1463INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text); 1464ERROR: column "uuid" is of type uuid but expression is of type text 1465LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e... 1466 ^ 1467HINT: You will need to rewrite or cast the expression. 1468INSERT INTO caster (text) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid); 1469INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::citext); 1470ERROR: column "uuid" is of type uuid but expression is of type citext 1471LINE 1: INSERT INTO caster (uuid) VALUES ('a0eebc99-9c0b-4e... 1472 ^ 1473HINT: You will need to rewrite or cast the expression. 1474INSERT INTO caster (citext) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid); 1475-- Table 9-5. SQL String Functions and Operators 1476SELECT 'D'::citext || 'avid'::citext = 'David'::citext AS citext_concat; 1477 citext_concat 1478--------------- 1479 t 1480(1 row) 1481 1482SELECT 'Value: '::citext || 42 = 'Value: 42' AS text_concat; 1483 text_concat 1484------------- 1485 t 1486(1 row) 1487 1488SELECT 42 || ': value'::citext ='42: value' AS int_concat; 1489 int_concat 1490------------ 1491 t 1492(1 row) 1493 1494SELECT bit_length('jose'::citext) = 32 AS t; 1495 t 1496--- 1497 t 1498(1 row) 1499 1500SELECT bit_length( name ) = bit_length( name::text ) AS t FROM srt; 1501 t 1502--- 1503 t 1504 t 1505 t 1506 t 1507(4 rows) 1508 1509SELECT textlen( name ) = textlen( name::text ) AS t FROM srt; 1510 t 1511--- 1512 t 1513 t 1514 t 1515 t 1516(4 rows) 1517 1518SELECT char_length( name ) = char_length( name::text ) AS t FROM srt; 1519 t 1520--- 1521 t 1522 t 1523 t 1524 t 1525(4 rows) 1526 1527SELECT lower( name ) = lower( name::text ) AS t FROM srt; 1528 t 1529--- 1530 t 1531 t 1532 t 1533 t 1534(4 rows) 1535 1536SELECT octet_length( name ) = octet_length( name::text ) AS t FROM srt; 1537 t 1538--- 1539 t 1540 t 1541 t 1542 t 1543(4 rows) 1544 1545SELECT overlay( name placing 'hom' from 2 for 4) = overlay( name::text placing 'hom' from 2 for 4) AS t FROM srt; 1546 t 1547--- 1548 t 1549 t 1550 t 1551 t 1552(4 rows) 1553 1554SELECT position( 'a' IN name ) = position( 'a' IN name::text ) AS t FROM srt; 1555 t 1556--- 1557 t 1558 t 1559 t 1560 t 1561(4 rows) 1562 1563SELECT substr('alphabet'::citext, 3) = 'phabet' AS t; 1564 t 1565--- 1566 t 1567(1 row) 1568 1569SELECT substr('alphabet'::citext, 3, 2) = 'ph' AS t; 1570 t 1571--- 1572 t 1573(1 row) 1574 1575SELECT substring('alphabet'::citext, 3) = 'phabet' AS t; 1576 t 1577--- 1578 t 1579(1 row) 1580 1581SELECT substring('alphabet'::citext, 3, 2) = 'ph' AS t; 1582 t 1583--- 1584 t 1585(1 row) 1586 1587SELECT substring('Thomas'::citext from 2 for 3) = 'hom' AS t; 1588 t 1589--- 1590 t 1591(1 row) 1592 1593SELECT substring('Thomas'::citext from 2) = 'homas' AS t; 1594 t 1595--- 1596 t 1597(1 row) 1598 1599SELECT substring('Thomas'::citext from '...$') = 'mas' AS t; 1600 t 1601--- 1602 t 1603(1 row) 1604 1605SELECT substring('Thomas'::citext from '%#"o_a#"_' for '#') = 'oma' AS t; 1606 t 1607--- 1608 t 1609(1 row) 1610 1611SELECT trim(' trim '::citext) = 'trim' AS t; 1612 t 1613--- 1614 t 1615(1 row) 1616 1617SELECT trim('xxxxxtrimxxxx'::citext, 'x'::citext) = 'trim' AS t; 1618 t 1619--- 1620 t 1621(1 row) 1622 1623SELECT trim('xxxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t; 1624 t 1625--- 1626 t 1627(1 row) 1628 1629SELECT trim('xxxxxtrimxxxx'::text, 'x'::citext) = 'trim' AS t; 1630 t 1631--- 1632 t 1633(1 row) 1634 1635SELECT upper( name ) = upper( name::text ) AS t FROM srt; 1636 t 1637--- 1638 t 1639 t 1640 t 1641 t 1642(4 rows) 1643 1644-- Table 9-6. Other String Functions. 1645SELECT ascii( name ) = ascii( name::text ) AS t FROM srt; 1646 t 1647--- 1648 t 1649 t 1650 t 1651 t 1652(4 rows) 1653 1654SELECT btrim(' trim'::citext ) = 'trim' AS t; 1655 t 1656--- 1657 t 1658(1 row) 1659 1660SELECT btrim('xxxxxtrimxxxx'::citext, 'x'::citext ) = 'trim' AS t; 1661 t 1662--- 1663 t 1664(1 row) 1665 1666SELECT btrim('xyxtrimyyx'::citext, 'xy'::citext) = 'trim' AS t; 1667 t 1668--- 1669 t 1670(1 row) 1671 1672SELECT btrim('xyxtrimyyx'::text, 'xy'::citext) = 'trim' AS t; 1673 t 1674--- 1675 t 1676(1 row) 1677 1678SELECT btrim('xyxtrimyyx'::citext, 'xy'::text ) = 'trim' AS t; 1679 t 1680--- 1681 t 1682(1 row) 1683 1684-- chr() takes an int and returns text. 1685-- convert() and convert_from take bytea and return text. 1686SELECT convert_from( name::bytea, 'SQL_ASCII' ) = convert_from( name::text::bytea, 'SQL_ASCII' ) AS t FROM srt; 1687 t 1688--- 1689 t 1690 t 1691 t 1692 t 1693(4 rows) 1694 1695SELECT decode('MTIzAAE='::citext, 'base64') = decode('MTIzAAE='::text, 'base64') AS t; 1696 t 1697--- 1698 t 1699(1 row) 1700 1701-- encode() takes bytea and returns text. 1702SELECT initcap('hi THOMAS'::citext) = initcap('hi THOMAS'::text) AS t; 1703 t 1704--- 1705 t 1706(1 row) 1707 1708SELECT length( name ) = length( name::text ) AS t FROM srt; 1709 t 1710--- 1711 t 1712 t 1713 t 1714 t 1715(4 rows) 1716 1717SELECT lpad('hi'::citext, 5 ) = ' hi' AS t; 1718 t 1719--- 1720 t 1721(1 row) 1722 1723SELECT lpad('hi'::citext, 5, 'xy'::citext) = 'xyxhi' AS t; 1724 t 1725--- 1726 t 1727(1 row) 1728 1729SELECT lpad('hi'::text, 5, 'xy'::citext) = 'xyxhi' AS t; 1730 t 1731--- 1732 t 1733(1 row) 1734 1735SELECT lpad('hi'::citext, 5, 'xy'::text ) = 'xyxhi' AS t; 1736 t 1737--- 1738 t 1739(1 row) 1740 1741SELECT ltrim(' trim'::citext ) = 'trim' AS t; 1742 t 1743--- 1744 t 1745(1 row) 1746 1747SELECT ltrim('zzzytrim'::citext, 'xyz'::citext) = 'trim' AS t; 1748 t 1749--- 1750 t 1751(1 row) 1752 1753SELECT ltrim('zzzytrim'::text, 'xyz'::citext) = 'trim' AS t; 1754 t 1755--- 1756 t 1757(1 row) 1758 1759SELECT ltrim('zzzytrim'::citext, 'xyz'::text ) = 'trim' AS t; 1760 t 1761--- 1762 t 1763(1 row) 1764 1765SELECT md5( name ) = md5( name::text ) AS t FROM srt; 1766 t 1767--- 1768 t 1769 t 1770 t 1771 t 1772(4 rows) 1773 1774-- pg_client_encoding() takes no args and returns name. 1775SELECT quote_ident( name ) = quote_ident( name::text ) AS t FROM srt; 1776 t 1777--- 1778 t 1779 t 1780 t 1781 t 1782(4 rows) 1783 1784SELECT quote_literal( name ) = quote_literal( name::text ) AS t FROM srt; 1785 t 1786--- 1787 t 1788 t 1789 t 1790 t 1791(4 rows) 1792 1793SELECT regexp_match('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t; 1794 t 1795--- 1796 t 1797(1 row) 1798 1799SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t; 1800 t 1801--- 1802 t 1803(1 row) 1804 1805SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t; 1806 t 1807--- 1808 t 1809(1 row) 1810 1811SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; 1812 t 1813--- 1814 t 1815(1 row) 1816 1817SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t; 1818 t 1819--- 1820 t 1821(1 row) 1822 1823SELECT regexp_match('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; 1824 t 1825--- 1826 t 1827(1 row) 1828 1829SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t; 1830 t 1831--- 1832 t 1833(1 row) 1834 1835-- c forces case-sensitive 1836SELECT regexp_match('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no result"; 1837 no result 1838----------- 1839 1840(1 row) 1841 1842-- g is not allowed 1843SELECT regexp_match('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g') AS "error"; 1844ERROR: regexp_match() does not support the "global" option 1845HINT: Use the regexp_matches function instead. 1846CONTEXT: SQL function "regexp_match" statement 1 1847SELECT regexp_matches('foobarbequebaz'::citext, '(bar)(beque)') = ARRAY[ 'bar', 'beque' ] AS t; 1848 t 1849--- 1850 t 1851(1 row) 1852 1853SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)') = ARRAY[ 'bar', 'beque' ] AS t; 1854 t 1855--- 1856 t 1857(1 row) 1858 1859SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext) = ARRAY[ 'bar', 'beque' ] AS t; 1860 t 1861--- 1862 t 1863(1 row) 1864 1865SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; 1866 t 1867--- 1868 t 1869(1 row) 1870 1871SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)', '') = ARRAY[ 'bar', 'beque' ] AS t; 1872 t 1873--- 1874 t 1875(1 row) 1876 1877SELECT regexp_matches('foobarbequebaz', '(BAR)(BEQUE)'::citext, '') = ARRAY[ 'bar', 'beque' ] AS t; 1878 t 1879--- 1880 t 1881(1 row) 1882 1883SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, ''::citext) = ARRAY[ 'bar', 'beque' ] AS t; 1884 t 1885--- 1886 t 1887(1 row) 1888 1889-- c forces case-sensitive 1890SELECT regexp_matches('foobarbequebaz'::citext, '(BAR)(BEQUE)'::citext, 'c'::citext) = ARRAY[ 'bar', 'beque' ] AS "no rows"; 1891 no rows 1892--------- 1893(0 rows) 1894 1895-- g allows multiple output rows 1896SELECT regexp_matches('foobarbequebazmorebarbequetoo'::citext, '(BAR)(BEQUE)'::citext, 'g'::citext) AS "two rows"; 1897 two rows 1898------------- 1899 {bar,beque} 1900 {bar,beque} 1901(2 rows) 1902 1903SELECT regexp_replace('Thomas'::citext, '.[mN]a.', 'M') = 'ThM' AS t; 1904 t 1905--- 1906 t 1907(1 row) 1908 1909SELECT regexp_replace('Thomas'::citext, '.[MN]A.', 'M') = 'ThM' AS t; 1910 t 1911--- 1912 t 1913(1 row) 1914 1915SELECT regexp_replace('Thomas', '.[MN]A.'::citext, 'M') = 'ThM' AS t; 1916 t 1917--- 1918 t 1919(1 row) 1920 1921SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M') = 'ThM' AS t; 1922 t 1923--- 1924 t 1925(1 row) 1926 1927-- c forces case-sensitive 1928SELECT regexp_replace('Thomas'::citext, '.[MN]A.'::citext, 'M', 'c') = 'Thomas' AS t; 1929 t 1930--- 1931 t 1932(1 row) 1933 1934SELECT regexp_split_to_array('hello world'::citext, E'\\s+') = ARRAY[ 'hello', 'world' ] AS t; 1935 t 1936--- 1937 t 1938(1 row) 1939 1940SELECT regexp_split_to_array('helloTworld'::citext, 't') = ARRAY[ 'hello', 'world' ] AS t; 1941 t 1942--- 1943 t 1944(1 row) 1945 1946SELECT regexp_split_to_array('helloTworld', 't'::citext) = ARRAY[ 'hello', 'world' ] AS t; 1947 t 1948--- 1949 t 1950(1 row) 1951 1952SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext) = ARRAY[ 'hello', 'world' ] AS t; 1953 t 1954--- 1955 t 1956(1 row) 1957 1958SELECT regexp_split_to_array('helloTworld'::citext, 't', 's') = ARRAY[ 'hello', 'world' ] AS t; 1959 t 1960--- 1961 t 1962(1 row) 1963 1964SELECT regexp_split_to_array('helloTworld', 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t; 1965 t 1966--- 1967 t 1968(1 row) 1969 1970SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 's') = ARRAY[ 'hello', 'world' ] AS t; 1971 t 1972--- 1973 t 1974(1 row) 1975 1976-- c forces case-sensitive 1977SELECT regexp_split_to_array('helloTworld'::citext, 't'::citext, 'c') = ARRAY[ 'helloTworld' ] AS t; 1978 t 1979--- 1980 t 1981(1 row) 1982 1983SELECT regexp_split_to_table('hello world'::citext, E'\\s+') AS words; 1984 words 1985------- 1986 hello 1987 world 1988(2 rows) 1989 1990SELECT regexp_split_to_table('helloTworld'::citext, 't') AS words; 1991 words 1992------- 1993 hello 1994 world 1995(2 rows) 1996 1997SELECT regexp_split_to_table('helloTworld', 't'::citext) AS words; 1998 words 1999------- 2000 hello 2001 world 2002(2 rows) 2003 2004SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext) AS words; 2005 words 2006------- 2007 hello 2008 world 2009(2 rows) 2010 2011-- c forces case-sensitive 2012SELECT regexp_split_to_table('helloTworld'::citext, 't'::citext, 'c') AS word; 2013 word 2014------------- 2015 helloTworld 2016(1 row) 2017 2018SELECT repeat('Pg'::citext, 4) = 'PgPgPgPg' AS t; 2019 t 2020--- 2021 t 2022(1 row) 2023 2024SELECT replace('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t; 2025 t 2026--- 2027 t 2028(1 row) 2029 2030SELECT replace('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t; 2031 t 2032--- 2033 t 2034(1 row) 2035 2036SELECT replace('ab^is$abcdef'::citext, '^is$', 'XX') = 'abXXabcdef' AS t; 2037 t 2038--- 2039 t 2040(1 row) 2041 2042SELECT replace('abcdefabcdef', 'cd'::citext, 'XX') = 'abXXefabXXef' AS t; 2043 t 2044--- 2045 t 2046(1 row) 2047 2048SELECT replace('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; 2049 t 2050--- 2051 t 2052(1 row) 2053 2054SELECT replace('ab^is$abcdef', '^is$'::citext, 'XX') = 'abXXabcdef' AS t; 2055 t 2056--- 2057 t 2058(1 row) 2059 2060SELECT replace('abcdefabcdef'::citext, 'cd'::citext, 'XX') = 'abXXefabXXef' AS t; 2061 t 2062--- 2063 t 2064(1 row) 2065 2066SELECT replace('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; 2067 t 2068--- 2069 t 2070(1 row) 2071 2072SELECT replace('ab^is$abcdef'::citext, '^is$'::citext, 'XX') = 'abXXabcdef' AS t; 2073 t 2074--- 2075 t 2076(1 row) 2077 2078SELECT rpad('hi'::citext, 5 ) = 'hi ' AS t; 2079 t 2080--- 2081 t 2082(1 row) 2083 2084SELECT rpad('hi'::citext, 5, 'xy'::citext) = 'hixyx' AS t; 2085 t 2086--- 2087 t 2088(1 row) 2089 2090SELECT rpad('hi'::text, 5, 'xy'::citext) = 'hixyx' AS t; 2091 t 2092--- 2093 t 2094(1 row) 2095 2096SELECT rpad('hi'::citext, 5, 'xy'::text ) = 'hixyx' AS t; 2097 t 2098--- 2099 t 2100(1 row) 2101 2102SELECT rtrim('trim '::citext ) = 'trim' AS t; 2103 t 2104--- 2105 t 2106(1 row) 2107 2108SELECT rtrim('trimxxxx'::citext, 'x'::citext) = 'trim' AS t; 2109 t 2110--- 2111 t 2112(1 row) 2113 2114SELECT rtrim('trimxxxx'::text, 'x'::citext) = 'trim' AS t; 2115 t 2116--- 2117 t 2118(1 row) 2119 2120SELECT rtrim('trimxxxx'::text, 'x'::text ) = 'trim' AS t; 2121 t 2122--- 2123 t 2124(1 row) 2125 2126SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t; 2127 t 2128--- 2129 t 2130(1 row) 2131 2132SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t; 2133 t 2134--- 2135 t 2136(1 row) 2137 2138SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t; 2139 t 2140--- 2141 t 2142(1 row) 2143 2144SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t; 2145 t 2146--- 2147 t 2148(1 row) 2149 2150SELECT strpos('high'::citext, 'gh' ) = 3 AS t; 2151 t 2152--- 2153 t 2154(1 row) 2155 2156SELECT strpos('high', 'gh'::citext) = 3 AS t; 2157 t 2158--- 2159 t 2160(1 row) 2161 2162SELECT strpos('high'::citext, 'gh'::citext) = 3 AS t; 2163 t 2164--- 2165 t 2166(1 row) 2167 2168SELECT strpos('high'::citext, 'GH' ) = 3 AS t; 2169 t 2170--- 2171 t 2172(1 row) 2173 2174SELECT strpos('high', 'GH'::citext) = 3 AS t; 2175 t 2176--- 2177 t 2178(1 row) 2179 2180SELECT strpos('high'::citext, 'GH'::citext) = 3 AS t; 2181 t 2182--- 2183 t 2184(1 row) 2185 2186-- to_ascii() does not support UTF-8. 2187-- to_hex() takes a numeric argument. 2188SELECT substr('alphabet', 3, 2) = 'ph' AS t; 2189 t 2190--- 2191 t 2192(1 row) 2193 2194SELECT translate('abcdefabcdef'::citext, 'cd', 'XX') = 'abXXefabXXef' AS t; 2195 t 2196--- 2197 t 2198(1 row) 2199 2200SELECT translate('abcdefabcdef'::citext, 'CD', 'XX') = 'abXXefabXXef' AS t; 2201 t 2202--- 2203 t 2204(1 row) 2205 2206SELECT translate('abcdefabcdef'::citext, 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; 2207 t 2208--- 2209 t 2210(1 row) 2211 2212SELECT translate('abcdefabcdef', 'CD'::citext, 'XX') = 'abXXefabXXef' AS t; 2213 t 2214--- 2215 t 2216(1 row) 2217 2218-- Table 9-20. Formatting Functions 2219SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) 2220 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; 2221 t 2222--- 2223 t 2224(1 row) 2225 2226SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY') 2227 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; 2228 t 2229--- 2230 t 2231(1 row) 2232 2233SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext) 2234 = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; 2235 t 2236--- 2237 t 2238(1 row) 2239 2240SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext) 2241 = to_number('12,454.8-', '99G999D9S') AS t; 2242 t 2243--- 2244 t 2245(1 row) 2246 2247SELECT to_number('12,454.8-'::citext, '99G999D9S') 2248 = to_number('12,454.8-', '99G999D9S') AS t; 2249 t 2250--- 2251 t 2252(1 row) 2253 2254SELECT to_number('12,454.8-', '99G999D9S'::citext) 2255 = to_number('12,454.8-', '99G999D9S') AS t; 2256 t 2257--- 2258 t 2259(1 row) 2260 2261SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) 2262 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; 2263 t 2264--- 2265 t 2266(1 row) 2267 2268SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY') 2269 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; 2270 t 2271--- 2272 t 2273(1 row) 2274 2275SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext) 2276 = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; 2277 t 2278--- 2279 t 2280(1 row) 2281 2282-- Try assigning function results to a column. 2283SELECT COUNT(*) = 8::bigint AS t FROM try; 2284 t 2285--- 2286 t 2287(1 row) 2288 2289INSERT INTO try 2290VALUES ( to_char( now()::timestamp, 'HH12:MI:SS') ), 2291 ( to_char( now() + '1 sec'::interval, 'HH12:MI:SS') ), -- timetamptz 2292 ( to_char( '15h 2m 12s'::interval, 'HH24:MI:SS') ), 2293 ( to_char( current_date, '999') ), 2294 ( to_char( 125::int, '999') ), 2295 ( to_char( 127::int4, '999') ), 2296 ( to_char( 126::int8, '999') ), 2297 ( to_char( 128.8::real, '999D9') ), 2298 ( to_char( 125.7::float4, '999D9') ), 2299 ( to_char( 125.9::float8, '999D9') ), 2300 ( to_char( -125.8::numeric, '999D99S') ); 2301SELECT COUNT(*) = 19::bigint AS t FROM try; 2302 t 2303--- 2304 t 2305(1 row) 2306 2307SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt; 2308 t 2309--- 2310 t 2311 t 2312 t 2313 t 2314(4 rows) 2315 2316SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt; 2317 t 2318--- 2319 t 2320 t 2321 t 2322 t 2323(4 rows) 2324 2325-- Ensure correct behavior for citext with materialized views. 2326CREATE TABLE citext_table ( 2327 id serial primary key, 2328 name citext 2329); 2330INSERT INTO citext_table (name) 2331 VALUES ('one'), ('two'), ('three'), (NULL), (NULL); 2332CREATE MATERIALIZED VIEW citext_matview AS 2333 SELECT * FROM citext_table; 2334CREATE UNIQUE INDEX citext_matview_id 2335 ON citext_matview (id); 2336SELECT * 2337 FROM citext_matview m 2338 FULL JOIN citext_table t ON (t.id = m.id AND t *= m) 2339 WHERE t.id IS NULL OR m.id IS NULL; 2340 id | name | id | name 2341----+------+----+------ 2342(0 rows) 2343 2344UPDATE citext_table SET name = 'Two' WHERE name = 'TWO'; 2345SELECT * 2346 FROM citext_matview m 2347 FULL JOIN citext_table t ON (t.id = m.id AND t *= m) 2348 WHERE t.id IS NULL OR m.id IS NULL; 2349 id | name | id | name 2350----+------+----+------ 2351 | | 2 | Two 2352 2 | two | | 2353(2 rows) 2354 2355REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview; 2356SELECT * FROM citext_matview ORDER BY id; 2357 id | name 2358----+------- 2359 1 | one 2360 2 | Two 2361 3 | three 2362 4 | 2363 5 | 2364(5 rows) 2365 2366-- test citext_pattern_cmp() function explicitly. 2367SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero; 2368 zero 2369------ 2370 0 2371(1 row) 2372 2373SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero; 2374 zero 2375------ 2376 0 2377(1 row) 2378 2379SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero; 2380 zero 2381------ 2382 0 2383(1 row) 2384 2385SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true; 2386 true 2387------ 2388 t 2389(1 row) 2390 2391SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true; 2392 true 2393------ 2394 t 2395(1 row) 2396 2397SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true; 2398 true 2399------ 2400 t 2401(1 row) 2402 2403SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true; 2404 true 2405------ 2406 t 2407(1 row) 2408 2409SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::citext) < 0 AS true; 2410 true 2411------ 2412 t 2413(1 row) 2414 2415-- test operator functions 2416-- lt 2417SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true; 2418 true 2419------ 2420 t 2421(1 row) 2422 2423SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true; 2424 true 2425------ 2426 t 2427(1 row) 2428 2429SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true; 2430 true 2431------ 2432 t 2433(1 row) 2434 2435SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false; 2436 false 2437------- 2438 f 2439(1 row) 2440 2441SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false; 2442 false 2443------- 2444 f 2445(1 row) 2446 2447SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false; 2448 false 2449------- 2450 f 2451(1 row) 2452 2453-- le 2454SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true; 2455 true 2456------ 2457 t 2458(1 row) 2459 2460SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true; 2461 true 2462------ 2463 t 2464(1 row) 2465 2466SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true; 2467 true 2468------ 2469 t 2470(1 row) 2471 2472SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true; 2473 true 2474------ 2475 t 2476(1 row) 2477 2478SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true; 2479 true 2480------ 2481 t 2482(1 row) 2483 2484SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true; 2485 true 2486------ 2487 t 2488(1 row) 2489 2490SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true; 2491 true 2492------ 2493 t 2494(1 row) 2495 2496SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false; 2497 false 2498------- 2499 f 2500(1 row) 2501 2502SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false; 2503 false 2504------- 2505 f 2506(1 row) 2507 2508SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false; 2509 false 2510------- 2511 f 2512(1 row) 2513 2514-- gt 2515SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false; 2516 false 2517------- 2518 f 2519(1 row) 2520 2521SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false; 2522 false 2523------- 2524 f 2525(1 row) 2526 2527SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false; 2528 false 2529------- 2530 f 2531(1 row) 2532 2533SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true; 2534 true 2535------ 2536 t 2537(1 row) 2538 2539SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true; 2540 true 2541------ 2542 t 2543(1 row) 2544 2545SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true; 2546 true 2547------ 2548 t 2549(1 row) 2550 2551-- ge 2552SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true; 2553 true 2554------ 2555 t 2556(1 row) 2557 2558SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true; 2559 true 2560------ 2561 t 2562(1 row) 2563 2564SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true; 2565 true 2566------ 2567 t 2568(1 row) 2569 2570SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true; 2571 true 2572------ 2573 t 2574(1 row) 2575 2576SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false; 2577 false 2578------- 2579 f 2580(1 row) 2581 2582SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false; 2583 false 2584------- 2585 f 2586(1 row) 2587 2588SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false; 2589 false 2590------- 2591 f 2592(1 row) 2593 2594SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true; 2595 true 2596------ 2597 t 2598(1 row) 2599 2600SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true; 2601 true 2602------ 2603 t 2604(1 row) 2605 2606SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true; 2607 true 2608------ 2609 t 2610(1 row) 2611 2612-- Multi-byte tests below are disabled like the sanity tests above. 2613-- Uncomment to run them. 2614-- Test ~<~ and ~<=~ 2615SELECT 'a'::citext ~<~ 'B'::citext AS t; 2616 t 2617--- 2618 t 2619(1 row) 2620 2621SELECT 'b'::citext ~<~ 'A'::citext AS f; 2622 f 2623--- 2624 f 2625(1 row) 2626 2627-- SELECT 'à'::citext ~<~ 'À'::citext AS f; 2628SELECT 'a'::citext ~<=~ 'B'::citext AS t; 2629 t 2630--- 2631 t 2632(1 row) 2633 2634SELECT 'a'::citext ~<=~ 'A'::citext AS t; 2635 t 2636--- 2637 t 2638(1 row) 2639 2640-- SELECT 'à'::citext ~<=~ 'À'::citext AS t; 2641-- Test ~>~ and ~>=~ 2642SELECT 'B'::citext ~>~ 'a'::citext AS t; 2643 t 2644--- 2645 t 2646(1 row) 2647 2648SELECT 'b'::citext ~>~ 'A'::citext AS t; 2649 t 2650--- 2651 t 2652(1 row) 2653 2654-- SELECT 'à'::citext ~>~ 'À'::citext AS f; 2655SELECT 'B'::citext ~>~ 'b'::citext AS f; 2656 f 2657--- 2658 f 2659(1 row) 2660 2661SELECT 'B'::citext ~>=~ 'b'::citext AS t; 2662 t 2663--- 2664 t 2665(1 row) 2666 2667-- SELECT 'à'::citext ~>=~ 'À'::citext AS t; 2668-- Test implicit casting. citext casts to text, but not vice-versa. 2669SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins. 2670 t 2671--- 2672 t 2673(1 row) 2674 2675SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins. 2676 t 2677--- 2678 t 2679(1 row) 2680 2681SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins. 2682 t 2683--- 2684 t 2685(1 row) 2686 2687SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins. 2688 t 2689--- 2690 t 2691(1 row) 2692 2693-- Test implicit casting. citext casts to varchar, but not vice-versa. 2694SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins. 2695 t 2696--- 2697 t 2698(1 row) 2699 2700SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins. 2701 t 2702--- 2703 t 2704(1 row) 2705 2706SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins. 2707 t 2708--- 2709 t 2710(1 row) 2711 2712SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins. 2713 t 2714--- 2715 t 2716(1 row) 2717 2718