Postgres: Read CONSTRAINTS definition

Question

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?

There are many more tables in the Information Schema; see https://www.postgresql.org/docs/10/static/information-schema.html In addition, you should probably be using the Postgres-specific system catalogs, https://www.postgresql.org/docs/10/static/catalogs.html — Colin 't Hart, May 12 '18 at 12:42
But the real Postgres-way to do this is to use pg_get_constraintdef() and other similar functions. See https://www.postgresql.org/docs/current/static/functions-info.html — Colin 't Hart, May 12 '18 at 12:44

score 12 · Accepted Answer · answered May 13 '18 at 20:55

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;

Postgres: Read CONSTRAINTS definition

1 Answers1