0

I am managing a postgres db created by third parts.

One of the tables is described as

\d my_table;

Table "my_table"

...

Indexes:
    "my_table_pkey" PRIMARY KEY, btree (dt, ida, idm, idd, idt, idr)
    "my_table_fa" btree (dt, idd, idt, idfa, fnc)
    "my_table_typ_fnc" btree (dtr, idd, idt, typl, fnc, idb)

I understand the meaning of the first line of Indexes, and I know that in order to make it "appear in the table description", the code to run in CREATE TABLE is

...
PRIMARY KEY(dt, ida, idm, idd, idt, idr)
...

Bu what is the meaning of the other two lines, and which command should be run in CREATE TABLE (or ALTER TABLE) in order to apply them to the table / "to make them appear in the table description" ?

Tms91
  • 3,456
  • 6
  • 40
  • 74
  • The first line corresponds to an index which has been automatically created by pg from the primary key of the table, but it is not part of the table definition. The two other lines correspond to indexes which have been created after the table creation. None of these 3 indexes can be displayed in the table definition. See the manual [A](https://www.postgresql.org/docs/current/indexes-intro.html) and [B](https://www.postgresql.org/docs/current/sql-createindex.html) – Edouard Jan 03 '22 at 14:41
  • 1
    Thanks! If you want to post you comment as answer I will flag it as correct – Tms91 Jan 03 '22 at 14:50

1 Answers1

0

The two last lines indicate indexes, which were created after the table was created, with the following commands:

CREATE INDEX my_table_fa
ON my_table(dt, idd, idt, idfa, fnc);

CREATE INDEX my_table_typ_fnc 
ON my_table(dtr, idd, idt, typl, fnc, idb);

So they are not part of the CREATE TABLE command.

Tms91
  • 3,456
  • 6
  • 40
  • 74