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