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.