首页 > 代码库 > postgresql的/d命令
postgresql的/d命令
ostgreSQL-psql常用命令
文章索引
[隐藏]
- \d命令
\d命令
1
2
3
|
格式:
\d [ pattern ]
\d [ pattern ] +
|
该命令将显示每个匹配关系(表,视图,索引,序列)的信息,包括对象所有的列,它们的类型,表空间(如果不是默认的)和任何特殊属性(如NOT NULL或默认值等)。与唯一约束相关的索引,规则,约束,触发器也同样会显示出来。如果关系是一个视图,还会显示视图的定义。
1.如果\d命令什么都不带,将列出当前数据库中的所有表。
1
2
3
4
5
6
7
8
9
10
11
12
|
sample_db=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+---------
public | account | table | postgre
public | book | table | postgre
public | customers | table | postgre
public | fruits | table | postgre
...
public | view_t | view | postgre
public | view_t2 | view | postgre
(42 rows)
|
2.\d后面跟一个表名,表示显示这个表的结构定义。
1
2
3
4
5
6
7
8
9
10
11
12
|
sample_db=# \d tb_dept2
Table "public.tb_dept2"
Column | Type | Modifiers
----------+-----------------------+-----------
id | integer | not null
name | character varying(22) |
location | character varying(50) |
Indexes:
"tb_dept2_pkey" PRIMARY KEY, btree (id)
"tb_dept2_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
TABLE "tb_tmp" CONSTRAINT "fk_emp_dept" FOREIGN KEY (deptid) REFERENCES tb_dept2(id)
|
3.\d也可以显示索引信息
1
2
3
4
5
6
|
sample_db=# \d tb_dept2_pkey
Index "public.tb_dept2_pkey"
Column | Type | Definition
--------+---------+------------
id | integer | id
primary key, btree, for table "public.tb_dept2"
|
4.\d后面可以跟一通配符"*"或"?"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
sample_db=# \d boo?
Table "public.book"
Column | Type | Modifiers
------------------+------------------------+-----------
bookid | integer | not null
bookname | character varying(255) | not null
authors | character varying(255) | not null
info | character varying(255) |
comment | character varying(255) |
year_publication | date | not null
Indexes:
"uniqididx" UNIQUE, btree (bookid)
"bkcmtidx" btree (comment)
"bknameidx" btree (bookname)
sample_db=# \d a*
Table "public.account"
Column | Type | Modifiers
--------+---------------+-----------
id | integer |
name | character(20) |
Triggers:
account_stamp BEFORE INSERT ON account FOR EACH ROW EXECUTE PROCEDURE account_stam()
|
5.\d+命令,该命令将显示比\d命令更详细的信息,除了前面介绍的那些,它还会显示任何与表列关联的注释,以及表中出现的ODI。
1
2
3
4
5
6
7
|
sample_db=# \d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
----------+---------+-----------+---------+--------------+-------------
quantity | integer | | plain | |
price | integer | | plain | |
Has OIDs: no
|
6.匹配不同对象类型的\d命令
1
2
3
4
5
|
如果想只显示匹配的表,可以使用\dt命令
如果想只显示索引,可以使用\di命令
如果想只显示序号,可以使用\ds命令
如果想只显示视图,可以使用\dv命令
如果想只显示函数,可以使用\df命令
|
7.如果想显示SQL已执行的时间,可以用\timing命令
1
2
3
4
5
6
7
8
9
|
sample_db=# \timing on
Timing is on.
sample_db=# select count(*) from t;
count
-------
1
(1 row)
Time: 0.348 ms
|
8.列出所有的schemas可以使用\dn命令
1
2
3
4
5
6
|
sample_db=# \dn
List of schemas
Name | Owner
--------+---------
public | postgre
(1 row)
|
9.显示所有的表空间可以用\db命令
1
2
3
4
5
6
7
|
sample_db=# \db
List of tablespaces
Name | Owner | Location
------------+---------+----------
pg_default | postgre |
pg_global | postgre |
(2 rows)
|
表空间就是对一个目录,放在这个表空间的表,就是把表的数据文件放到这个表空间下。
10.列出数据库所有角色或用户\du或\dg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
sample_db=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bob | | {}
linux78 | | {}
post4 | | {}
post5 | Superuser, Cannot login | {}
post6 | Create DB, Cannot login | {}
post7 | Create role, Cannot login | {}
post8 | Cannot login | {}
postgre | Superuser, Create role, Create DB, Replication | {}
sample_db=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bob | | {}
linux78 | | {}
post4 | | {}
post5 | Superuser, Cannot login | {}
post6 | Create DB, Cannot login | {}
post7 | Create role, Cannot login | {}
post8 | Cannot login | {}
postgre | Superuser, Create role, Create DB, Replication | {}
|
11.\dp或\z命令用于显示表的权限分配情况
1
2
3
4
5
6
|
sample_db=# \dp t
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+-------------------+--------------------------
public | t | table | |
(1 row)
|
postgresql的/d命令
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。