Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to obtain the key system information in the database by PostgreSql". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to obtain the key system information in the database by PostgreSql".

Generally speaking, there are one or more system databases in each kind of database. In PG, schemaname starts with pg_catalog and is the system table. Through the system table, we can know what most database systems do.

1 View all current tables (user tables)

SELECT relname

FROM pg_class

WHERE relname! ~'^ (pg_ | sql_)'

AND relkind ='r'

Or

Also OK

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'

AND table_schema NOT IN

('pg_catalog',' information_schema')

2 View the VIEW created by the user

SELECT table_name

FROM information_schema.views

WHERE table_schema NOT IN ('pg_catalog',' information_schema')

AND table_name! ~'^ pg_'

3 users of the current database

SELECT usename FROM pg_user

4 list the fields of a table

SELECT a.attname

FROM pg_class c, pg_attribute a, pg_type t

WHERE c.relname = 'table name'

AND a.attnum > 0

AND a.attrelid = c.oid

AND a.atttypid = t.oid

5 Index of query table

SELECT relname, indkey

FROM pg_class, pg_index

WHERE pg_class.oid = pg_index.indexrelid

AND pg_class.oid IN (

SELECT indexrelid

FROM pg_index, pg_class

WHERE pg_class.relname=' table name'

AND pg_class.oid=pg_index.indrelid

AND indisunique! ='t'

AND indisprimary! ='t'

);

The fields of this table are queried and indexed.

SELECT t.relname, a.attname, a.attnum

FROM pg_index c

LEFT JOIN pg_class t

ON c.indrelid = t.oid

LEFT JOIN pg_attribute a

ON a.attrelid = t.oid

AND a.attnum = ANY (indkey)

WHERE t.relname = 'table name'

Get the indexing statement for the current database table

SELECT

Tablename

Indexname

Indexdef

FROM

Pg_indexes

WHERE

Schemaname = 'public'

ORDER BY

Tablename

Indexname

6 the constraints established by the specified table in the system

SELECT constraint_name, constraint_type

FROM information_schema.table_constraints

WHERE table_name = 'table name'

7 display of the details of a constraint

SELECT c.conname AS constraint_name

CASE c.contype

WHEN'c 'THEN' CHECK'

WHEN'f 'THEN' FOREIGN KEY'

WHEN'p 'THEN' PRIMARY KEY'

WHEN'u 'THEN' UNIQUE'

END AS "constraint_type"

CASE WHEN c.condeferrable ='f 'THEN 0 ELSE 1 END AS is_deferrable

CASE WHEN c.condeferred ='f 'THEN 0 ELSE 1 END AS is_deferred

T.relname AS table_name

Array_to_string (c.conkey,'') AS constraint_key

CASE confupdtype

WHEN'a 'THEN' NO ACTION'

WHEN'r 'THEN' RESTRICT'

WHEN'c 'THEN' CASCADE'

WHEN 'n'THEN 'SET NULL'

WHEN 'd'THEN 'SET DEFAULT'

END AS on_update

CASE confdeltype

WHEN'a 'THEN' NO ACTION'

WHEN'r 'THEN' RESTRICT'

WHEN'c 'THEN' CASCADE'

WHEN 'n'THEN 'SET NULL'

WHEN 'd'THEN 'SET DEFAULT'

END AS on_delete

CASE confmatchtype

WHEN'u 'THEN' UNSPECIFIED'

WHEN'f 'THEN' FULL'

WHEN'p 'THEN' PARTIAL'

END AS match_type

T2.relname AS references_table

Array_to_string (c.confkey,'') AS fk_constraint_key

FROM pg_constraint c

LEFT JOIN pg_class t ON c.conrelid = t.oid

LEFT JOIN pg_class T2 ON c.confrelid = t2.oid

WHERE t.relname = 'table name'

AND c.conname = 'constraint name'

8 list the relevant self-increasing sequences

SELECT relname

FROM pg_class

WHERE relkind ='S'

AND relnamespace IN (

SELECT oid

FROM pg_namespace

WHERE nspname NOT LIKE 'pg_%'

AND nspname! = 'information_schema'

);

9 filter the trigger established in the related database

SELECT DISTINCT trigger_name

FROM information_schema.triggers

WHERE trigger_schema NOT IN

('pg_catalog',' information_schema')

And detailed information about trigger

SELECT *

FROM information_schema.triggers

WHERE trigger_schema NOT IN

('pg_catalog',' information_schema')

10 View the functions created in the system

SELECT routine_name

FROM information_schema.routines

WHERE specific_schema NOT IN

('pg_catalog',' information_schema')

AND type_udt_name! = 'trigger'

11 View the primary key of the table in the current database

SELECT tc.constraint_name

Tc.constraint_type

Tc.table_name

Kcu.column_name

Tc.is_deferrable

Tc.initially_deferred

Rc.match_option AS match_type

Rc.update_rule AS on_update

Rc.delete_rule AS on_delete

Ccu.table_name AS references_table

Ccu.column_name AS references_field

FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu

ON tc.constraint_catalog = kcu.constraint_catalog

AND tc.constraint_schema = kcu.constraint_schema

AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc

ON tc.constraint_catalog = rc.constraint_catalog

AND tc.constraint_schema = rc.constraint_schema

AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu

ON rc.unique_constraint_catalog = ccu.constraint_catalog

AND rc.unique_constraint_schema = ccu.constraint_schema

AND rc.unique_constraint_name = ccu.constraint_name

WHERE tc.table_name! ~'^ (pg_ | sql_)'

AND tc.constraint_type = 'PRIMARY KEY'

12 get the relationship between the index and the table

SELECT a.index_name, b.attname

FROM (

SELECT a.indrelid

C.relname index_name

Unnest (a.indkey) index_num

FROM pg_index a

Pg_class b

Pg_class c

WHERE

B.oid=a.indrelid

AND a.indisprimary! ='t'

AND a.indexrelid=c.oid

) a

Pg_attribute b

WHERE a.indrelid = b.attrelid

AND a.index_num = b.attnum and a.index_name! ~'^ (pg_ | sql_)'

ORDER BY a.index_name, a.index_num

13 shows the dependencies between VIEW

SELECT v.relname AS "dependent_view"

T.relname AS "referenced_relation"

FROM pg_depend dv

LEFT JOIN pg_class v ON v.oid = dv.refobjid

LEFT JOIN pg_namespace nv ON v.relnamespace = nv.oid

LEFT JOIN pg_depend dt

ON dv.classid = dt.classid

AND dv.objid = dt.objid

AND dv.refobjid dt.refobjid

AND dv.refclassid = dt.refclassid

AND dv.classid = 'pg_catalog.pg_rewrite'::regclass

AND dv.refclassid = 'pg_catalog.pg_class'::regclass

LEFT JOIN pg_class t ON t.oid = dt.refobjid

LEFT JOIN pg_namespace nt

ON t.relnamespace = nt.oid

AND nv.nspname = 'public'

AND nt.nspname = 'public'

WHERE dv.deptype ='i'

AND v.relkind ='v'

AND t.relkind IN ('ringing,' v')

AND v.relname = 'testttt'-- VIEW NAME

GROUP BY v.relname, t.relname

Thank you for your reading, the above is the content of "how to obtain the key system information in the database by PostgreSql". After the study of this article, I believe you have a deeper understanding of how PostgreSql obtains the key system information in the database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.