복붙노트

[SQL] PostgreSQL의에서 인덱스 목록 열

SQL

PostgreSQL의에서 인덱스 목록 열

나는 인덱스는 PostgreSQL을에 켜져 있는지 열을 좀하고 싶습니다.

MySQL의에서 당신은 COLUMN_NAME 컬럼에 테이블과보기를위한 SHOW 인덱스를 사용할 수 있습니다.

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      |         | 
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

PostgreSQL를위한이 존재 같은 것을합니까?

나는 (SQL을 표시합니다 -E 옵션) 프롬프트 psql의 명령에 \ D를 해봤지만 내가 찾는 정보를 표시하지 않습니다.

업데이트 : 자신의 답변을 추가 한 모두에게 감사합니다. cope360는 내가 찾던하지만 여러 사람이 매우 유용한 링크에 울려 정확히 무엇을 나에게 주었다. 미래 참고로, (Milen A. Radev를 통해) PG_INDEX에 대한 설명서 및 (마이클 니클라스 통해) PostgreSQL의에서 매우 유용한 기사 추출 메타 정보를 확인하십시오.

해결법

  1. ==============================

    1.테스트 데이터를 생성 ...

    테스트 데이터를 생성 ...

    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));

    목록 인덱스 및 열 인덱스 :

    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

    열 이름을 롤 :

    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
  2. ==============================

    2.PostgreSQL의 (pg_indexes) :

    PostgreSQL의 (pg_indexes) :

    SELECT * FROM pg_indexes WHERE tablename = 'mytable';

    MySQL은 (SHOW INDEX) :

    SHOW INDEX FROM mytable;
  3. ==============================

    3.\ D TABLE_NAME 쇼 psql의에서이 정보,하지만 당신은 데이터베이스 사용 SQL에서 그러한 정보를 얻으려면 다음의 PostgreSQL에서 추출 메타 정보를 살펴 있습니다.

    \ D TABLE_NAME 쇼 psql의에서이 정보,하지만 당신은 데이터베이스 사용 SQL에서 그러한 정보를 얻으려면 다음의 PostgreSQL에서 추출 메타 정보를 살펴 있습니다.

    내가 테스트 및 생산 환경에서의 PostgreSQL 데이터베이스를 비교하기 위해 DB 스키마에서 일부 정보를보고 내 유틸리티 등의 정보를 사용합니다.

  4. ==============================

    4.그냥 할 : \ D 테이블 _

    그냥 할 : \ D 테이블 _

    그러나 나는 당신이 컬럼에 대한 정보가없는 것을 무엇을 의미합니까 모르겠어요.

    예를 들면 :

    # \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)

    그것은 분명 열 인덱스를 부여 쇼는이 테이블에 있습니다.

  5. ==============================

    5.easies과 짧은 방법은 현재 데이터베이스에있는 모든 인덱스를 나열합니다 \ 디입니다.

    easies과 짧은 방법은 현재 데이터베이스에있는 모든 인덱스를 나열합니다 \ 디입니다.

    $ \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)

    \ 디는 현재 데이터베이스의 모든 관계를 나열합니다 \ D 명령의 "작은 형제"입니다. 따라서 \ 디 확실히 "이 데이터베이스의 인덱스를 보여줘"의 약자입니다.

    타자 \ DIS는 물론 모든 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--

    모두이는 크기 디스크 공간 인덱스의 요구와 가능한 경우에 대한 설명과 같은 더 많은 정보를 얻을 후 당신은 +를 추가 할 수 있습니다 명령.

    $ \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)

    psql 프로그램에서 당신은 쉽게에 대한 명령을 입력 \? 도움을 찾을 수 있습니다

  6. ==============================

    6.다른 코드와 결합 뷰를 생성 :

    다른 코드와 결합 뷰를 생성 :

    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;
  7. ==============================

    7.일부 샘플 데이터 ...

    일부 샘플 데이터 ...

    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));

    pg_get_indexdef 기능을 사용 :

    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)
  8. ==============================

    8.이 너무 쇼를 테이블 변수, 인덱스 및 제약 조건의보기를 명령

    이 너무 쇼를 테이블 변수, 인덱스 및 제약 조건의보기를 명령

    =# \d table_name;

    예:

    testannie=# \d dv.l_customer_account;
  9. ==============================

    9.\ D TABLENAME 쇼 버전 8.3.8에 나를 위해 열 이름.

    \ D TABLENAME 쇼 버전 8.3.8에 나를 위해 열 이름.

     "username_idx" UNIQUE, btree (username), tablespace "alldata1"
  10. ==============================

    10.쿼리 결과 :

    쿼리 결과 :

    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)

    질문:

    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;
  11. ==============================

    11.원시 정보는 PG_INDEX입니다.

    원시 정보는 PG_INDEX입니다.

  12. ==============================

    12.인덱스의 열 순서를 유지하려면, 여기에 할 수있는 (매우 추한) 방법이있다 :

    인덱스의 열 순서를 유지하려면, 여기에 할 수있는 (매우 추한) 방법이있다 :

    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

    I가 배열 첨자으로 정렬되도록 열 순서는 pg_index.indkey 열에 저장된다.

  13. ==============================

    13.인덱스 주위를 재생할 때 열이 인덱스에 건설하는 순서는 열 자체로 중요하다.

    인덱스 주위를 재생할 때 열이 인덱스에 건설하는 순서는 열 자체로 중요하다.

    다음 쿼리 목록 정렬 방식으로 주어진 테이블의 모든 인덱스와 모든 열입니다.

    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
  14. ==============================

    14.필요한 인덱스의 드릴 다운 아래의 쿼리를 시도하십시오

    필요한 인덱스의 드릴 다운 아래의 쿼리를 시도하십시오

    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;
  15. ==============================

    15.

    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;
  16. ==============================

    16.여기 cope360의 답변을 래핑하는 함수는 다음과 같습니다

    여기 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;

    용법:

    select * from getIndices('<my_table>')
  17. ==============================

    17.어떻게 간단한 솔루션에 대한 :

    어떻게 간단한 솔루션에 대한 :

    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%'

    `

  18. ==============================

    18.@ cope360의 훌륭한 대답은, 조인 구문을 사용하도록 변환.

    @ cope360의 훌륭한 대답은, 조인 구문을 사용하도록 변환.

    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
;
  19. ==============================

    19.나는이 버전은 아직이 스레드에있는 생각하지 않습니다 : 그것은 인덱스에 대한 DDL과 함께 열 이름의 두 목록을 제공합니다.

    나는이 버전은 아직이 스레드에있는 생각하지 않습니다 : 그것은 인덱스에 대한 DDL과 함께 열 이름의 두 목록을 제공합니다.

    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;

    나는 기능을 사용하여 해당 인덱스가 있으므로 때때로 당신은 예를 들어, 목록 인덱스를 찾을 수, 열 이름에 연결하지 않습니다 발견 하나의 컬럼 이름이 때 실제로 사용 3입니다.

    예:

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

    쿼리 만 반환 인덱스의 컬럼에 'COL3'하지만, DDL 쇼 인덱스에 사용 된 컬럼의 전체 세트.

  20. ==============================

    20.@ Cope360의 좋은 답변을 확장합니다. 단지 테이블의 OID를 사용하여 특정 테이블 (같은 테이블 이름 만 다른 스키마가있다 넣다) 도착합니다.

    @ Cope360의 좋은 답변을 확장합니다. 단지 테이블의 OID를 사용하여 특정 테이블 (같은 테이블 이름 만 다른 스키마가있다 넣다) 도착합니다.

    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;

    설명 : 나는 두 스키마 'dbAsset'와 'dbLegal'테이블 이름 'tbassettype'을 가지고있다. dbLegal에 전용 테이블을 얻으려면, 단지 a.attrelid = 그 OID를 할 수 있습니다.

  21. ==============================

    21.조금 @ cope360의 답을 수정 :

    조금 @ 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;

    이것은 올바른 순서로 인덱스 열을 표시합니다 :

    index_name      is_unique  column_name
pk_test         true       c
pk_test         true       a
pk_test         true       b
  22. ==============================

    22.

    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)
  23. ==============================

    23.@ cope360에 의해 허용 대답은 좋다,하지만 난 좀 더 오라클의 DBA_IND_COLUMNS, ALL_IND_COLUMNS 및 USER_IND_COLUMNS 같은 것을 원했다 (예를 들면,보고 테이블 / 인덱스 스키마 및 다중 열 인덱스에서 인덱스의 위치), I는 허용 적응 때문에 이것으로 답 :

    @ cope360에 의해 허용 대답은 좋다,하지만 난 좀 더 오라클의 DBA_IND_COLUMNS, ALL_IND_COLUMNS 및 USER_IND_COLUMNS 같은 것을 원했다 (예를 들면,보고 테이블 / 인덱스 스키마 및 다중 열 인덱스에서 인덱스의 위치), I는 허용 적응 때문에 이것으로 답 :

    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
;

    이 같은 출력을 제공합니다 :

     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)

    24. from https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 테이블의 열 순서 걱정할 어떤 이유가 있나요?  (0) 2020.04.28
[SQL] 때 ... FOR UPDATE SELECT 사용 하는가?  (0) 2020.04.28
[SQL] 어떻게 공유 테이블 구조와 멀티 테넌트 (multi-tenant) 데이터베이스를 만드는 방법?  (0) 2020.04.28
[SQL] 테이블 이름 및 테이블의 스키마를 SQL Server 데이터베이스의 모든 트리거를 나열 할 필요  (0) 2020.04.28
[SQL] 어떻게 활성 SQL Server 연결을 볼 수 있습니까?  (0) 2020.04.28
,

티스토리툴바