btree_gist_test_ne.diff
text/x-patch
Filename: btree_gist_test_ne.diff
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: context
| File | + | − |
|---|---|---|
| contrib/btree_gist/expected/mixed.out | 31 | 0 |
| contrib/btree_gist/Makefile | 1 | 0 |
| contrib/btree_gist/sql/mixed.sql | 30 | 0 |
*** 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');