Lista coloanelor cu indici în PostgreSQL (Programare, Sql, Postgresql, Indexare)

Luca Francl a intrebat.

Aș dori să obțin coloanele pe care se află un index în PostgreSQL.

În MySQL puteți utiliza SHOW INDEXES FOR table și să vă uitați la coloana Column_name coloană.

mysql> show indexes from foos;

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foos  |          0 | PRIMARY             |            1 | id          | A         |       19710 |     NULL | NULL   |      | BTREE      |         | 
| foos  |          0 | index_foos_on_email |            1 | email       | A         |       19710 |     NULL | NULL   | YES  | BTREE      |         | 
| foos  |          1 | index_foos_on_name  |            1 | name        | A         |       19710 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Există așa ceva pentru PostgreSQL?

Am încercat d la coloana psql prompt de comandă (cu -E opțiunea de a afișa SQL), dar nu arată informațiile pe care le caut.

Actualizare: Mulțumesc tuturor celor care au adăugat răspunsurile lor. cope360 mi-a dat exact ceea ce căutam, dar mai multe persoane au intervenit cu link-uri foarte utile. Pentru referințe viitoare, consultați documentația pentru pg_index (prin intermediul lui Milen A. Radev) și articolul foarte util Extragerea informațiilor META din PostgreSQL (prin Michał Niklas).

Comentarii

  • Doar pentru a clarifica: Doriți ca programul dvs. să fie capabil să își dea seama, în timpul execuției, ce coloane sunt indexate, corect? Spre deosebire de faptul că tu, programatorul, trebuie să știi. –  > Por Wayne Conrad.
  • Da, corect. În mod ideal, aș dori o instrucțiune SQL care să enumere NUMAI coloanele pe care se află indexul. Dar știu că PostgreSQL este mai complicat decât MySQL și că indexul ar putea fi pe o funcție etc. –  > Por Luke Francl.
23 răspunsuri
cope360

Creați niște date de test…

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

Enumerați indicii și coloanele indexate:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
order by
    t.relname,
    i.relname;

 table_name | index_name | column_name
------------+------------+-------------
 test       | pk_test    | a
 test       | pk_test    | b
 test2      | uk_test2   | b
 test2      | uk_test2   | c
 test3      | uk_test3ab | a
 test3      | uk_test3ab | b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

Rulează numele coloanelor:

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

 table_name | index_name | column_names
------------+------------+--------------
 test       | pk_test    | a, b
 test2      | uk_test2   | b, c
 test3      | uk_test3ab | a, b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

Comentarii

    28

  • Pentru oricine încearcă să găsească indici într-o bază de date populată: această interogare funcționează foarte bine, dar schimbați and t.relname like 'test%' cu tabelul (tabelele) pe care îl (le) doriți sau ștergeți complet linia respectivă pentru a găsi toți indicii din baza dvs. de date. –  > Por Erik J.
  • Ar putea cineva să explice ce înseamnă relkind='r' înseamnă? –  > Por Qwerty.
  • @Qwery, consultați documentația pentru pg_class r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table. –  > Por cope360.
  • există o modalitate de a spune și despre unicitatea cheii? –  > Por Andrew.
  • pentru a vedea unicitatea indexului, selectați și unicitatea ix.indisunique –  > Por Jana.
Valentin Podkamennyi

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL (SHOW INDEX):

SHOW INDEX FROM mytable;

Comentarii

  • Acesta este răspunsul cel mai simplu și cel mai interesant în ceea ce privește răspunsul la întrebarea „Este indexată coloana mea?”. PostgreSQL: SELECT COUNT(indexname) AS indexcount FROM pg_indexes WHERE tablename='mytablename' AND indexdef LIKE '%mycolumnname%' ; și verificați indexcount>0. mySQL: SHOW INDEX FROM mytablename WHERE Column_name='mycolumnname' ; și verifică dacă setul de rezultate nu este gol. –  > Por zerobandwidth.
  • Deși acesta este un răspuns foarte util în ceea ce privește obținerea rapidă de informații despre indici, nu răspunde la întrebarea inițială, deoarece pg_indexes vizualizarea nu oferă nume de coloane. postgresql.org/docs/current/view-pg-indexes.html –  > Por akagixxer.
