postgresql 获取schema,table 信息
postgres=# \c postgres You are now connected to database "postgres" as user "postgres". postgres=# select * from information_schema.schemata; catalog_name | schema_name | schema_owner | default_character_set_catalog | default_character_set_schema | default_character_set_name | sql_path --------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+---------- postgres | information_schema | postgres | | | | postgres | public | postgres | | | | postgres | pg_catalog | postgres | | | | postgres | pg_toast_temp_1 | postgres | | | | postgres | pg_temp_1 | postgres | | | | postgres | pg_toast | postgres | | | | (6 rows)
查询schema下面的表
postgres=# select * FROM pg_tables WHERE schemaname = 'information_schema'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity --------------------+-------------------------+------------+------------+------------+----------+-------------+------------- information_schema | sql_packages | postgres | | f | f | f | f information_schema | sql_features | postgres | | f | f | f | f information_schema | sql_implementation_info | postgres | | f | f | f | f information_schema | sql_parts | postgres | | f | f | f | f information_schema | sql_languages | postgres | | f | f | f | f information_schema | sql_sizing | postgres | | f | f | f | f information_schema | sql_sizing_profiles | postgres | | f | f | f | f (7 rows)
获取某个表的字段信息
postgres=# SELECT table_schema, table_name, column_name, ordinal_position, postgres-# column_default, data_type, udt_name postgres-# FROM information_schema.columns sc WHERE table_schema = 'information_schema' AND table_name = 'sql_features'; table_schema | table_name | column_name | ordinal_position | column_default | data_type | udt_name --------------------+--------------+------------------+------------------+----------------+-------------------+---------- information_schema | sql_features | feature_id | 1 | | character varying | varchar information_schema | sql_features | feature_name | 2 | | character varying | varchar information_schema | sql_features | sub_feature_id | 3 | | character varying | varchar information_schema | sql_features | sub_feature_name | 4 | | character varying | varchar information_schema | sql_features | is_supported | 5 | | character varying | varchar information_schema | sql_features | is_verified_by | 6 | | character varying | varchar information_schema | sql_features | comments | 7 | | character varying | varchar (7 rows)
