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
– Adrian Klaver

2022-11-13 20:46:02 +00:00
Commented Nov 13, 2022 at 20:46 — Adrian Klaver
– Adrian Klaver, Commented Nov 13, 2022 at 20:46

Adrian Klaver · Accepted Answer · 2024-01-16 21:58:52Z

5

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

edited Jan 16, 2024 at 21:58

answered Nov 13, 2022 at 20:55

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richard Huxton Over a year ago

Nicely spotted. I couldn't figure out how you could lose half of your \d output

Ahmed A Over a year ago

@Adrian Klaver Thank you very much, that was exactly it.

Kemin Zhou Over a year ago

Anyone knows where we can put the config into a user HOME? Somehow, my previous version by default shows the full info with the slash d command. Now it is tuples_only.

Adrian Klaver Over a year ago

@KeminZhou. In psql read the section under Files

Frank Heikens · Accepted Answer · 2022-11-13 20:39:11Z

-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, 2022 at 20:39

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

Adrian Klaver Over a year ago

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.

Collectives™ on Stack Overflow

postgresql psql command \d does not list indexes

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related