Re: [GENERAL] Schema Information .

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.