Re: Show method of index

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.