Re: [GENERAL] Schema Information .

On Tue, Dec 5, 2017 at 4:09 AM, Brahmam Eswar <brahmam1234(at)gmail(dot)com> 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(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar <brahmam1234(at)gmail(dot)com>
>> 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>
>> 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.