List all primary keys (PKs) and their columns in PostgreSQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2018-12-12

Table of Contents:


    Query below lists all primary keys constraints (PK) in the database with their columns (one row per column).

    See also: list of all primary keys (one row per PK).

    Query

    select kcu.table_schema,
           kcu.table_name,
           tco.constraint_name,
           kcu.ordinal_position as position,
           kcu.column_name as key_column
    from information_schema.table_constraints tco
    join information_schema.key_column_usage kcu 
         on kcu.constraint_name = tco.constraint_name
         and kcu.constraint_schema = tco.constraint_schema
         and kcu.constraint_name = tco.constraint_name
    where tco.constraint_type = 'PRIMARY KEY'
    order by kcu.table_schema,
             kcu.table_name,
             position;
    

    Columns

    • table_schema - PK schema name
    • table_name - PK table name
    • constraint_name - PK constraint name
    • position - index of column in table (1, 2, ...). 2 or higher means key is composite (contains more than one column)
    • key_column - PK column name

    Rows

    • One row represents one primary key column
    • Scope of rows: columns of all PK constraints in a database
    • Ordered by table schema, table name, column position

    Sample results

    You could also get this

    Get this interactive HTML data dictionary in minutes with Dataedo.

    See live HTML data dictionary sample

    Try for free

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept