psql元命令
1.查看数据库列表
mydb=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/pguser +
| | | | | pguser=C*T*c*/pguser
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
登录时使用psql -E可以查看具体执行\l等元命令的具体执行的SQL，示例如下：
postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | pguser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/pguser +
| | | | | pguser=C*T*c*/pguser
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
2.查看表空间列表
表空间信息可查pg_catalog.pg_tablespace表
postgres=# \db
********* QUERY **********
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************
List of tablespaces
Name | Owner | Location
------------+----------+---------------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_mydb | pguser | /vastbase/postgres/10/pg_tbs/tbs_mydb
3.查看表的定义信息
mydb=#\c mydb // \c表示切换到mydb库中
You are now connected to database "mydb" as user "postgres".
mydb=# create table test1(id int,name varchar(20));
CREATE TABLE
mydb=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
mydb=# alter table test1 add primary key(id);
ALTER TABLE
mydb=# \d test1;
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
4.查看表和索引的空间大小
使用generate_series（start,stop）函数生成数据
mydb=# INSERT INTO test1(id,name) SELECT n,n ||'_francs' FROM generate_series(1,5000000) n;
INSERT 0 5000000
mydb=# \dt+ test1
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------+-------+----------+--------+-------------
public | test1 | table | postgres | 249 MB |
(1 row)
mydb=# \di+ test1_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+------------+-------+----------+-------+--------+-------------
public | test1_pkey | index | postgres | test1 | 107 MB |
(1 row)
mydb=#
5.\sf查看函数代码
可以查看函数的定义
postgres=# \sf random
CREATE OR REPLACE FUNCTION pg_catalog.random()
RETURNS double precision
LANGUAGE internal
PARALLEL RESTRICTED STRICT
AS $function$drandom$function$
postgres=#
6.\x，将结果竖向输出，执行完成之后可以再次执行\x将竖向输出关闭，该功能类似MySQL的\G
mydb=# \x
Expanded display is on.
mydb=# select * from test1 limit 10;
-[ RECORD 1 ]---
id | 1
name | 1_francs
-[ RECORD 2 ]---
id | 2
name | 2_francs
-[ RECORD 3 ]---
id | 3
name | 3_francs
-[ RECORD 4 ]---
id | 4
name | 4_francs
-[ RECORD 5 ]---
id | 5
name | 5_francs
-[ RECORD 6 ]---
id | 6
name | 6_francs
-[ RECORD 7 ]---
id | 7
name | 7_francs
-[ RECORD 8 ]---
id | 8
name | 8_francs
-[ RECORD 9 ]---
id | 9
name | 9_francs
-[ RECORD 10 ]--
id | 10
name | 10_francs
mydb=#
mydb=#
mydb=# \x
Expanded display is off.
mydb=# select * from test1 limit 10;
id | name
----+-----------
1 | 1_francs
2 | 2_francs
3 | 3_francs
4 | 4_francs
5 | 5_francs
6 | 6_francs
7 | 7_francs
8 | 8_francs
9 | 9_francs
10 | 10_francs
(10 rows)
7.*/？通配符
mydb=# create table test2 (id int,name varchar(20));
CREATE TABLE
mydb=#
mydb=# \d 查看mydb下的所有表，目前只有test1和test2
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | postgres
public | test2 | table | postgres
(2 rows)
mydb=# \d test? 找出test开头的表，？表示只通配一个字符，但是目前只有test1和test2两张表看不出来
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
Table "public.test2"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
mydb=# create table test22 (id int,name varchar(20)); 创建test22
CREATE TABLE
mydb=# \d test? 可以看到查到的表只有test1和test2
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
Table "public.test2"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
mydb=# \d test* 可以看到查到的表有test1，test2以及test22
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
Index "public.test1_pkey"
Column | Type | Definition
--------+---------+------------
id | integer | id
primary key, btree, for table "public.test1"
Table "public.test2"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
Table "public.test22"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
mydb=#
7.匹配查询不同对象
\df 显示函数
\ds 显示序列
\dv 显示视图
\di 显示索引
\dt 显示表
例：
mydb=# \df random
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+--------
pg_catalog | random | double precision | | normal
(1 row)
mydb=# \df+ random
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner |
Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+--------+------------+------------+----------+-
---------+-------------------+----------+-------------+--------------
pg_catalog | random | double precision | | normal | volatile | restricted | postgres |
invoker | | internal | drandom | random value
(1 row)
mydb=#
8.列出所有的schema
mydb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
9.列出数据库中的所有角色和用户
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
pguser | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
pguser | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
10.\dp或者\z显示表的权限分配
mydb=# \dp test1;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+-------------------+-------------------+----------
public | test1 | table | | |
(1 row)
