My sincere apologies for flooding your mailboxes once again, as the patch attached in the previous post was incorrect. Also, I had failed to show test-cases of \d <index> in both 8.4 and 8.3 servers.
Attached are the test cases for psql connecting to 8.4 and 8.3. psql (8.4beta1) Type "help" for help. postgres=# CREATE TABLE foo(a int, b text); CREATE TABLE postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); CREATE INDEX postgres=# postgres=# \div List of relations Schema | Name | Type | Owner | Table | Method | Definition --------+-------------------+-------+-------+-------+--------+------------------------------- public | idx_foo_bt_ab | index | rubik | foo | btree | a,b public | idx_foo_bt_fooi | index | rubik | foo | btree | md5((a)::text), md5((a || b)) public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b)) public | idx_foo_hash | index | rubik | foo | hash | a public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a)) (5 rows) postgres=# \di idx_foo_bt_ab; List of relations Schema | Name | Type | Owner | Table | Method | Definition --------+---------------+-------+-------+-------+--------+------------ public | idx_foo_bt_ab | index | rubik | foo | btree | a,b (1 row) postgres=# \d idx_foo_bt_ab; Index "public.idx_foo_bt_ab" Column | Type | Definition --------+---------+------------ a | integer | a b | text | b btree, for table "public.foo" postgres=# \di idx_foo_bt_fooi; List of relations Schema | Name | Type | Owner | Table | Method | Definition --------+-----------------+-------+-------+-------+--------+------------------------------- public | idx_foo_bt_fooi | index | rubik | foo | btree | md5((a)::text), md5((a || b)) (1 row) postgres=# \d idx_foo_bt_fooi; Index "public.idx_foo_bt_fooi" Column | Type | Definition -----------------+------+-------------- pg_expression_1 | text | md5(a::text) pg_expression_2 | text | md5(a || b) btree, for table "public.foo" postgres=# psql (8.4beta1, server 8.3.6) WARNING: psql version 8.4, server version 8.3. Some psql features might not work. Type "help" for help. postgres=# CREATE TABLE foo(a int, b text); CREATE TABLE postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); CREATE INDEX postgres=# \div; List of relations Schema | Name | Type | Owner | Table | Method | Definition --------+-------------------+-------+----------+-------+--------+------------------------------------------------------ public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b) public | idx_foo_bt_fooi | index | postgres | foo | btree | md5((a)::text), md5((a || b)) public | idx_foo_bt_func | index | postgres | foo | btree | md5((a || b)) public | idx_foo_hash | index | postgres | foo | hash | CREATE INDEX idx_foo_hash ON foo USING hash (a) public | idx_foo_hash_func | index | postgres | foo | hash | md5((b || a)) (5 rows) postgres=# \di idx_foo_bt_ab; List of relations Schema | Name | Type | Owner | Table | Method | Definition --------+---------------+-------+----------+-------+--------+------------------------------------------------------ public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b) (1 row) postgres=# \d idx_foo_bt_ab; Index "public.idx_foo_bt_ab" Column | Type | Definition --------+---------+------------ a | integer | a b | text | b btree, for table "public.foo" postgres=# \di idx_foo_bt_fooi; List of relations Schema | Name | Type | Owner | Table | Method | Definition --------+-----------------+-------+----------+-------+--------+------------------------------- public | idx_foo_bt_fooi | index | postgres | foo | btree | md5((a)::text), md5((a || b)) (1 row) postgres=# \d idx_foo_bt_fooi; Index "public.idx_foo_bt_fooi" Column | Type | Definition -----------------+------+-------------- pg_expression_1 | text | md5(a::text) pg_expression_2 | text | md5(a || b) btree, for table "public.foo" postgres=# -- Regards, Khee Chin.
index.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers