On Mon, 2010-08-02 at 12:27 -0400, Robert Haas wrote:
> I was also wondering if it would be worth adding some additional
> regression testing to contrib/btree_gist exercising this new
> functionality. Thoughts?
Sure. I attached two tests.
Regards,
Jeff Davis
*** a/contrib/btree_gist/Makefile
--- b/contrib/btree_gist/Makefile
***************
*** 11,17 **** DATA_built = btree_gist.sql
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
ifdef USE_PGXS
PG_CONFIG = pg_config
--- 11,17 ----
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 mixed
ifdef USE_PGXS
PG_CONFIG = pg_config
*** /dev/null
--- b/contrib/btree_gist/expected/mixed.out
***************
*** 0 ****
--- 1,31 ----
+ SET enable_seqscan = '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);
+ 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');
*** /dev/null
--- b/contrib/btree_gist/sql/mixed.sql
***************
*** 0 ****
--- 1,30 ----
+
+ SET enable_seqscan = '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);
+
+ SELECT * FROM test_ne WHERE a <> '2009-01-01' AND b <> 10.7;
+
+ -- test search for "not equals" using an exclusion constraint
+
+ CREATE TABLE zoo (
+ cage INTEGER,
+ animal TEXT,
+ EXCLUDE USING gist (cage WITH =, animal WITH <>)
+ );
+
+ INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT INTO zoo VALUES(123, 'zebra');
+ INSERT INTO zoo VALUES(123, 'lion');
+ INSERT INTO zoo VALUES(124, 'lion');
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