Michał Niklas

d table_name arată această informație din psql, , dar dacă doriți să obțineți astfel de informații din baza de date folosind SQL, atunci aruncați o privire la Extragerea informațiilor META din PostgreSQL.

Eu folosesc astfel de informații în utilitar pentru a raporta unele informații din schema bazei de date pentru a compara bazele de date PostgreSQL în mediile de testare și de producție.

Comentarii

  • Link-ul dvs. privind extragerea informațiilor meta din Postgres este exact ceea ce căutam! Folosind sfaturile din acest fir de discuție și câteva săpături am ajuns destul de aproape de interogarea pe care o folosește în acea postare, dar este frumos să ai totul prezentat așa. –  > Por Luke Francl.
  • Folosesc AWS RDS PostgreSQL 9.6.5 și d table nu afișează niciun index, însă di arată toți indicii. –  > Por Hendy Irawan.
  • @HendyIrawan se pare că poate fi afectat de alte setări. De exemplu, mă întreb dacă ați avut activat modul „numai tupluri” (activat prin t). Cu „numai tuple” activat, nu primesc indici din d, , cu „numai tuple” dezactivat, primesc. Acest lucru se întâmplă cu psql (PostgreSQL) 9.6.15. –  > Por JMM.
user80168

Doar faceți: d table_name

Dar nu sunt sigur ce vrei să spui că informațiile despre coloane nu sunt acolo.

De exemplu:

# d pg_class
       Table "pg_catalog.pg_class"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 relname         | name      | not null
 relnamespace    | oid       | not null
 reltype         | oid       | not null
 reloftype       | oid       | not null
 relowner        | oid       | not null
 relam           | oid       | not null
 relfilenode     | oid       | not null
 reltablespace   | oid       | not null
 relpages        | integer   | not null
 reltuples       | real      | not null
 reltoastrelid   | oid       | not null
 reltoastidxid   | oid       | not null
 relhasindex     | boolean   | not null
 relisshared     | boolean   | not null
 relistemp       | boolean   | not null
 relkind         | "char"    | not null
 relnatts        | smallint  | not null
 relchecks       | smallint  | not null
 relhasoids      | boolean   | not null
 relhaspkey      | boolean   | not null
 relhasexclusion | boolean   | not null
 relhasrules     | boolean   | not null
 relhastriggers  | boolean   | not null
 relhassubclass  | boolean   | not null
 relfrozenxid    | xid       | not null
 relacl          | aclitem[] |
 reloptions      | text[]    |
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

Arată clar ce coloane dat indice este pe acest tabel.

Comentarii

  • Speram la ceva care să-mi permită să fac toți indicii pe un tabel, dar aveți dreptate, d index_name are aceste informații. Deci, pot să caut indicii pe un tabel, apoi să caut detaliile. Prin faptul că nu sunt afișate coloanele mă refer la faptul că m-am uitat la SQL-ul generat de d table nume și nu mi se pare evident de unde provine lista de coloane. Cred că este analizată din definiția indexului, ceea ce aș prefera să nu se întâmple. –  > Por Luke Francl.
  • Folosesc AWS RDS PostgreSQL 9.6.5 și d table nu afișează niciun index, însă di arată toți indicii. –  > Por Hendy Irawan.
sebisnow

# di

Cea mai ușoară și mai scurtă cale este di, , care va lista toți indicii din baza de date curentă.

$ di
                      List of relations
 Schema |            Name             | Type  |  Owner   |     Table     
--------+-----------------------------+-------+----------+---------------
 public | part_delivery_index         | index | shipper  | part_delivery
 public | part_delivery_pkey          | index | shipper  | part_delivery
 public | shipment_by_mandator        | index | shipper  | shipment_info
 public | shipment_by_number_and_size | index | shipper  | shipment_info
 public | shipment_info_pkey          | index | shipper  | shipment_info
(5 rows)

di este „fratele mai mic” al d care va lista toate relațiile din baza de date curentă database. Astfel, di înseamnă cu siguranță „arată-mi acest database indexuri”.

Tastarea diS va lista toți indicii utilizați la nivel de sistem, ceea ce înseamnă că veți obține și toți indicii pg_catalog.

