Re: Need r_constraint_name

thank u ,

SELECT constraint_name
FROM information_schema.table_constraints AS tc
WHERE tc.table_name = p_table_name
AND constraint_name IN (SELECT constraint_name
FROM
information_schema.table_constraints AS tc
WHERE tc.table_name =
p_ref_table_name
AND tc.constraint_type =
'PRIMARY KEY');

is this correct process same as above ..

but i want check "r_constraint_name" instead of "constraint_name" in outer
statement in above code..

please let me know..

thanks in advance,
ramesh

On Tue, Jul 22, 2014 at 7:52 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>