Detailed explanation of PostgreSql database object information and application

PostgreSql database objects mainly include databases, tables, views, indexes, schemas, functions, triggers, etc. PostgreSql provides information_schema schema, which includes a view that returns a database object. If the user has access rights, you can also query tables, views and other objects in the pg_catalog schema.

1. Query the database object

The following examples show how to query various database objects.

1.1 Table query

PostgreSql table information can be queried from the information_schema.tables or pg_catalog.pg_tables view:

select * from information_schema.tables;
select * from pg_catalog.pg_tables;

1.2 Query Schema

Get the schema currently selected by the user:

select current_schema();

Return all schemas in the database:

select * from information_schema.schemata;
select * from pg_catalog.pg_namespace

1.3 Query the database

Query the currently selected database:

select current_database();

Return to all databases on the server:

select * from pg_catalog.pg_database

1.4 Query View

Query all views in all schemas in the database:

select * from information_schema.views

select * from pg_catalog.pg_views;

1.5 Query table column information

Query the column information of a table:

SELECT
	*
FROM
	information_schema.columns
WHERE
	table_name = 'employee'
ORDER BY
	ordinal_position;

1.6 Query index information

Query all index information in the database;

select * from pg_catalog.pg_indexes;

1.6 Query function information

Returns all functions in the database. For user-defined functions, the routine_definition column has a function body:

select * from information_schema.routines where routine_type = 'FUNCTION';

1.7 Trigger

Query all triggers in the database, the action_statemen category includes trigger body information:

select * from information_schema.triggers;

2. The query table takes up space

2.1 Query table takes up space

In practical applications, tables usually require disk space to be occupied, and we can use system tables to implement:

SELECT nspname || '.' || relname AS "relation",
 pg_size_pretty(pg_total_relation_size()) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON ( = )
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 AND  <> 'i'
 AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size() DESC
 LIMIT 5;

Sample output:

(5 rows)

2.2 Query the database footprint

SELECT
	pg_database.datname AS "database_name",
	pg_size_pretty(pg_database_size (pg_database.datname)) AS size_in_mb
FROM
	pg_database
ORDER BY
	size_in_mb DESC;

2.3 Number of records in the query table

You can query through the statistical system table:

SELECT schemaname,relname,n_live_tup 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC
LIMIT 12;

By the way, readers can compare and learn about MySQL for queries:

SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = (SELECT database())
ORDER BY table_rows DESC
LIMIT 12;

4. System tables and system views

Check the database system table command:

\dt  pg_*

List all system diagrams starting with pg:

\dv  pg_*

4. Summary

This article introduces PostgreSQL system tables and views; query database objects and commonly used statistical information through system tables or views.

This is the article about PostgreSql database object information and applications. For more related PostgreSql database application content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!