$ diS
                                      List of relations
   Schema   |                   Name                    | Type  |  Owner   |          Table
------------+-------------------------------------------+-------+----------+-------------------------
 pg_catalog | pg_aggregate_fnoid_index                  | index | postgres | pg_aggregate
 pg_catalog | pg_am_name_index                          | index | postgres | pg_am
 pg_catalog | pg_am_oid_index                           | index | postgres | pg_am
 pg_catalog | pg_amop_fam_strat_index                   | index | postgres | pg_amop
 pg_catalog | pg_amop_oid_index                         | index | postgres | pg_amop
 pg_catalog | pg_amop_opr_fam_index                     | index | postgres | pg_amop
 pg_catalog | pg_amproc_fam_proc_index                  | index | postgres | pg_amproc
 pg_catalog | pg_amproc_oid_index                       | index | postgres | pg_amproc
 pg_catalog | pg_attrdef_adrelid_adnum_index            | index | postgres | pg_attrdef
--More-- 

Cu aceste două comenzi puteți adăuga un index + după ea pentru a obține și mai multe informații, cum ar fi dimensiunea – spațiul pe disc – de care are nevoie indexul și o descriere, dacă este disponibilă.

$ di+
                                 List of relations
 Schema |            Name             | Type  |  Owner   |     Table     | Size  | Description 
--------+-----------------------------+-------+----------+---------------+-------+-------------
 public | part_delivery_index         | index | shipper  | part_delivery | 16 kB | 
 public | part_delivery_pkey          | index | shipper  | part_delivery | 16 kB | 
 public | shipment_by_mandator        | index | shipper  | shipment_info | 19 MB | 
 public | shipment_by_number_and_size | index | shipper  | shipment_info | 19 MB | 
 public | shipment_info_pkey          | index | shipper  | shipment_info | 53 MB | 
(5 rows)

În psql puteți găsi cu ușurință ajutor despre comenzi tastând ?.

Comentarii

  • Dar nu arată numele coloanelor pe care sunt creați indicii. Indicele de chei primare compozite are multe coloane și acestea nu pot fi văzute. –  > Por Vignesh Raja.
naoko

Combinat cu codul altora și a creat o vizualizare:

CREATE OR REPLACE VIEW view_index AS 
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
WHERE c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname
    ,t.relname
    ,c.relname;

cope360

Câteva exemple de date…

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c), constraint uk_test3ab unique (a, b));

Utilizați pg_get_indexdef funcția:

select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test'::regclass;

                    pg_get_indexdef
--------------------------------------------------------
 CREATE UNIQUE INDEX pk_test ON test USING btree (a, b)
(1 row)


select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test2'::regclass;
                     pg_get_indexdef
----------------------------------------------------------
 CREATE UNIQUE INDEX uk_test2 ON test2 USING btree (b, c)
(1 row)


select pg_get_indexdef(indexrelid) from pg_index where indrelid ='test3'::regclass;
                      pg_get_indexdef
------------------------------------------------------------
 CREATE UNIQUE INDEX uk_test3b ON test3 USING btree (b)
 CREATE UNIQUE INDEX uk_test3c ON test3 USING btree (c)
 CREATE UNIQUE INDEX uk_test3ab ON test3 USING btree (a, b)
(3 rows)

Comentarii

  • Simplu și eficient! –  > Por David.
  • Pur și simplu minunat. Sunt norocos că am derulat până la acest răspuns. –  > Por greatvovan.
Dryymoon

REZULTATUL INTEROGĂRII:

table |     column     |          type          | notnull |  index_name  | is_index | primarykey | uniquekey | default
-------+----------------+------------------------+---------+--------------+----------+-   -----------+-----------+---------
 nodes | dns_datacenter | character varying(255) | f       |              | f        | f          | f         |
 nodes | dns_name       | character varying(255) | f       | dns_name_idx | t        | f          | f         |
 nodes | id             | uuid                   | t       | nodes_pkey   | t        | t          | t         |
(3 rows)

QUERY:

SELECT  
c.relname AS table,
f.attname AS column,  
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
f.attnotnull AS notnull,  
i.relname as index_name,
CASE  
    WHEN i.oid<>0 THEN 't'  
    ELSE 'f'  
