Thanks David for your quick response.
I'm using below query to pull the schema information ,but the count of rows in table is giving wrong ,i can see the count of records using select count(*) from <table_Name> . How do we get an exact number of rows in table.
SELECT C.relname AS Table_Name,
C.relnatts AS NUM_COLS,
C.reltuples::bigint AS NUM_ROWS,
C.relhastriggers AS Has_Triggers,
C.relhasindex AS HAS_INDEX
FROM pg_class C
JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE n.nspname='ap' and C.relkind='r' ORDER BY C.relname ;
On Mon, Dec 4, 2017 at 9:20 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:
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;To obtain information about indexed columns and triggers, you will also need to querypg_index and pg_triggerhttps://www.postgresql.org/docs/9.6/static/catalogs.html 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.
Thanks & Regards,
Brahmeswara Rao J.
Brahmeswara Rao J.
