Re: [GENERAL] Schema Information .

On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar <brahmam1234(at)gmail(dot)com> wrote:

> Hi ,
>
> Is there anyway to pull the complete information of tables in a particular
> schema .
>
> Table_Name ,Num_Rows,Columns,Indexed_Columns,Triggers.
>
>
>
> The context of this request is ,we are migrating the database from Oracle
> to PostgreSQl,,so we need to verify the data after perform data migration
> from oracle.
>
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.
>

>Is there anyway to pull the complete information of tables in a particular
schema .

The following query will give you all the tables and columns in a schema:

SELECT n.nspname AS schema,
c.relname AS table,
a.attname AS column,
a.attnum AS col_pos
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE n.nspname = 'your_schema'
AND relkind = 'r'
AND a.attnum > 0
ORDER BY 1, 2, 4;

To obtain information about indexed columns and triggers, you will also
need to query
pg_index and pg_trigger

https://www.postgresql.org/docs/9.6/static/catalogs.html

You might also find it easier to look at the information_schema

<https://www.postgresql.org/docs/9.6/static/information-schema.html>
https://www.postgresql.org/docs/9.6/static/information-schema.html

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.