How to SELECT data from a postgreSQL INDEX?
You can with pg_filedump utility
step 1. Installing pg_filedump in Debian
$ git clone git://git.postgresql.org/git/pg_filedump.git
$ cd pg_filedump/
$ make
$ make install
# note the line /usr/bin/install -c pg_filedump '/usr/lib/postgresql/17/bin'
# add env var
$ PATH=/usr/lib/postgresql/17/bin
# check that it works
$ pg_filedump -h
step 2. Finding path to the index file via SQL
SHOW data_directory;
-- /var/lib/postgresql/17/main
-- list your indices
SELECT *
FROM pg_indexes;
-- locate particular index
SELECT pg_relation_filepath('name_of_target_index');
-- let's say output is
-- base/123/4567
step 3. Look inside the index file
$ cd /var/lib/postgresql/17/main/base/123/
$ pg_filedump -D int,varchar 4567
Play with pg_filedump parameters to get nice visualization. Inspired by https://blog.dbi-services.com/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump/
You just can't. Not as a client, not using SQL.
Data in the index is internal to PostgreSQL, and it's not accessible to the outside world. You can introspect your index definitions (using pg_indexes
table or pg_get_indexdef
function), but you can't look up what's actually stored in those.
Well, you technically can find the file(s) in which the index data is stored (use pg_class.relfilenode
and checking for files in base/
subdirectory), and decode the binary data of their b-trees (or whatever your indexes use), but I'm not sure this is what you want to do. Unless you intend to learn or hack PostgreSQL internals.