How to find primary column name in Postgresql

In this Postgresql tutorial, we will learn about “How to find primary column name in Postgresql“, primary columns are columns that uniquely identify each record in the table. And we will cover the following.

• How to find primary key column name in Postgresql
• How to find primary column name using pgAdmin

How to find primary key column name in Postgresql

In Postgresql, to get the primary key column name, we need to know about two catalogs.

This catalog has a number of attributes that help in retrieving lots of different information related to the table columns like indexes, datatype, etc.

• One is the pg_index and the other is the pg_attribute

Use the below command to get the primary column name in Postgresql.

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 = 'address'::regclass
AND    i.indisprimary;

pg_attribute: The catalog with the name “pg_attribute” stores information regarding the table columns. There will be exactly one “pg_attribute” row for every column in every table in the database.

Some attributes of pg_attribute are:

• attname : It shows column name.
• attrelid : The table this column belongs to.
• attnum : The number of the column.

pg_index: The catalog “pg_index” stores part of the information about indexes.

Some attributes of pg_attribute are:

• indrelid : The OID of the “pg_class” entry for the table this index is for.
• indkey : This is an array of “indnatts” values that indicate which table columns this index indexes. For example, a value of ( 2 3 ) would mean that the second and the third table columns make up the index entries.
• indisprimary : If true, this index represents the primary key of the table. 

Also Read: Postgresql date_trunc function

How to find primary column name using pgAdmin

To find the primary column names in Postgresql using the pgAdmin use the below instructions.

• Select the required table from the list.

• Right-click on the selected table and click on Properties.

• Select the “Constraints” tab.

• Now click on “Primary key“, it will show the primary column names.

You may also like reading the following articles.

• Postgresql generate_series
• Update query in PostgreSQL
• Postgresql date_trunc function
• PostgreSQL TO_NUMBER() function
• PostgreSQL Length + 12 Examples
• PostgreSQL TO_TIMESTAMP function
• Postgresql change column data type
• Postgresql cast int
• Postgresql ilike case insensitive
• Postgresql auto increment
• Postgresql import SQL file

So, in this Postgresql section, we have learned about “How to find a primary column name” using pgAdmin. And in this article, we have covered the following topics.

• How to find primary key column name in Postgresql
• How to find primary column name using pgAdmin