pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtables meta data collection
2+ messages / 2 participants
[nested] [flat]
* tables meta data collection
@ 2021-03-03 01:20 Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-03-03 04:20 ` Re: tables meta data collection Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 2+ messages in thread
From: Nagaraj Raj @ 2021-03-03 01:20 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Everyone,
I was trying to collect table metadata with a description; the use case is that I need to show all columns of the tables whether it has the description or not.
I tried the below query, but it only gives column details that have a description and ignore others if not.
Postgres 11 | db<>fiddle
|
|
| |
Postgres 11 | db<>fiddle
Free online SQL environment for experimenting and sharing.
|
|
|
create table test(id int);create table test1(id int Primary key );comment on column test.id is 'Test descr';
SELECT c.table_schema,c.table_name,c.column_name,case when c.domain_name is not null then c.domain_name when c.data_type='character varying' THEN 'character varying('||c.character_maximum_length||')' when c.data_type='character' THEN 'character('||c.character_maximum_length||')' when c.data_type='numeric' THEN 'numeric('||c.numeric_precision||','||c.numeric_scale||')' else c.data_typeend as data_type,c.is_nullable, (select 'Y' from information_schema.table_constraints tcojoin information_schema.key_column_usage kcu on kcu.constraint_name = tco.constraint_name and kcu.constraint_schema = tco.constraint_schema and kcu.constraint_schema = c.table_schema and kcu.table_name = c.table_name and kcu.column_name = c.column_namewhere tco.constraint_type = 'PRIMARY KEY' ) as is_in_PK,(select distinct 'Y' from information_schema.table_constraints tcojoin information_schema.key_column_usage kcu on kcu.constraint_name = tco.constraint_name and kcu.constraint_schema = tco.constraint_schema and kcu.constraint_schema = c.table_schema and kcu.table_name = c.table_name and kcu.column_name = c.column_namewhere tco.constraint_type = 'FOREIGN KEY' ) as is_in_FK,pgd.description
FROM pg_catalog.pg_statio_all_tables as st Left outer join pg_catalog.pg_description pgd on (pgd.objoid=st.relid) left outer join information_schema.columns c on (pgd.objsubid=c.ordinal_position and c.table_schema=st.schemaname and c.table_name=st.relname)where c.table_name='test'order by c.table_schema,c.table_name,c.ordinal_position;
expected formate is :
| table_schema | table_name | column_name | data_type | is_nullable | is_in_pk | is_in_fk | description |
any suggestions?
Thanks,Rj
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: tables meta data collection
2021-03-03 01:20 tables meta data collection Nagaraj Raj <nagaraj.sf@yahoo.com>
@ 2021-03-03 04:20 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 2+ messages in thread
From: Justin Pryzby @ 2021-03-03 04:20 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Mar 03, 2021 at 01:20:51AM +0000, Nagaraj Raj wrote:
> I was trying to collect table metadata with a description; the use case is that I need to show all columns of the tables whether it has the description or not.
> I tried the below query, but it only gives column details that have a description and ignore others if not.
Looks like you should join information_schema.columns *before*
pg_catalog.pg_description, otherwise the columns view has nothing to join to
unless the column has a description.
...or you could use FULL OUTER JOIN on the columns view.
> SELECT c.table_schema,c.table_name,c.column_name,case when c.domain_name is not null then c.domain_name when c.data_type='character varying' THEN 'character varying('||c.character_maximum_length||')' when c.data_type='character' THEN 'character('||c.character_maximum_length||')' when c.data_type='numeric' THEN 'numeric('||c.numeric_precision||','||c.numeric_scale||')' else c.data_typeend as data_type,c.is_nullable, (select 'Y' from information_schema.table_constraints tcojoin information_schema.key_column_usage kcu on kcu.constraint_name = tco.constraint_name and kcu.constraint_schema = tco.constraint_schema and kcu.constraint_schema = c.table_schema and kcu.table_name = c.table_name and kcu.column_name = c.column_namewhere tco.constraint_type = 'PRIMARY KEY' ) as is_in_PK,(select distinct 'Y' from information_schema.table_constraints tcojoin information_schema.key_column_usage kcu on kcu.constraint_name = tco.constraint_name and kcu.constraint_schema = tco.constraint_schema and kcu.constraint_schema = c.table_schema and kcu.table_name = c.table_name and kcu.column_name = c.column_namewhere tco.constraint_type = 'FOREIGN KEY' ) as is_in_FK,pgd.description
>
> FROM pg_catalog.pg_statio_all_tables as st Left outer join pg_catalog.pg_description pgd on (pgd.objoid=st.relid) left outer join information_schema.columns c on (pgd.objsubid=c.ordinal_position and c.table_schema=st.schemaname and c.table_name=st.relname)where c.table_name='test'order by c.table_schema,c.table_name,c.ordinal_position;
>
> expected formate is :
>
> | table_schema | table_name | column_name | data_type | is_nullable | is_in_pk | is_in_fk | description |
--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-03 04:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-03 01:20 tables meta data collection Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-03-03 04:20 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox