Date: Tue, 2 Mar 2021 22:20:23 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: tables meta data collection
Message-ID: <20210303042023.GF29832@telsasoft.com>
References: <591365510.2330079.1614734451782.ref@mail.yahoo.com>
 <591365510.2330079.1614734451782@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <591365510.2330079.1614734451782@mail.yahoo.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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 i=
s that I need to show all columns of the tables whether it has the descript=
ion or not.=A0
> I tried the below query, but it only gives column details that have a des=
cription and ignore others if not.=A0

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.
=2E..or you could use FULL OUTER JOIN on the columns view.

> =A0SELECT c.table_schema,c.table_name,c.column_name,case=A0=A0 =A0 when c=
=2Edomain_name is not null then c.domain_name=A0 =A0 when c.data_type=3D'ch=
aracter varying' THEN 'character varying('||c.character_maximum_length||')'=
=A0 =A0 when c.data_type=3D'character' THEN 'character('||c.character_maxim=
um_length||')' =A0 =A0 when c.data_type=3D'numeric' THEN 'numeric('||c.nume=
ric_precision||','||c.numeric_scale||')'=A0 =A0 else c.data_typeend as data=
_type,c.is_nullable,=A0(select 'Y' from information_schema.table_constraint=
s tcojoin information_schema.key_column_usage kcu=A0=A0 =A0 =A0on kcu.const=
raint_name =3D tco.constraint_name=A0 =A0 =A0and kcu.constraint_schema =3D =
tco.constraint_schema=A0 =A0 =A0and kcu.constraint_schema =3D c.table_schem=
a=A0and kcu.table_name =3D c.table_name=A0and kcu.column_name =3D c.column_=
namewhere tco.constraint_type =3D 'PRIMARY KEY' ) as is_in_PK,(select disti=
nct 'Y' from information_schema.table_constraints tcojoin information_schem=
a.key_column_usage kcu=A0=A0 =A0 =A0on kcu.constraint_name =3D tco.constrai=
nt_name=A0 =A0 =A0and kcu.constraint_schema =3D tco.constraint_schema=A0 =
=A0 =A0and kcu.constraint_schema =3D c.table_schema=A0and kcu.table_name =
=3D c.table_name=A0and kcu.column_name =3D c.column_namewhere tco.constrain=
t_type =3D 'FOREIGN KEY' ) as is_in_FK,pgd.description
>=20
> FROM pg_catalog.pg_statio_all_tables as st=A0 Left outer join pg_catalog.=
pg_description pgd on (pgd.objoid=3Dst.relid)=A0 left outer join informatio=
n_schema.columns c on (pgd.objsubid=3Dc.ordinal_position=A0 =A0 and=A0 c.ta=
ble_schema=3Dst.schemaname and c.table_name=3Dst.relname)where c.table_name=
=3D'test'order by c.table_schema,c.table_name,c.ordinal_position;=A0
>=20
> expected formate is :
>=20
> | table_schema | table_name | column_name | data_type | is_nullable | is_=
in_pk | is_in_fk | description |

--=20
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581