Postgres: Read CONSTRAINTS definition I'm able to read CONSTRAINTS in the Information Schema, but how can I get all parameters associated with that CONSTRAINTS to get a full CONSTRAINTS definition by using SQL ?
For example,

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 


This will give me list of CONSTRAINTS, but let say, I got a CONSTRAINTS FOREIGN KEY  "user_role_id" and its full definition (via pgAdmin) is

ALTER TABLE app.user
ADD CONSTRAINT user_role_id FOREIGN KEY (role_id)
REFERENCES app.role (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE;


So how do I get this definition by just writing SQL that give me all those options associated with that CONSTRAINTS?
This solve my problem

    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    n.nspname = 'public' -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;