postgresql psql command \d does not list indexes

Question

I created a table in PGSQL (version 13) using the following command:

db1=# create table temp2(
foo int PRIMARY KEY,
bar varchar(20) UNIQUE NOT NULL
);
CREATE TABLE

The \d or d+ command does not list the associated indexes for the table (contrary to what I gathered from reading various sites.)

db1=# \d temp2
 foo    | integer               |           | not null |
 bar    | character varying(20) |           | not null |

db1=# \d+ temp2
 foo    | integer               |           | not null |         | plain    |              |
 bar    | character varying(20) |           | not null |         | extended |              |

Is there a way I get list indexes associated with a table?

Thank you, Ahmed.

Works for me. What version of `psql` are you using? – Adrian Klaver Nov 13 '22 at 20:46 — Adrian Klaver, Nov 13 '22 at 20:46

score 3 · Accepted Answer · answered Nov 13 '22 at 20:55

You have tuples_only set to on.

 create table temp2(
foo int PRIMARY KEY,
bar varchar(20) UNIQUE NOT NULL
);

test(5432)=# \d temp2
                      Table "public.temp2"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 foo    | integer               |           | not null | 
 bar    | character varying(20) |           | not null | 
Indexes:
    "temp2_pkey" PRIMARY KEY, btree (foo)
    "temp2_bar_key" UNIQUE CONSTRAINT, btree (bar)

test(5432)=# \pset tuples_only on


test(5432)=# \d temp2
 foo    | integer               |           | not null | 
 bar    | character varying(20) |           | not null | 

test(5432)=# \pset tuples_only off
test(5432)=# \d temp2
                      Table "public.temp2"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 foo    | integer               |           | not null | 
 bar    | character varying(20) |           | not null | 
Indexes:
    "temp2_pkey" PRIMARY KEY, btree (foo)
    "temp2_bar_key" UNIQUE CONSTRAINT, btree (bar)

Verify by doing:

\pset
border                   1
columns                  0
csv_fieldsep             ','
expanded                 off
fieldsep                 '|'
fieldsep_zero            off
footer                   on
format                   aligned
linestyle                ascii
null                     'NULL'
numericlocale            off
pager                    1
pager_min_lines          0
recordsep                '\n'
recordsep_zero           off
tableattr                
title                    
tuples_only              on
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single

Nicely spotted. I couldn't figure out how you could lose half of your \d output — Richard Huxton, Nov 13 '22 at 22:42

score -1 · Answer 2 · answered Nov 13 '22 at 20:39

-1

You need \di to get the index information. Check the manual:

the letters E, i, m, s, t, and v stand for foreign table, index, materialized view, sequence, table, and view, respectively.

answered Nov 13 '22 at 20:39

Frank Heikens

117,544
24
142
135

Incorrect answer. `\di` is how you get all indexes in the databases. `\d(+)` will return the index(es) on the provided table. There is something else wrong in the OP's case. – Adrian Klaver Nov 13 '22 at 20:47

postgresql psql command \d does not list indexes

2 Answers2