Comment puis-je obtenir la clé primaire(s) d'une table de Postgres via plpgsql?

Donné un nom de table, comment puis-je extraire une liste de colonnes de clé primaire et de leurs types de données à partir d'une fonction plpgsql?

InformationsquelleAutor jsight | 2009-07-31

28

La requête ci-dessus est très mauvais car il est vraiment lent.

Je recommande cette version officielle:

http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

si le schéma est nécessaire de la requête comme suit
```
SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
WHERE 
  pg_class.oid = 'foo'::regclass AND 
  indrelid = pg_class.oid AND 
  nspname = 'public' AND 
  pg_class.relnamespace = pg_namespace.oid AND 
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
 AND indisprimary
```
- Je suis sûr que vous n'avez pas besoin de se joindre avec pg_namespace. L'oid de la pg_class est unique donc quand vous le lancez à un regclass vous êtes déjà impliquant le nom de schéma. Si vous avez besoin du nom du schéma de simplement l'inclure dans le nom de la table: 'public.foo'::regclass.
- "La requête ci-dessus est très mauvais car il est vraiment lent." Veuillez éviter de faire référence à la position relative sur la page de d'autres réponses que celle-ci change au cours du temps et par la sélection de méthode de tri. La place la plus pérenne façon de se référer à une autre réponse consiste à lier à elle.
- Cela fonctionne si la table est faite sans oid?
InformationsquelleAutor user3094383

De fournir une droite bits de SQL, vous pouvez lister les colonnes de clé primaire et de leurs types avec:

SELECT c.column_name, c.data_type
FROM information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
  AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytable';

Quand je le lance sur mon serveur postgres, je reçois un jeu de résultats vide. Avant de vous demander, j'ai fait remplacer 'mytable' avec le bon nom de la table. Ai-je besoin d'être dans un contexte spécifique ou de la portée pour que cela fonctionne?

InformationsquelleAutor Jamie Love

Suivantes SQL instruction fonctionne pour moi:

SELECT a.attname
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tablename'::regclass
AND    i.indisprimary;

Il est pris directement à partir de ici.

InformationsquelleAutor BullyWiiPlaza

5

Prendre un coup d'oeil à pg_constraint système de de table. Ou information_schema.table_constraints vue si vous préférez rester à proximité de la norme SQL.

Pour un exemple complet se connecter à une base de données à l'aide de psql avec l'option "-E" et le type \d <some_table> - vous allez voir les requêtes utilisées dans la description d'un tableau.
- En outre, les combiner avec les données de pg_indexes, et vous devriez être assez bon. Vraiment une clé primaire est juste un index unique avec not null sur tous les champs.
InformationsquelleAutor Milen A. Radev

Méfiez-vous des indices lorsque l'ordre de la colonne diffère du tableau de l'ordre des colonnes. (c'est à dire si la clé primaire utilisée colonnes 3, 2 et 1)

La requête suivante est beaucoup plus complexe, mais retourne les colonnes dans l'ordre approprié. (Supprimer le " indisprimary clause d'obtenir les mêmes informations pour tous les index d'une table)

WITH ndx_list AS
(
    SELECT pg_index.indexrelid
      FROM pg_index, pg_class
     WHERE pg_class.relname = 'test_indices_table'
       AND pg_class.oid = pg_index.indrelid
       AND pg_index.indisprimary
), ndx_cols AS
(
   SELECT pg_class.relname AS index_name, UNNEST(i.indkey) AS col_ndx, i.indisunique, i.indisprimary
     FROM pg_class, pg_index i
    WHERE pg_class.oid = i.indexrelid
      AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)
)
  SELECT ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary,
         a.attname, format_type(a.atttypid, a.atttypmod), a.attnum
    FROM pg_class c, pg_attribute a
    JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
   WHERE c.oid = 'test_indices_table'::regclass
     AND a.attrelid = c.oid

InformationsquelleAutor jamesvl

La préservation de l'ordre des colonnes à l'aide de generate_subscripts:

SELECT
  a.attname,
  format_type(a.atttypid, a.atttypmod) 
FROM
  pg_attribute a
  JOIN (SELECT *, GENERATE_SUBSCRIPTS(indkey, 1) AS indkey_subscript FROM pg_index) AS i
    ON
      i.indisprimary
      AND i.indrelid = a.attrelid
      AND a.attnum = i.indkey[i.indkey_subscript]
WHERE
  a.attrelid = 'your_table'::regclass
ORDER BY
  i.indkey_subscript

InformationsquelleAutor snipsnipsnip

SELECT
   conrelid::regclass AS table_from,
   conname,
   pg_get_constraintdef ( c.oid )
FROM
   pg_constraint c
   JOIN pg_namespace n ON n.oid = c.connamespace
WHERE
   contype IN ( 'f', 'p ' )
   AND conrelid::regclass::TEXT IN ( 'foo' )

ORDER BY
   conrelid::regclass::TEXT,
   contype DESC

Cela vous donnera la liste des contraintes le long de avec la définition.

InformationsquelleAutor user2553316 Ns

0

Vous avez vraiment besoin de 2 tables système pour que:
- pg_constraint - vous informe sur les colonnes (en nombre) appartiennent à la clé primaire
- pg_attribute - traduit les numéros de colonne pour les noms de colonne
Remarque: les tables système peut changer entre les versions de PostgreSQL, mais il n'arrive pas souvent (en fait très rarement, voire pas du tout). Et contrairement à l'utilisation de la information_schema.table_constraints, vous n'avez pas besoin d'autorisations spéciales, il suffit de sélectionner sur la table. (Cela a été testé dans Postgres 10.6)
```
SELECT string_agg(a.attname, ', ') AS pk
FROM
    pg_constraint AS c
    CROSS JOIN LATERAL UNNEST(c.conkey) AS cols(colnum) -- conkey is a list of the columns of the constraint; so we split it into rows so that we can join all column numbers onto their names in pg_attribute
    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum
WHERE
    c.contype = 'p' -- p = primary key constraint
    AND c.conrelid = '<schemaname>.<tablename>'::REGCLASS; -- regclass will type the name of the object to its internal oid
```
InformationsquelleAutor Joe Samanek
0
```
\d tablename 
```
vous donnera la clé primaire d'infos avec d'autres renseignements connexes, tels que toutes les colonnes, leurs types, les index, contraintes, des règles, des déclencheurs etc.
Vous n'avez probablement pas besoin de toutes ces informations, mais il est le moyen le plus rapide pour obtenir tous les détails d'un coup d'oeil, voir plus de détails ici.

Il renvoie à quelque chose comme ceci:
```
    Table "public.tablename"
     Column |  Type   | Modifiers 
    --------+---------+-----------
     col1   | text    | not null
     col2   | numeric | 
     col3   | text    | 
     col4   | text    | 
     col5   | numeric | 
    Indexes:
        "tablename_pkey" PRIMARY KEY, btree (col1)
```
InformationsquelleAutor Konrad

Vous devez vous connecter pour publier un commentaire.