On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar 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
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.