END AS is_index,  
CASE  
    WHEN p.contype = 'p' THEN 't'  
    ELSE 'f'  
END AS primarykey,  
CASE  
    WHEN p.contype = 'u' THEN 't' 
    WHEN p.contype = 'p' THEN 't' 
    ELSE 'f'
END AS uniquekey,
CASE
    WHEN f.atthasdef = 't' THEN d.adsrc
END AS default  FROM pg_attribute f  
JOIN pg_class c ON c.oid = f.attrelid  
JOIN pg_type t ON t.oid = f.atttypid  
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid 
LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid 

WHERE c.relkind = 'r'::char  
AND n.nspname = 'public'  -- Replace with Schema name 
--AND c.relname = 'nodes'  -- Replace with table name, or Comment this for get all tables
AND f.attnum > 0
ORDER BY c.relname,f.attname;

Comentarii

  • frumos, însă numele „column” pentru o coloană este un cuvânt rezervat. IDEM pentru schemă, ar trebui să folosească numele coloanei –  > Por parisni.
Aryan

Aceste comenzi arată vizualizarea variabilelor tabelelor, a indicilor și a constrângerilor de asemenea

=# d table_name;

Exemplu:

testannie=# d dv.l_customer_account;

Corey

d tablename arată numele coloanelor pentru mine pe versiunea 8.3.8.

 "username_idx" UNIQUE, btree (username), tablespace "alldata1"

Milen A. Radev

Informațiile brute se găsesc în pg_index.

Comentarii

  • Interesant. În special indkey: „Aceasta este o matrice de valori indnatts care indică ce coloane de tabel indexează acest index. De exemplu, o valoare de 1 3 ar însemna că prima și a treia coloană din tabel constituie cheia indexului. Un zero în această matrice indică faptul că atributul de index corespunzător este o expresie asupra coloanelor din tabel, mai degrabă decât o simplă referință de coloană” – –  > Por Luke Francl.
user6654165

Atunci când vă jucați cu indicii, ordinea în care sunt construite coloanele în index este la fel de importantă ca și coloanele în sine.

Următoarea interogare enumeră toți indicii pentru o anumită tabelă și toate coloanele acestora în ordine.

SELECT
  table_name,
  index_name,
  string_agg(column_name, ',')
FROM (
       SELECT
         t.relname AS table_name,
         i.relname AS index_name,
         a.attname AS column_name,
         (SELECT i
          FROM (SELECT
                  *,
                  row_number()
                  OVER () i
                FROM unnest(indkey) WITH ORDINALITY AS a(v)) a
          WHERE v = attnum)
       FROM
         pg_class t,
         pg_class i,
         pg_index ix,
         pg_attribute a
       WHERE
         t.oid = ix.indrelid
         AND i.oid = ix.indexrelid
         AND a.attrelid = t.oid
         AND a.attnum = ANY (ix.indkey)
         AND t.relkind = 'r'
         AND t.relname LIKE 'tablename'
       ORDER BY table_name, index_name, i
     ) raw
GROUP BY table_name, index_name

Comentarii

  • De ce ar trebui ca OP să „încerce asta”? A răspuns bun va avea întotdeauna o explicație a ceea ce s-a făcut și de ce s-a făcut în acest fel, nu numai pentru OP, ci și pentru viitorii vizitatori ai SO care ar putea găsi această întrebare și care vor citi răspunsul dumneavoastră. –  > Por Maximilian Ast.
  • the i pentru ordinalitate este foarte șmecher. Se asigură că coloanele sunt enunțate în ordinea corectă. –  > Por kbrock.
  • Acesta a fost singurul răspuns care a funcționat pentru mine. Ordinea coloanelor este critică. (Dacă nu mă credeți, căutați toate persoanele cu prenumele Frank într-o agendă telefonică). –  > Por Juraj.
David Willis

Dacă doriți să păstrați ordinea coloanelor în index, iată o modalitate (foarte urâtă) de a face acest lucru:

select table_name,
    index_name,
    array_agg(column_name)
