Re: Need r_constraint_name - Mailing list pgsql-general

On 07/19/2014 12:26 PM, Ramesh T wrote:
> Hi,
>             In oracle got constraint details using user_constraint,
>
> But in postgres how to get the r_constraint_name,constraint_name  of the
> particular table...?
>
> mainly i need r_constraint_name on table.. how to get it?please let me know
>
>

 From psql:

test=> CREATE TABLE parent_tbl(id serial primary key, fld_1 text);
NOTICE:  CREATE TABLE will create implicit sequence "parent_tbl_id_seq"
for serial column "parent_tbl.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"parent_tbl_pkey" for table "parent_tbl"
CREATE TABLE

test=> CREATE TABLE child_tbl (id serial primary key, fk_fld integer
references parent_tbl, fld_2 text);
NOTICE:  CREATE TABLE will create implicit sequence "child_tbl_id_seq"
for serial column "child_tbl.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"child_tbl_pkey" for table "child_tbl"
CREATE TABLE

test=> \d parent_tbl
                          Table "public.parent_tbl"
  Column |  Type   |                        Modifiers

--------+---------+---------------------------------------------------------
  id     | integer | not null default nextval('parent_tbl_id_seq'::regclass)
  fld_1  | text    |
Indexes:
     "parent_tbl_pkey" PRIMARY KEY, btree (id)
Referenced by:
     TABLE "child_tbl" CONSTRAINT "child_tbl_fk_fld_fkey" FOREIGN KEY
(fk_fld) REFERENCES parent_tbl(id)

test=> \d child_tbl
                          Table "public.child_tbl"
  Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
  id     | integer | not null default nextval('child_tbl_id_seq'::regclass)
  fk_fld | integer |
  fld_2  | text    |
Indexes:
     "child_tbl_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
     "child_tbl_fk_fld_fkey" FOREIGN KEY (fk_fld) REFERENCES parent_tbl(id)


If you want to know what query psql uses to get this information start
psql with -E, this will tell you that the queries are:


To get the child key that references the parent from the parent:

test=> SELECT conname, conrelid::pg_catalog.regclass,
   pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = 'parent_tbl'::regclass AND c.contype = 'f' ORDER BY 1
;

         conname        | conrelid  |                     condef

-----------------------+-----------+------------------------------------------------
  child_tbl_fk_fld_fkey | child_tbl | FOREIGN KEY (fk_fld) REFERENCES
parent_tbl(id)


To get the information from the child table:

test=> SELECT conname,
   pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = 'child_tbl'::regclass AND r.contype = 'f' ORDER BY 1
;
         conname        |                     condef
-----------------------+------------------------------------------------
  child_tbl_fk_fld_fkey | FOREIGN KEY (fk_fld) REFERENCES parent_tbl(id)


I used the regclass cast to convert the table names to the appropriate
ids the query expects. In the psql output you will see the numbers.


>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com