MODULE_big = btree_gist

DATA = uninstall_btree_gist.sql

REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz time timetz\

date interval macaddr inet cidr text varchar char bytea bit varbit numeric

date interval macaddr inet cidr text varchar char bytea bit varbit numeric not_equal

PG_CONFIG = pg_config

SET enable_seqscan to false;

-- test search for "not equals"

CREATE TABLE test_ne (

a TIMESTAMP,

b NUMERIC

);

CREATE INDEX test_ne_idx ON test_ne USING gist (a, b);

INSERT INTO test_ne SELECT '2009-01-01', 10.7 FROM generate_series(1,1000);

INSERT INTO test_ne VALUES('2007-02-03', -91.3);

INSERT INTO test_ne VALUES('2011-09-01', 43.7);

INSERT INTO test_ne SELECT '2009-01-01', 10.7 FROM generate_series(1,1000);

EXPLAIN (COSTS OFF) SELECT * FROM test_ne WHERE a <> '2009-01-01' AND b <> 10.7;

QUERY PLAN

------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on test_ne

Recheck Cond: ((a <> 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) AND (b <> 10.7))

-> Bitmap Index Scan on test_ne_idx

Index Cond: ((a <> 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) AND (b <> 10.7))

(4 rows)

SELECT * FROM test_ne WHERE a <> '2009-01-01' AND b <> 10.7;

a | b

--------------------------+-------

Sat Feb 03 00:00:00 2007 | -91.3

Thu Sep 01 00:00:00 2011 | 43.7

(2 rows)

-- test search for "not equals" using an exclusion constraint

CREATE TABLE zoo (

cage INTEGER,

animal TEXT,

EXCLUDE USING gist (cage WITH =, animal WITH <>)

);

NOTICE: CREATE TABLE / EXCLUDE will create implicit index "zoo_cage_animal_excl" for table "zoo"

INSERT INTO zoo VALUES(123, 'zebra');

INSERT INTO zoo VALUES(123, 'zebra');

INSERT INTO zoo VALUES(123, 'lion');

ERROR: conflicting key value violates exclusion constraint "zoo_cage_animal_excl"

DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).

INSERT INTO zoo VALUES(124, 'lion');

SET enable_seqscan to false;

CREATETABLEtest_ne (

aTIMESTAMP,

bNUMERIC

);

CREATEINDEXtest_ne_idxON test_ne USING gist (a, b);

INSERT INTO test_neSELECT'2009-01-01',10.7FROM generate_series(1,1000);

INSERT INTO test_neVALUES('2007-02-03',-91.3);

INSERT INTO test_neVALUES('2011-09-01',43.7);

INSERT INTO test_neSELECT'2009-01-01',10.7FROM generate_series(1,1000);

EXPLAIN (COSTS OFF)SELECT*FROM test_neWHERE a<>'2009-01-01'AND b<>10.7;

SELECT*FROM test_neWHERE a<>'2009-01-01'AND b<>10.7;

CREATETABLEzoo (

cageINTEGER,

animalTEXT,

EXCLUDE USING gist (cage WITH=, animal WITH<>)

);

INSERT INTO zooVALUES(123,'zebra');

INSERT INTO zooVALUES(123,'zebra');

INSERT INTO zooVALUES(123,'lion');

INSERT INTO zooVALUES(124,'lion');