Thread
-
Query which shows FK child columns?
Ron <ronljohnsonjr@gmail.com> — 2019-11-14T18:49:03Z
v9.6.16 I have a query which shows the parents and children in FK relations, along with the parent column name, but can't seem to find the child column names. Is there a way to find the child column names without having to dig into pg_constraint? Thanks test=# select ccu.table_schema||'.'||ccu.table_name as parent_table, test-# ccu.column_name as parent_column, test-# tc.table_schema||'.'||tc.table_name as child_table, test-# ccu.constraint_schema||'.'||ccu.constraint_name as con_name test-# from information_schema.table_constraints tc, test-# information_schema.constraint_column_usage ccu test-# where tc.constraint_type = 'FOREIGN KEY' test-# and tc.constraint_schema = ccu.constraint_schema test-# and tc.constraint_name = ccu.constraint_name test-# order by parent_table, child_table, ccu.column_name test-# ; parent_table | parent_column | child_table | con_name ---------------------+---------------+---------------------+------------------------------------ public.inventory | inv_id | public.sales_detail | public.sales_detail_sd_inv_id_fkey public.sales_header | parent_id | public.sales_detail | public.sales_detail_id_fkey (2 rows) test=# \d inventory Table "public.inventory" Column | Type | Modifiers -------------+------------------------+----------- inv_id | integer | not null description | character varying(255) | Indexes: "inventory_pkey" PRIMARY KEY, btree (inv_id) Referenced by: TABLE "sales_detail" CONSTRAINT "sales_detail_sd_inv_id_fkey" FOREIGN KEY (sd_inv_id) REFERENCES inventory(inv_id) test=# \d sales_header Table "public.sales_header" Column | Type | Modifiers -----------+---------+----------- parent_id | integer | not null Indexes: "sales_header_pkey" PRIMARY KEY, btree (parent_id) Referenced by: TABLE "sales_detail" CONSTRAINT "sales_detail_id_fkey" FOREIGN KEY (child_id) REFERENCES sales_header(parent_id) test=# \d sales_detail Table "public.sales_detail" Column | Type | Modifiers -----------+---------+----------- child_id | integer | not null seq | integer | not null sd_inv_id | integer | Indexes: "sales_detail_pkey" PRIMARY KEY, btree (child_id, seq) Foreign-key constraints: "sales_detail_id_fkey" FOREIGN KEY (child_id) REFERENCES sales_header(parent_id) "sales_detail_sd_inv_id_fkey" FOREIGN KEY (sd_inv_id) REFERENCES inventory(inv_id) -- Angular momentum makes the world go 'round.
-
Re: Query which shows FK child columns?
Jeff Ross <jross@openvistas.net> — 2019-11-14T23:43:01Z
On 11/14/19 11:49 AM, Ron wrote: > v9.6.16 > > I have a query which shows the parents and children in FK relations, > along with the parent column name, but can't seem to find the child > column names. > > Is there a way to find the child column names without having to dig > into pg_constraint? > > Thanks > I do not think you can do this without using pg_constraint. I've been using this function to display those FKs. The original code isn't mine but as I recall I had to tweak it a little. This is on 10 and I can't remember if this was used on 9.6 but I'd be surprised if any of this won't work on 9.6. client@cargotel_dev> \sf cargotel_common.show_foreign_keys(text) CREATE OR REPLACE FUNCTION cargotel_common.show_foreign_keys(tablename text) RETURNS TABLE(table1 text, column1 text, type text, table2 text, column2 text) LANGUAGE plpgsql AS $function$ declare schemaname text; begin select into schemaname current_schema(); return query execute format(' select conrelid::regclass::text as table1, a.attname::text as column1, t.typname::text as type, confrelid::regclass::text as table2, af.attname::text as column2 from pg_attribute af, pg_attribute a, pg_type t, ( select conrelid, confrelid, conkey[i] as conkey, confkey[i] as confkey from ( select conrelid, confrelid, conkey, confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = ''f'' ) ss) ss2 where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid and a.atttypid = t.oid and confrelid::regclass = ''%I.%I''::regclass order by 1,2;',schemaname,tablename); end; $function$ I use column headings "table 1, column1, table2, column2" but It's easy enough to tweak the column labels. Example: client@cargotel_dev> \d+ ref_acct_cache Table "client.ref_acct_cache" Column │ Type │ Collation │ Nullable │ Default │ Storage │ Stats target │ Description ────────┼─────────┼───────────┼──────────┼────────────────────────────────────────────┼──────────┼──────────────┼───────────── id │ integer │ │ not null │ nextval('ref_acct_cache_id_seq'::regclass) │ plain │ │ descr │ text │ │ │ │ extended │ │ Indexes: "ref_acct_cache_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "acct_cache" CONSTRAINT "acct_cache_type_id_ref_acct_cache_id_fk" FOREIGN KEY (type_id) REFERENCES ref_acct_cache(id) client@cargotel_dev> select * from cargotel_common.show_foreign_keys('ref_acct_cache'); table1 │ column1 │ type │ table2 │ column2 ────────────┼─────────┼──────┼────────────────┼───────── acct_cache │ type_id │ int4 │ ref_acct_cache │ id (1 row) client@cargotel_dev> \d+ acct_cache Table "client.acct_cache" Column │ Type │ Collation │ Nullable │ Default │ Storage │ Stats target │ Description ───────────────┼──────────────────────────┼───────────┼──────────┼────────────────────────────────────────┼──────────┼──────────────┼───────────── id │ integer │ │ not null │ nextval('acct_cache_id_seq'::regclass) │ plain │ │ type_id │ integer │ │ │ │ plain │ │ prefix │ text │ │ │ │ extended │ │ data │ text │ │ │ │ extended │ │ amount │ numeric │ │ │ │ main │ │ timestamp │ timestamp with time zone │ │ │ │ plain │ │ check_number │ text │ │ │ │ extended │ │ client_number │ text │ │ │ │ extended │ │ check_date │ date │ │ │ │ plain │ │ Indexes: "acct_cache_pkey" PRIMARY KEY, btree (id) "acct_cache_prefix_type_id_data_idx" btree (prefix, type_id, data) "acct_cache_type_id_idx" btree (type_id) Foreign-key constraints: "acct_cache_type_id_ref_acct_cache_id_fk" FOREIGN KEY (type_id) REFERENCES ref_acct_cache(id) Referenced by: TABLE "load_trx" CONSTRAINT "load_trx_ar_voucher_id_acct_cache_id_fk" FOREIGN KEY (ar_voucher_id) REFERENCES acct_cache(id) TABLE "loadacct_link" CONSTRAINT "loadacct_link_acct_cache_id_acct_cache_id_fk" FOREIGN KEY (acct_cache_id) REFERENCES acct_cache(id) TABLE "qb_invoice_incomplete" CONSTRAINT "qb_invoice_incomplete_acct_cache_id_acct_cache_id_fk" FOREIGN KEY (acct_cache_id) REFERENCES acct_cache(id) TABLE "qb_payment_log" CONSTRAINT "qb_payment_log_acct_cache_id_acct_cache_id_fk" FOREIGN KEY (acct_cache_id) REFERENCES acct_cache(id) And as a bonus: client@cargotel_dev> select * from cargotel_common.show_foreign_keys('acct_cache'); table1 │ column1 │ type │ table2 │ column2 ───────────────────────┼───────────────┼──────┼────────────┼───────── loadacct_link │ acct_cache_id │ int4 │ acct_cache │ id load_trx │ ar_voucher_id │ int4 │ acct_cache │ id qb_invoice_incomplete │ acct_cache_id │ int4 │ acct_cache │ id qb_payment_log │ acct_cache_id │ int4 │ acct_cache │ id (4 rows) Hope that helps! Jeff -
Re: Query which shows FK child columns?
Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-15T14:55:17Z
Jeff Ross <jross@openvistas.net> writes: > On 11/14/19 11:49 AM, Ron wrote: >> I have a query which shows the parents and children in FK relations, >> along with the parent column name, but can't seem to find the child >> column names. >> Is there a way to find the child column names without having to dig >> into pg_constraint? > I do not think you can do this without using pg_constraint. In principle, you can get useful information out of a join of information_schema.referential_constraints and information_schema.key_column_usage, but I think the only appeal that would have is (theoretical) portability to other DBMSes. It'd likely be horribly slow in any nontrivial database, because the information_schema views really don't map very well onto the Postgres catalogs, so the view definitions are overcomplicated already ... and then you gotta join them to get what you want. Aside from manual queries of pg_constraint, you might find it useful to do what psql and pg_dump do, namely use one of the built-in functions that reconstruct the text form of some SQL entity. In this case pg_catalog.pg_get_constraintdef(oid) might serve. For example, in a database containing only d1=# create table pk(a int, b int, primary key(a,b)); CREATE TABLE d1=# create table fk(x int, y int, foreign key (x,y) references pk); CREATE TABLE I get d1=# table information_schema.referential_constraints; constraint_catalog | constraint_schema | constraint_name | unique_constraint_catalog | unique_constraint_schema | unique_constraint_name | match_option | update_rule | delete_rule --------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+------------- d1 | public | fk_x_y_fkey | d1 | public | pk_pkey | NONE | NO ACTION | NO ACTION (1 row) d1=# table information_schema.key_column_usage; constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint --------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+------------------------------- d1 | public | pk_pkey | d1 | public | pk | a | 1 | d1 | public | pk_pkey | d1 | public | pk | b | 2 | d1 | public | fk_x_y_fkey | d1 | public | fk | x | 1 | 1 d1 | public | fk_x_y_fkey | d1 | public | fk | y | 2 | 2 (4 rows) so something could be made out of that, but not without some work to link up the FK and unique constraints. Meanwhile d1=# select conname, conrelid::regclass, pg_catalog.pg_get_constraintdef(oid) from pg_constraint; conname | conrelid | pg_get_constraintdef ------------------------------+----------+---------------------------------------------------------------------------------------------------- cardinal_number_domain_check | - | CHECK ((VALUE >= 0)) yes_or_no_check | - | CHECK (((VALUE)::text = ANY ((ARRAY['YES'::character varying, 'NO'::character varying])::text[]))) pk_pkey | pk | PRIMARY KEY (a, b) fk_x_y_fkey | fk | FOREIGN KEY (x, y) REFERENCES pk(a, b) (4 rows) (Those first two CHECK constraints seem to belong to domains defined in the information_schema itself.) Of course, if what you need is something that can be programmatically analyzed, these text forms aren't too helpful --- but if you just want to see what the constraints are, then this is a good way. regards, tom lane