How to get all the constraints for a table with this command?

Getting the constraint for a table with a single command in your Postgres database.

Abdallah Yashir
2 min readOct 6, 2023

A nifty little trick if you want to quickly know all the constraints in a table in your postgres database:

This is esp. useful when you have dozens of tables and you don’t want to go through each one of them to find out the constraints.

For example, before deleting an unused table, I want to check if there’s any foreign key constraint on it. What type of constraint is it? Is it on delete cascade? Or is it on delete restrict? Or is it on delete set null?

Here’s the command

select *

from information_schema.table_constraints

where table_name = 'table_name';

This will give something like this:

| constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred |

| — — — — — — — — — — | — — — — — — — — — -| — — — — — — — — -| — — — — — — — -| — — — — — — — | — — — — — — | — — — — — — — — -| — — — — — — — -| — — — — — — — — — — |

| postgres | public | table_name_pkey | postgres | public | table_name | PRIMARY KEY | NO | NO |

--

--

Abdallah Yashir
Abdallah Yashir

Written by Abdallah Yashir

Senior Software Developer, Writer, Amateur Photographer, Reader

No responses yet