--
--  Test citext datatype
--

--
-- first, define the datatype.  Turn off echoing so that expected file
-- does not depend on contents of citext.sql.
--
SET client_min_messages = warning;
\set ECHO none
\i citext.sql
\set ECHO all
RESET client_min_messages;

-- Test the operators and indexing functions

-- Test = and <>.
SELECT 'a'::citext = 'a'::citext as t;
SELECT 'a'::citext = 'A'::citext as t;
SELECT 'a'::citext = 'A'::text as f;		-- text wins the discussion
SELECT 'a'::citext = 'b'::citext as f;
SELECT 'a'::citext = 'ab'::citext as f;
SELECT 'a'::citext <> 'ab'::citext as t;

-- Test > and >=
SELECT 'B'::citext > 'a'::citext as t;
SELECT 'b'::citext > 'A'::citext as t;
SELECT 'B'::citext > 'b'::citext as f;
SELECT 'B'::citext >= 'b'::citext as t;

-- Test < and <=
SELECT 'a'::citext < 'B'::citext as t;
SELECT 'a'::citext <= 'B'::citext as t;

-- Test implicit casting. citext casts to text, but not vice-versa.
SELECT 'a'::citext = 'a'::text as t;
SELECT 'A'::text <> 'a'::citext as t;

SELECT 'aardvark'::citext = 'aardvark'::citext as t;
SELECT 'aardvark'::citext = 'aardVark'::citext as t;

-- Check the citext_cmp() function explicitly.
SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) as zero;
SELECT citext_cmp('aardvark'::citext, 'aardVark'::citext) as zero;
SELECT citext_cmp('B'::citext, 'a'::citext) as one;

-- Do some tests using a table and index.

CREATE TEMP TABLE try (
    name citext PRIMARY KEY
);

INSERT INTO try (name)
VALUES ('a'), ('ab'), ('aba'), ('b'), ('ba'), ('bab'), ('AZ');

SELECT name, 'a' = name from try;
SELECT name, 'a' = name from try where name = 'a';
SELECT name, 'A' = name from try;
SELECT name, 'A' = name from try where name = 'A';
SELECT name, 'A' = name from try where name = 'A';

-- expected failures on duplicate key
INSERT INTO try (name) VALUES ('a');
INSERT INTO try (name) VALUES ('A');
INSERT INTO try (name) VALUES ('aB');

-- Test aggregate functions and sort ordering

CREATE TEMP TABLE srt (
    name CITEXT
);

INSERT INTO srt (name)
VALUES ('aardvark'),
       ('AAA'),
       ('aba'),
       ('ABC'),
       ('abd');

-- Check the min() and max() aggregates, with and without index.
set enable_seqscan = off;
SELECT MIN(name) from srt;
SELECT MAX(name) from srt;
reset enable_seqscan;
set enable_indexscan = off;
SELECT MIN(name) from srt;
SELECT MAX(name) from srt;
reset enable_indexscan;

-- Check sorting likewise
set enable_seqscan = off;
SELECT name FROM srt ORDER BY name;
reset enable_seqscan;
set enable_indexscan = off;
SELECT name FROM srt ORDER BY name;
reset enable_indexscan;

-- Check LIKE
SELECT name FROM srt WHERE name LIKE '%a%' ORDER BY name;
SELECT name FROM srt WHERE name NOT LIKE '%a%' ORDER BY name;
SELECT name FROM srt WHERE name LIKE '%A%' ORDER BY name;
SELECT name FROM srt WHERE name NOT LIKE '%A%' ORDER BY name;

-- ~ should be case-insensitive
SELECT name FROM srt WHERE name ~ '%a%' ORDER BY name;
SELECT name FROM srt WHERE name !~ '%a%' ORDER BY name;
SELECT name FROM srt WHERE name ~ '%A%' ORDER BY name;
SELECT name FROM srt WHERE name !~ '%A%' ORDER BY name;