Postgres System information functions and examples

Here are collected some of the system functions and tricks how to get data from the Postgres database

How to get creation clause of the view

Sometimes you have an database view and you start to wonder what kind of sql clause there is. Following command will show you definition of particular view.

1

select definition from pg_views where schemaname = 'example_public' and viewname ='example_view';

How do I see the status of database index in postgres

Notice! Following query requires the PG_stat extension.

1
2
3

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
    FROM pg_stat_all_indexes
    WHERE schemaname NOT LIKE 'pg_%' AND schemaname != 'information_schema';