On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar <brahmam1234@xxxxxxxxx> 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;
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
You might also find it easier to look at the information_schema
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
