Les colonnes de la liste avec les index dans PostgreSQL
Je voudrais obtenir les colonnes d'index qui est sur PostgreSQL.
Dans MySQL, vous pouvez utiliser SHOW INDEXES FOR table
et de regarder la Column_name
colonne.
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 | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
N'a rien de ce genre existent pour PostgreSQL?
J'ai essayé \d
à la psql
invite de commande (avec l' -E
option pour afficher SQL), mais il n'affiche pas les informations que je cherche.
Mise à jour: Merci à tous ceux qui ont ajouté leurs réponses. cope360 m'a donné exactement ce que je cherchais, mais plusieurs personnes se sont mises à sonner avec très liens utiles. Pour référence future, consultez la documentation de pg_index (via Milen A. Radev) et l'article très utile L'extraction des MÉTA-données de PostgreSQL (via Michał Niklas).
- Juste pour clarifier: Vous voulez que votre programme pour être en mesure de déterminer, au moment de l'exécution, les colonnes sont indexées, droit? Contrairement à vous, la programmation de savoir.
- Oui, c'est exact. Idéalement, je veux une instruction SQL qui répertorie UNIQUEMENT les colonnes de l'index est sur. Mais je sais que PostgreSQL est plus compliqué que MySQL et l'index pourrait être sur une fonction, etc.
Vous devez vous connecter pour publier un commentaire.
Créer des données de test...
Liste des index et des colonnes indexées:
Retrousser les noms de colonne:
and t.relname like 'test%'
ligne de la table(s) que vous voulez, ou effacer cette ligne pour trouver tous les indices dans votre base de données.relkind='r'
signifie?r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table
.SELECT DISTINCT
au lieu d'éviter les doublons (dans le cas où vous avez plusieurs schémas dans un Postgres DB avec la même structure et que vous ne voulez pas voir la même entrée pour chaque locataire)ix.indisunique
ANY
rend les colonnes de sortir dans un funky commande. d'autres solutions de régler cette question par une sous-sélection et ajout de la création d'une séquence deindkey
\d table_name
montre cette information à partir depsql
, mais si vous voulez obtenir de telles informations de la base de données en utilisant SQL puis un coup d'oeil à L'extraction des MÉTA-données de PostgreSQL.- Je utiliser ces infos dans mon utilitaire de déclarer certaines informations db schéma de comparer les bases de données PostgreSQL dans l'essai et les environnements de production.
\d table
ne montre pas tout les indices, cependant\di
n'afficher tous les indices.PostgreSQL (pg_indexes):
MySQL (VOIR L'INDEX):
SELECT COUNT(indexname) AS indexcount FROM pg_indexes WHERE tablename='mytablename' AND indexdef LIKE '%mycolumnname%' ;
et vérifierindexcount>0
. mySQL:SHOW INDEX FROM mytablename WHERE Column_name='mycolumnname' ;
et de vérifier le résultat d'ensemble n'est pas vide.pg_indexes
vue ne fournit pas les noms de colonne. postgresql.org/docs/current/view-pg-indexes.htmlViens de faire:
\d table_name
Mais je ne suis pas sûr de ce que vous voulez dire que les informations sur les colonnes n'est pas là.
Par exemple:
Il montre clairement les colonnes index est sur cette table.
\d index_name
ne disposent de l'information. Donc, je peux regarder l'index sur une table, puis vérifier les détails. En ne montrant pas les colonnes, je veux dire que j'ai regardé le code SQL généré par\d table
nom et il n'est pas évident pour moi, où la colonne de la liste des vient de. Je pense que c'est d'être analysé de la définition de l'index, que je préfère ne pas le faire.\d table
ne montre pas tout les indices, cependant\di
n'afficher tous les indices.# \di
La easies et le plus court chemin est
\di
, qui fournira la liste de tous les index de la base de données actuelle.\di
est le "petit frère" de la\d
de commande qui liste l'ensemble des relations de l'actuel database. Ainsi\di
certainement stand pour "montrez-moi ce databases jendexes".Tapant
\diS
liste de tous les indices utilisés à l'échelle du système, ce qui signifie que vous obtenez tous les pg_catalog ainsi les index.Avec ces deux commandes, vous pouvez ajouter un
+
après pour obtenir encore plus d'informations, comme la taille de l'espace disque de l'indice de besoins et une description si disponible.Dans psql, vous pouvez facilement trouver de l'aide sur les commandes à taper
\?
.Combiné avec d'autres code et créé une vue:
Quelques exemples de données...
Utilisation
pg_get_indexdef
fonction:Cette commande montre la vue de variables de tables, les index et les contraintes trop
Exemple:
\d tablename
montre les noms de colonne pour moi sur la version 8.3.8.RÉSULTAT DE LA REQUÊTE:
REQUÊTE:
Les premières info est pg_index.
indkey
: "C'est un tableau de indnatts valeurs qui indiquent les colonnes de la table de cet index index. Par exemple, une valeur de 1 à 3 signifie que la première et la troisième colonnes du tableau constituent la clé de l'index. Un zéro dans ce tableau indique que l'index correspondant attribut est une expression sur les colonnes de la table, plutôt qu'une simple colonne de référence"Si vous souhaitez conserver l'ordre des colonnes dans l'index, voici un (très laid) façon de le faire:
ordre de la colonne est stockée dans le pg_index.indkey colonne, donc je l'ai commandé par les indices du tableau.
En jouant avec des indices de l'ordre dans lequel les colonnes sont construits dans l'index est aussi important que les colonnes elles-mêmes.
La requête suivante répertorie tous les index d'une table donnée, et tous leurs colonnes triées de la mode.
i
pour la ordinality est très lisse. Il assure les colonnes sont indiquées dans le bon ordre.S'il vous plaît essayer la requête ci-dessous pour explorer le nécessaire de l'indice
Voici un fonction qui encapsule cope360 réponse:
Utilisation:
Quelle est la solution la plus simple:
`
'^[^\)]*\(([^\)]*)\).*$'
@cope360 's réponse excellent, converti à l'utilisation de la syntaxe de jointure.
Je ne pense pas que cette version existe sur ce fil encore: il fournit la liste des noms de colonnes avec le ddl pour l'index.
J'ai trouvé que l'index à l'aide de fonctions de ne pas accéder à des noms de colonne, de sorte occasionnellement, vous trouverez un index de liste par exemple, un nom de colonne quand, en fait, est utilise 3.
Exemple:
La requête renvoie seulement "col3" comme une colonne de l'index, mais le DDL montre l'ensemble des colonnes utilisées dans l'indice.
Étendre à une bonne réponse de @Cope360. Pour obtenir pour certaines tables ( au cas où il leur est le même nom de table, mais différent du schéma ), juste à l'aide de la table OID.
Expliquer : j'ai nom de la table 'tbassettype" à la fois dans le schéma "dbAsset" et "dbLegal'. Pour obtenir uniquement la table sur dbLegal, il suffit de laisser un.attrelid = son OID.
Un peu modifié la réponse de @cope360:
Cela permettra de montrer les colonnes d'index dans l'ordre correct:
La accepté de répondre à par @cope360 est bon, mais je voulais quelque chose d'un peu plus comme Oracle DBA_IND_COLUMNS, ALL_IND_COLUMNS, et USER_IND_COLUMNS (par exemple, les rapports de la table/schéma de l'index et de la position de l'index un index multicolonne), j'ai donc adapté la accepté de répondre à ceci:
Cela donne une sortie comme: