Thread: [GENERAL] Schema Information .

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.

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.

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 . 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 wrote: > > > 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. > -- Thanks & Regards, Brahmeswara Rao J.

On Tue, Dec 5, 2017 at 4:09 AM, Brahmam Eswar wrote: > 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 . 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 > wrote: > >> >> >> 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. >> > > > > -- > Thanks & Regards, > Brahmeswara Rao J. > >Thanks David for your quick response. My name is NOT David! It is Melvin. >How do we get an exact number of rows in table. As stated in the _documentation_, reltuples "This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX." https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html Did you analyze your database before executing the query? The reltuples are only updated during an analyze. You need to do a VACUUMDB -z -d yourdb_name Also, in the future, please be kind enough to state your PostgreSQL VERSION and O/S. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.