How to show all constraints on a Postgres table

I recently needed to update the check constraint on a Postgres table column. This check does format validation similar to regex. This check was part of the CREATE TABLE statement meaning it was created with the table.

However, ALTER COLUMN is not supporting updates of checks. So what we need to do is to drop the constraint and create a new one. dropConstraint requires the name of the constraint, but the name is not obvious. This is because check was create with createTable. To find the name, there’s a SQL query we can run:

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';