Add record_image_ops opclass for matview concurrent refresh.

[postgresql] / contrib / citext / sql / citext.sql

diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql
index 428f6e1cfc2d70ded3466d5021b40340a629239f..8a3532bea59533f8367e6ec96d4d0f63d5ed7f2c 100644 (file)
--- a/contrib/citext/sql/citext.sql
+++ b/contrib/citext/sql/citext.sql
@@ -2,15 +2,7 @@
 --  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
-RESET client_min_messages;
-\set ECHO all
+CREATE EXTENSION citext;
 
 -- Test the operators and indexing functions
 
@@ -90,7 +82,7 @@ SELECT 'aardvark'::citext = 'aardVark'::citext AS t;
 SELECT citext_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
 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;
+SELECT citext_cmp('B'::citext, 'a'::citext) > 0 AS true;
 
 -- Do some tests using a table and index.
 
@@ -135,6 +127,8 @@ VALUES ('aardvark'),
        ('ABC'),
        ('abd');
 
+CREATE INDEX srt_name ON srt (name);
+
 -- Check the min() and max() aggregates, with and without index.
 set enable_seqscan = off;
 SELECT MIN(name) AS "AAA" FROM srt;
@@ -220,8 +214,11 @@ SELECT '4.0'::citext::float8 = 4.0 AS t;
 SELECT 'foo'::name::citext = 'foo' AS t;
 SELECT 'foo'::citext::name = 'foo'::name AS t;
 
-SELECT 'foo'::bytea::citext = 'foo' AS t;
-SELECT 'foo'::citext::bytea = 'foo'::bytea AS t;
+SELECT 'f'::char::citext = 'f' AS t;
+SELECT 'f'::citext::char = 'f'::char AS t;
+
+SELECT 'f'::"char"::citext = 'f' AS t;
+SELECT 'f'::citext::"char" = 'f'::"char" AS t;
 
 SELECT '100'::money::citext = '$100.00' AS t;
 SELECT '100'::citext::money = '100'::money AS t;
@@ -244,9 +241,6 @@ SELECT '192.168.100.128'::citext::inet = '192.168.100.128'::inet AS t;
 SELECT '08:00:2b:01:02:03'::macaddr::citext = '08:00:2b:01:02:03' AS t;
 SELECT '08:00:2b:01:02:03'::citext::macaddr = '08:00:2b:01:02:03'::macaddr AS t;
 
-SELECT '<p>foo</p>'::xml::citext = '<p>foo</p>' AS t;
-SELECT '<p>foo</p>'::citext::xml::text = '<p>foo</p>'::xml::text AS t;
-
 SELECT '1999-01-08 04:05:06'::timestamp::citext = '1999-01-08 04:05:06'::timestamp::text AS t;
 SELECT '1999-01-08 04:05:06'::citext::timestamp = '1999-01-08 04:05:06'::timestamp AS t;
 SELECT '1999-01-08 04:05:06'::timestamptz::citext = '1999-01-08 04:05:06'::timestamptz::text AS t;
@@ -300,7 +294,9 @@ CREATE TABLE caster (
     text        text,
     varchar     varchar,
     bpchar      bpchar,
-    name        name,    
+    char        char,
+    chr         "char",
+    name        name,
     bytea       bytea,
     boolean     boolean,
     float4      float4,
@@ -309,10 +305,9 @@ CREATE TABLE caster (
     int8        int8,
     int4        int4,
     int2        int2,
-    cidr        cidr,   
+    cidr        cidr,
     inet        inet,
     macaddr     macaddr,
-    xml         xml,
     money       money,
     timestamp   timestamp,
     timestamptz timestamptz,
@@ -346,6 +341,16 @@ INSERT INTO caster (text)          VALUES ('foo'::bpchar);
 INSERT INTO caster (bpchar)        VALUES ('foo'::citext);
 INSERT INTO caster (citext)        VALUES ('foo'::bpchar);
 
+INSERT INTO caster (char)          VALUES ('f'::text);
+INSERT INTO caster (text)          VALUES ('f'::char);
+INSERT INTO caster (char)          VALUES ('f'::citext);
+INSERT INTO caster (citext)        VALUES ('f'::char);
+
+INSERT INTO caster (chr)           VALUES ('f'::text);
+INSERT INTO caster (text)          VALUES ('f'::"char");
+INSERT INTO caster (chr)           VALUES ('f'::citext);
+INSERT INTO caster (citext)        VALUES ('f'::"char");
+
 INSERT INTO caster (name)          VALUES ('foo'::text);
 INSERT INTO caster (text)          VALUES ('foo'::name);
 INSERT INTO caster (name)          VALUES ('foo'::citext);
@@ -417,12 +422,6 @@ INSERT INTO caster (text)          VALUES ('08:00:2b:01:02:03'::macaddr);
 INSERT INTO caster (macaddr)       VALUES ('08:00:2b:01:02:03'::citext);
 INSERT INTO caster (citext)        VALUES ('08:00:2b:01:02:03'::macaddr);
 
--- Cannot cast to xml on assignment.
-INSERT INTO caster (xml)           VALUES ('<p>foo</p>'::text);
-INSERT INTO caster (text)          VALUES ('<p>foo</p>'::xml);
-INSERT INTO caster (xml)           VALUES ('<p>foo</p>'::citext);
-INSERT INTO caster (citext)        VALUES ('<p>foo</p>'::xml);
-
 -- Cannot cast to money on assignment.
 INSERT INTO caster (money)         VALUES ('12'::text);
 INSERT INTO caster (text)          VALUES ('12'::money);
@@ -712,3 +711,26 @@ SELECT COUNT(*) = 19::bigint AS t FROM try;
 
 SELECT like_escape( name, '' ) = like_escape( name::text, '' ) AS t FROM srt;
 SELECT like_escape( name::text, ''::citext ) = like_escape( name::text, '' ) AS t FROM srt;
+
+-- Ensure correct behavior for citext with materialized views.
+CREATE TABLE citext_table (
+  id serial primary key,
+  name citext
+);
+INSERT INTO citext_table (name)
+  VALUES ('one'), ('two'), ('three'), (NULL), (NULL);
+CREATE MATERIALIZED VIEW citext_matview AS
+  SELECT * FROM citext_table;
+CREATE UNIQUE INDEX citext_matview_id
+  ON citext_matview (id);
+SELECT *
+  FROM citext_matview m
+  FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+  WHERE t.id IS NULL OR m.id IS NULL;
+UPDATE citext_table SET name = 'Two' WHERE name = 'TWO';
+SELECT *
+  FROM citext_matview m
+  FULL JOIN citext_table t ON (t.id = m.id AND t *= m)
+  WHERE t.id IS NULL OR m.id IS NULL;
+REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
+SELECT * FROM citext_matview ORDER BY id;