{"id":931,"date":"2023-05-02T06:57:06","date_gmt":"2023-05-02T00:57:06","guid":{"rendered":"https:\/\/mellowhost.com\/blog\/?p=931"},"modified":"2023-05-02T06:57:06","modified_gmt":"2023-05-02T00:57:06","slug":"how-to-see-all-the-constraints-in-a-postgresql-database","status":"publish","type":"post","link":"https:\/\/mellowhost.com\/blog\/how-to-see-all-the-constraints-in-a-postgresql-database.html","title":{"rendered":"How to see all the constraints in a Postgresql Database"},"content":{"rendered":"\n<p>To see\/list the constraints, first, connect to the database using the following:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\\c my_prod;<\/pre>\n\n\n\n<p>Here we are assuming the database name is my_prod. Please note, we are putting these commands in the psql client utility.<\/p>\n\n\n\n<p>Now, use the following query to list all the constraints in the database:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">select pgc.conname as constraint_name,\n       ccu.table_schema as table_schema,\n       ccu.table_name,\n       ccu.column_name,\n       contype,\n        pg_get_constraintdef(pgc.oid)\nfrom pg_constraint pgc\n         join pg_namespace nsp on nsp.oid = pgc.connamespace\n         join pg_class  cls on pgc.conrelid = cls.oid\n         left join information_schema.constraint_column_usage ccu\n                   on pgc.conname = ccu.constraint_name\n                       and nsp.nspname = ccu.constraint_schema\norder by pgc.conname;<\/pre>\n\n\n\n<p>Good luck<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To see\/list the constraints, first, connect to the database using the following: Here we are assuming the database name is my_prod. Please note, we are putting these commands in the psql client utility. Now, use the following query to list all the constraints in the database: Good luck<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[412,523],"tags":[713,527,580,712],"_links":{"self":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/931"}],"collection":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/comments?post=931"}],"version-history":[{"count":1,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/931\/revisions"}],"predecessor-version":[{"id":932,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/931\/revisions\/932"}],"wp:attachment":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/media?parent=931"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/categories?post=931"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/tags?post=931"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}