Re: Schema Information .

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Tue, Dec 5, 2017 at 4:09 AM, Brahmam Eswar <brahmam1234@xxxxxxxxx> 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 <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 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.