from (
    select
        t.relname as table_name,
        i.relname as index_name,
        a.attname as column_name,
        unnest(ix.indkey) as unn,
        a.attnum
    from
        pg_class t,
        pg_class i,
        pg_index ix,
        pg_attribute a
    where
        t.oid = ix.indrelid
        and i.oid = ix.indexrelid
        and a.attrelid = t.oid
        and a.attnum = ANY(ix.indkey)
        and t.relkind = 'r'
        and t.relnamespace = <oid of the schema you're interested in>
    order by
        t.relname,
        i.relname,
        generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name, index_name

ordinea coloanelor este stocată în coloana pg_index.indkey, așa că am ordonat după subscrierile din acel array.

Barath Ravichander

Vă rugăm să încercați interogarea de mai jos pentru a detalia până la indicii necesari

Interogare ca mai jos – am încercat acest lucru personal și îl folosesc frecvent.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' 
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner",
 c2.relname as "Table"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
      AND c2.relname like '%agg_transaction%' --table name
      AND nspname = 'edjus' -- schema name 
ORDER BY 1,2;

Nikhil

Similar cu răspunsul acceptat, dar având îmbinare la stânga pe pg_attribut ca o îmbinare normală sau o interogare cu pg_attribut nu oferă indici care sunt de tipul :
create unique index unique_user_name_index on users (lower(name))

select 
    row_number() over (order by c.relname),
    c.relname as index, 
    t.relname as table, 
    array_to_string(array_agg(a.attname), ', ') as column_names 
from pg_class c
join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%' 
join pg_class t on t.oid = i.indrelid
left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey) 
group by t.relname, c.relname order by c.relname;

Comentarii

  • notă bună, dar cum să obținem informații despre acel „lower(column_name”)” –  > Por pleerock.
chribsen

Iată un funcție care înfășoară răspunsul lui cope360:

CREATE OR REPLACE FUNCTION getIndices(_table_name varchar)
  RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$
  BEGIN
    RETURN QUERY
    select
    t.relname::varchar as table_name,
    i.relname::varchar as index_name,
    a.attname::varchar as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname = _table_name
order by
    t.relname,
    i.relname;
  END;
  $$ LANGUAGE plpgsql;

Utilizare:

select * from getIndices('<my_table>')

Comentarii

  • Nu am listat părțile din indexurile mele care utilizează funcții (de exemplu, „upper(field_name)”). –  > Por JohnMudd.
datico

Nu cred că această versiune există încă pe acest fir de discuție: oferă atât lista de nume de coloane, cât și ddl-ul pentru index.

CREATE OR REPLACE VIEW V_TABLE_INDEXES AS

SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,i.indisunique AS "is_unique"
    ,array_to_string(array_agg(a.attname), ', ') as "columns"
    ,pg_get_indexdef(i.indexrelid) as "ddl"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE c.relkind = 'i'
      and n.nspname not in ('pg_catalog', 'pg_toast')
      and pg_catalog.pg_table_is_visible(c.oid)
GROUP BY
    n.nspname
    ,t.relname
    ,c.relname
    ,i.indisunique
    ,i.indexrelid
ORDER BY
    n.nspname
    ,t.relname
    ,c.relname;

Am constatat că indicii care utilizează funcții nu fac legătura cu numele coloanelor, astfel încât, ocazional, puteți găsi un index care enumeră, de exemplu, un nume de coloană, când, de fapt, utilizează 3.

Exemplu:

CREATE INDEX ui1 ON table1 (coalesce(col1,''),coalesce(col2,''),col3)

Interogarea returnează doar „col3” ca o coloană din index, dar DDL-ul arată întregul set de coloane utilizate în index.

Alex

Ce zici de o soluție simplă:

SELECT 
  t.relname table_name,
  ix.relname index_name,
  indisunique,
  indisprimary, 
  regexp_replace(pg_get_indexdef(indexrelid), '.*((.*))', '1') columns
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE t.relname LIKE 'test%'

