Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHGCK-00054j-00 for pgsql-performance@arkaria.postgresql.org; Wed, 03 Mar 2021 01:21:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lHGCI-000323-UB for pgsql-performance@arkaria.postgresql.org; Wed, 03 Mar 2021 01:21:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lHGCI-00031w-Ar for pgsql-performance@lists.postgresql.org; Wed, 03 Mar 2021 01:21:06 +0000 Received: from sonic311-25.consmr.mail.ne1.yahoo.com ([66.163.188.206]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lHGCB-0006gi-2w for pgsql-performance@lists.postgresql.org; Wed, 03 Mar 2021 01:21:05 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1614734455; bh=5Mhp/qO6K/4NudFg9u/vf3D5y8k7tS5OQrKySUppVxY=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=pGP8k5FLDyWBbsrzs5rrMEokecwRsKvhs/0zQFa56u/2xqcad4gpy2RemKjETCm8gfH+xI7cLojxiE/Cs0/sHE+nEw5SI/1u31AZjBm0S1cc6Tjhw5eDwPQelQeF5BXi6zgE14TlK3p35zCg/NNf2hr46AvjC2ZbrOOTc0UxCqpYS4slmwB3RB/s8s/nQ9dZVtcoF07/yCCfyo+bpCYfnkqsb3oE2NWhzvZ4MuF3yiAcaswWD0i9mA6W/1Jfh4xcU7hF9GknAxQR5aYekuLcSkiEKTuyKxIM+s6ycnkS/42AL7prvf90yFaFRGgzE1YhXkuC0Y0g1YciEr5qQ7mmPw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1614734455; bh=i/mTk7AvcDPOxuhOF1Y9UkogXFxdkiU/VXWSWFK8kGV=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=jFizJangkL61QwZb2O5YfE4yl15XX+k2tvwK2dkKMEb6zhV1U6RWJkeUddlaDOEppAujvr1DFF3TlARuHrDnRCtd23+50oNFVjqN5hAciKZjIfqAf6F9llKcy4lhJdqctQ1NHytBTD+47qH/89Dwq+T9jlKDCPaVYdJGlOJ6sy9iHwGOaPLI/OejYkcd8SEIIzrqD1FqYKw16wAnDvVbIgZzj1doReMsGltIzHdwL6xmqTb9W4UBPAGviJCpN5g47+P5xSXEBPDytg++edsBJxkexafOVoFXOp9HcXHx+8znZqbxO+0okC4DMvRveSsVwQ9FSW0Q8lN+AQzqR7fTpA== X-YMail-OSG: IvMYEFsVM1lbdobskaT61PdUxja2N7gdDN7BxrLWaaPV7SMRdhUY6Pag5h7c8Ep 4A8s7zw5jLPtByNM57tymHiS4HFBV5qstJHO03oNWqDjydvGBlO5NA.GPIQ12SFdLYNyhnFRz2V8 UpcZN1MJkZdRX312XbYiWhedA.TXT9vGhsxJoyeDzjV_zwbagg6PBWw9T0KDeKgnMHHRfK7maHL8 vqrZ6NSAX2EJkaxw7Ti2SlmRsF3pr0IL67TvdcUQcmNFsZ8IYlujXG41GDsj3yfktPtCBq8BbCJI jnk4jte2wU9EOD1zaQHgOQKxvAjsPZJWhgALMPmeX7MOyjMrxeXi3thMMbVQwwZ.Txe2cZY5j5IG pUgRSE7.rUhEtfAECNtm7EqbYRHpz4_PsdoWTOVm73hruLbMv4nsKTYq2W1z46qVDTab7nrjLxg1 Y7L65qQi6AS4rtnkN5fYFEcKIkriO2FRFI7mqdWZ9kAweCriy8k9RfJyZiWVcAvvi1r6L0grHdGM fanG6RPGqgYDEgLE6ejhm.rtAxQJpB2jtyFEs_RiejkwLmulZFawunxwmAG3_V5S7TTb2K9GkGnn MA11fm85NH8fb8RhsLDzJON3QtdEzSQ_uJ81MdFIcWRcTmSiiVA2ExhE4xDfAoFHERem9LNc0xO2 fGZ0SoP56Ontsd5bb6Cz9GXWHUjWu1fgns2o1DgKECv9AQ2Pl04BTEKJ8euHFootajgPs5bwPC1r M2S6nkusY56ROnN57HDYJXnfGXnW6bhkftggWdnknNxhN7MDjJlK1UcUSlsjJgMb2LPeYinVZRNo 6EK9bR5DrKgsjAbbQ3mAnAwt815KTFEmiNLytGsOrJqiUwcWe9C_RqgiL8tlydS2SsvCziY6E4gN 0._qNUtMY1fIkBc8hiojZ1IT.Q3BccjnxO2tGmCprUNtz.OVnH.zwyHXSdedHoWZmpp7mXDh9_u4 2JUPVKFr5i8bC5y_sZilDn7np_0VwHaxhY_QL6IFtUszEwQmwwARHYRzCXsQJ_ffIlFWUgGN3b2J My3JD5QK8CjeEEV2Vr82d0PEo01.rIG6pnv0K7x75xQjREasphTMP3.Ra58Vgwkt0v_IhTka60HI 3wIIbiFshkoFbq0zULtxzS2fg0vjsDn8GJuXHDMM_Pc0_fzW_hiOzmBAkS.7XBTB0058mp0pL0f3 zc0TYT5ZiMRNscNcqTo_JopwN1kdVUokORXTE4LA09pZ4yPWR.yVodCdS4p70SDrr00MiFDjl1Aa aQ4oKT7Udmt_yWoTvYXjQdTgLbHMi0d_NKladfAtiQsnOoysKeThtLNwZY_o_HAUJHMoZDmh.MtE OdDXteBbUI1dYhODlGEqCAjcRLCCFnNHpOCobf86Tm9IuKLfjd7fzZQCeLNuVrRi6F_o2cddyqZC uvdgQNIYhGaPrA1Fxrc2_iOIVouhnY6Mw0iVejrAmZQM0ryVzDwqqXr6ftNwZtrAbvJ2B8EUmOId O87aH8vor_4fL7tzKJwQK4zYz_3JvMfvmeUpdnxgIeANqeccACSSk3vmPGdQaBEaocENJXKikl_Y mNLMGzv7Iy03RAZf_OZzWEt9A5IfLYw3sO0aT8ff4JPlPk7Mq8HAx18I3vwcS0ZoM4d7bBduN1cg CDxGaUFEEdNFkyRiTphh0ol7U3vReUJno7JqY07Bc0p9T6lPdAJwLeLVx8nlTk7pRd0uhCKeyHEF ObNeKszY6eOemjRJivftCFk8HSRTQ68xgVTv.uyXKnl.xFqNFeIJeDSwLSbGbFmmMmiGefBP6mP1 ug7GBdzjWJPoCDyilSJv65.5H4AOr4WIDRDOwWM0Apqug25XcMXiZVSeb8iJKkZTKVBg12k7CabQ N2wfrSaLgSbjylj0VOIJd.Vhg2T9Xd5uuq4eQaFyPXvZa2S_0XoTyq0E_T2AxLvSIOlW3BhCUK5b CHH0abmfsnI000e9G3lUakiiuiwR0s9VWV6wJylZoiVpsuh17A_HrdhTlkcqBKWO7lugJ9qVBHMM dJHVJYiput4qOSY8As5BHK6kvXT6yHbYw8DmjXUEY.QypUsi3EkLVh5867ZLb0V5r322hKTtfyDz h62GxmXgCgXxCQ.uZRIxkoAgZX9DPUQ7Jxb5MEACiUijPgsew.BeB3X1Yde6E5XmtxPYelZpFxZ8 ftvxBAEAjT0PRs0kfwUR3EX.Um.ThOMw2eWy9qq4FzvcypGR1O6DVySjR.ohdUhWECuh5Ac5JqIp cxS95MSQFWL4q6DUg_.LX_IVI6OWjoj.3cTO6MxDjl7cqhbDTKb_xuAxTFYYYBguu_E1vp0v344Z wTmCW5iAL4XbiHT9MyUKrsex.NLG8AmafQEm6KPxmC04oPx33l1dqWJX2ddAmlTSV79A2qzcZqgE y9ZlGAnemrhFZdRz9TE94bvgWVAfvxE1IyH02fmCL2sUhmqNmUNXhxk96XSieFSwlei05368iTFx R.U1Oqpc9BG7XH9_2r9q3YChN3x.WpAJtoMHG X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ne1.yahoo.com with HTTP; Wed, 3 Mar 2021 01:20:55 +0000 Date: Wed, 3 Mar 2021 01:20:51 +0000 (UTC) From: Nagaraj Raj To: Pgsql Performance Message-ID: <591365510.2330079.1614734451782@mail.yahoo.com> Subject: tables meta data collection MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2330078_2139676964.1614734451779" References: <591365510.2330079.1614734451782.ref@mail.yahoo.com> X-Mailer: WebService/1.1.17828 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 11_2_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.192 Safari/537.36 Content-Length: 11331 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2330078_2139676964.1614734451779 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 descriptio= n or not.=C2=A0 I tried the below query, but it only gives column details that have a descr= iption and ignore others if not.=C2=A0 Postgres 11 | db<>fiddle |=20 |=20 | |=20 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 o= n column test.id is 'Test descr'; =C2=A0SELECT c.table_schema,c.table_name,c.column_name,case=C2=A0=C2=A0 =C2= =A0 when c.domain_name is not null then c.domain_name=C2=A0 =C2=A0 when c.d= ata_type=3D'character varying' THEN 'character varying('||c.character_maxim= um_length||')'=C2=A0 =C2=A0 when c.data_type=3D'character' THEN 'character(= '||c.character_maximum_length||')' =C2=A0 =C2=A0 when c.data_type=3D'numeri= c' THEN 'numeric('||c.numeric_precision||','||c.numeric_scale||')'=C2=A0 = =C2=A0 else c.data_typeend as data_type,c.is_nullable,=C2=A0(select 'Y' fro= m information_schema.table_constraints tcojoin information_schema.key_colum= n_usage kcu=C2=A0=C2=A0 =C2=A0 =C2=A0on kcu.constraint_name =3D tco.constra= int_name=C2=A0 =C2=A0 =C2=A0and kcu.constraint_schema =3D tco.constraint_sc= hema=C2=A0 =C2=A0 =C2=A0and kcu.constraint_schema =3D c.table_schema=C2=A0a= nd kcu.table_name =3D c.table_name=C2=A0and kcu.column_name =3D c.column_na= mewhere tco.constraint_type =3D 'PRIMARY KEY' ) as is_in_PK,(select distinc= t 'Y' from information_schema.table_constraints tcojoin information_schema.= key_column_usage kcu=C2=A0=C2=A0 =C2=A0 =C2=A0on kcu.constraint_name =3D tc= o.constraint_name=C2=A0 =C2=A0 =C2=A0and kcu.constraint_schema =3D tco.cons= traint_schema=C2=A0 =C2=A0 =C2=A0and kcu.constraint_schema =3D c.table_sche= ma=C2=A0and kcu.table_name =3D c.table_name=C2=A0and kcu.column_name =3D c.= column_namewhere tco.constraint_type =3D 'FOREIGN KEY' ) as is_in_FK,pgd.de= scription FROM pg_catalog.pg_statio_all_tables as st=C2=A0 Left outer join pg_catalog= .pg_description pgd on (pgd.objoid=3Dst.relid)=C2=A0 left outer join inform= ation_schema.columns c on (pgd.objsubid=3Dc.ordinal_position=C2=A0 =C2=A0 a= nd=C2=A0 c.table_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_positi= on;=C2=A0 expected formate is : | table_schema | table_name | column_name | data_type | is_nullable | is_in= _pk | is_in_fk | description | any suggestions? Thanks,Rj ------=_Part_2330078_2139676964.1614734451779 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Everyone,

<= div dir=3D"ltr" data-setdir=3D"false">I was trying to collect table metadat= a with a description; the use case is that I need to show all columns of th= e tables whether it has the description or not. 

I t= ried the below query, but it only gives column details that have a descript= ion and ignore others if not. 







<= /div>
create table test(id = int);
create table test1(id int Primary key );
comment = on column test.id is 'Test descr';


 SELECT c.table_sche= ma,c.table_name,c.column_name,
case 
   = when c.domain_name is not null then c.domain_name
    = when c.data_type=3D'character varying' THEN 'character varying('||c.charact= er_maximum_length||')'
    when c.data_type=3D'characte= r' THEN 'character('||c.character_maximum_length||')'=09
    when c.data_type=3D'num= eric' THEN 'numeric('||c.numeric_precision||','||c.numeric_scale||')'
=
    else c.data_type
end as data_type,
c= .is_nullable, 
(select 'Y' from information_schema.table_con= straints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name =3D tco.constraint_name=
     and kcu.constraint_schema =3D tco.constraint= _schema
     and kcu.constraint_schema =3D c.table= _schema
 and kcu.table_name =3D c.table_name
 = ;and kcu.column_name =3D c.column_name
where tco.constraint_type = =3D 'PRIMARY KEY' ) as is_in_PK,
(select distinct 'Y' from inform= ation_schema.table_constraints tco
join information_schema.key_co= lumn_usage kcu 
     on kcu.constraint_name = =3D tco.constraint_name
     and kcu.constraint_sc= hema =3D tco.constraint_schema
     and kcu.constr= aint_schema =3D c.table_schema
 and kcu.table_name =3D c.tab= le_name
 and kcu.column_name =3D c.column_name
whe= re tco.constraint_type =3D 'FOREIGN KEY' ) as is_in_FK,
pgd.descr= iption


FROM pg_catalog.pg_statio_al= l_tables as st
  Left outer join pg_catalog.pg_description p= gd on (pgd.objoid=3Dst.relid)
  left outer join information_= schema.columns c on (pgd.objsubid=3Dc.ordinal_position
  &nb= sp; and  c.table_schema=3Dst.schemaname and c.table_name=3Dst.relname)=
where=09c.table_na= me=3D'test'
order by c.table_schema,c.table_name,c.ordinal_positi= on; 


expected formate is :

=
table_schematable_namecolumn_namedata_typeis_nullableis_in_pkis_in_fkdescription
<= /span>

any suggestions?

Thanks,
Rj

------=_Part_2330078_2139676964.1614734451779--