查看表结构
1.1SQL查询
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull FROM pg_class c,pg_attribute a,pg_type t WHERE c.relname='t1' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid postgres=# SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull postgres-# FROM pg_class c,pg_attribute a,pg_type t postgres-# WHERE c.relname='t1' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid postgres-# ; attnum | field | type | length | lengthvar | notnull --------+-------+------+--------+-----------+--------- 1 | id | int4 | 4 | -1 | t
1.2快捷查询
postgres-# \d+ t1; Table "public.t1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | not null | | plain | 10000 | Indexes: "pk_t1_id" PRIMARY KEY, btree (id) "idx_t1_id" btree (id) Tablespace: "ts_data" Access method: heap Options: autovacuum_vacuum_cost_delay=10, autovacuum_vacuum_threshold=10000, autovacuum_analyze_threshold=10000, autovacuum_vacuum_cost_limit=10000, autovacuum_vacuum_scale_factor=0, autovacuum_analyze_scale_factor=0
2.查看锁定表
SELECT pg_class.relname AS table, pg_database.datname AS database, pid, mode, granted FROM pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid; postgres=# SELECT pg_class.relname AS table, pg_database.datname AS database, pid, mode, granted postgres-# FROM pg_locks, pg_class, pg_database postgres-# WHERE pg_locks.relation = pg_class.oid postgres-# AND pg_locks.database = pg_database.oid; table | database | pid | mode | granted -----------------------------------+----------+-------+-----------------+--------- pg_class_tblspc_relfilenode_index | postgres | 34425 | AccessShareLock | t pg_class_relname_nsp_index | postgres | 34425 | AccessShareLock | t pg_class_oid_index | postgres | 34425 | AccessShareLock | t pg_class | postgres | 34425 | AccessShareLock | t pg_locks | postgres | 34425 | AccessShareLock | t (5 rows)
3.查看数据库大小
postgres=# SELECT pg_size_pretty(pg_database_size('POSTGRES')) As fulldbsize;
fulldbsize
------------
5151 MB
(1 row)
4.快捷查询
4.1查看schema/owner关系
postgres-# \dn
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
(5 rows)
4.2、查看管理员用户
postgres-# \du
List of roles
Role name | Attributes | Member of
-------------+-----------------------------------+-----------
gisplatform | | {}
postgres | Superuser, Create role, Create DB | {}
4.3、查看数据库列表(dl)--l不是1
postgres-# \dl
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------------+-----------------------
postgis | postgres | UTF8 | C | en_US.UTF-8 |
postgres | postgres | UTF8 | C | en_US.UTF-8 |
template0 | postgres | UTF8 | C | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