`

Comentarii

  • Îmi place această soluție. Din nefericire, eșuează cu indicii care au clauze where. (sau alte paranteze) –  > Por kbrock.
  • Am schimbat pentru a nu sări peste parantezele de la început și pentru a nu captura parantezele din mijloc și a renunța la tot ce urmează. '^[^)]*(([^)]*)).*$' –  > Por kbrock.
Christian Long

Răspunsul excelent al lui @cope360 ‘s, convertit pentru a utiliza sintaxa join.

select t.relname as table_name
     , i.relname as index_name
     , array_to_string(array_agg(a.attname), ', ') as column_names
from pg_class t
join pg_index ix
on t.oid = ix.indrelid
join pg_class i
on i.oid = ix.indexrelid
join pg_attribute a
on a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'test%'
group by t.relname
       , i.relname
order by t.relname
       , i.relname
;

Wutikrai

Extindeți la răspunsul bun al lui @Cope360. Pentru a obține o anumită tabelă (în cazul în care există același nume de tabel, dar schema este diferită), trebuie doar să utilizați OID-ul tabelului.

select
     t.relname as table_name
    ,i.relname as index_name
    ,a.attname as column_name
    ,a.attrelid tableid

from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    -- and t.relname like 'tbassettype'
    and a.attrelid = '"dbLegal".tbassettype'::regclass
order by
    t.relname,
    i.relname;

Explicați: Am un nume de tabel „tbassettype” în ambele scheme „dbAsset” și „dbLegal”. Pentru a obține doar tabelul din dbLegal, trebuie doar să las a.attrelid = OID-ul acestuia.

Nikita Ryanov

Un răspuns puțin modificat de @cope360:

create table test (a int, b int, c int, constraint pk_test primary key(c, a, b));
select i.relname as index_name,
       ix.indisunique as is_unique,
       a.attname as column_name,
from pg_class c
       inner join pg_index ix on c.oid=ix.indrelid
       inner join pg_class i on ix.indexrelid=i.oid
       inner join pg_attribute a on a.attrelid=c.oid and a.attnum=any(ix.indkey)
where c.oid='public.test'::regclass::oid
order by array_position(ix.indkey, a.attnum) asc;

Acest lucru va afișa coloanele indexului în ordinea corectă:

index_name      is_unique  column_name
pk_test         true       c
pk_test         true       a
pk_test         true       b

Comentarii

  • Folosind „left join pg_attribute” va arăta, de asemenea, indicii pe coloane calculate, bineînțeles cu un nume de coloană NULL. –  > Por Paolo Bonzini.
Guy Cohen
select t.relname as table_name, 
       i.relname as index_name, 
       array_position(ix.indkey,a.attnum) pos, 
       a.attname as column_name
from pg_class t
join pg_index ix on t.oid = ix.indrelid
join pg_class i on i.oid = ix.indexrelid
join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'orders'
order by t.relname, i.relname, array_position(ix.indkey,a.attnum)

StephenK

Răspunsul acceptat de @cope360 este bun, dar am vrut ceva mai asemănător cu DBA_IND_COLUMNS, ALL_IND_COLUMNS și USER_IND_COLUMNS de la Oracle (de exemplu, raportează schema tabelului/indexului și poziția indexului într-un index cu mai multe coloane), așa că am adaptat răspunsul acceptat în acest sens:

with
 ind_cols as (
select
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    1 + array_position(ix.indkey, a.attnum) as column_position
from
     pg_catalog.pg_class t
join pg_catalog.pg_attribute a on t.oid    =      a.attrelid 
join pg_catalog.pg_index ix    on t.oid    =     ix.indrelid
join pg_catalog.pg_class i     on a.attnum = any(ix.indkey)
                              and i.oid    =     ix.indexrelid
join pg_catalog.pg_namespace n on n.oid    =      t.relnamespace
where t.relkind = 'r'
order by
    t.relname,
    i.relname,
    array_position(ix.indkey, a.attnum)
)
select * 
from ind_cols
where schema_name = 'test'
  and table_name  = 'indextest'
order by schema_name, table_name
;

Acest lucru oferă o ieșire de tipul:

 schema_name | table_name | index_name | column_name | column_position 
-------------+------------+------------+-------------+-----------------
 test        | indextest  | testind1   | singleindex |               1
 test        | indextest  | testind2   | firstoftwo  |               1
 test        | indextest  | testind2   | secondoftwo |               2
(3 rows)