PG客户端常用命令

最新推荐文章于 2025-04-19 11:54:05 发布

海鲨数据库架构师:曾凡坤.姒

最新推荐文章于 2025-04-19 11:54:05 发布

阅读量435

点赞数 9

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/ZengMuAnSha/article/details/144001445

版权

由于PG客户端PGSQL不太支持很多SQL命令,比如DESC!
PG使用元命令来替代,注意元命令是反斜杠开始"\"

SHOW DATABASES 使用\l 小写的L

SHARKSQL> \l                                                  List of databases+-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------+|   Name    | Owner | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules | Access privileges |+-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------+| postgres  | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |                   || template0 | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/shark         +||           |       |          |                 |            |            |        |           | shark=CTc/shark   || template1 | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/shark         +||           |       |          |                 |            |            |        |           | shark=CTc/shark   || testdb    | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =Tc/shark        +||           |       |          |                 |            |            |        |           | shark=CTc/shark   |+-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------+(4 rows

SHOW TABLES

查看当前数据库下所有表

SHARKSQL> \d         List of relations+--------+--------+-------+-------+| Schema |  Name  | Type  | Owner |+--------+--------+-------+-------+| public | mytest | table | shark |+--------+--------+-------+-------+(1 row)

查看表结构 DESC MYTEST

SHARKSQL> \d mytest                       Table "public.mytest"+--------+-----------------------+-----------+----------+---------+| Column |         Type          | Collation | Nullable | Default |+--------+-----------------------+-----------+----------+---------+| id     | integer               |           | not null |         || name   | character varying(30) |           |          |         |+--------+-----------------------+-----------+----------+---------+Indexes:    "mytest_pkey" PRIMARY KEY, btree (id)

切换数据库

USER TESTDB

SHARKSQL> \c postgresYou are now connected to database "postgres" as user "shark".

查看当前链接信息,或者是当前数据库

SHARKSQL> \conninfo You are connected to database "testdb" as user "shark" via socket in "/tmp" at port "5432".SHARKSQL> \connectYou are now connected to database "testdb" as user "shark".

垂直显示记录

SELECT * FROM MYTEST\G ;

SHARKSQL> \xExpanded display is on.SHARKSQL> select * from mytest;[ RECORD 1 ]-| id   | 1       || name | Jack    |[ RECORD 2 ]-| id   | 2       || name | Tonny   |[ RECORD 3 ]-| id   | 3       || name | Acebaby |----+--------
Time: 3.782 msSHARKSQL> \xExpanded display is off.

执行OS命令:

SHARKSQL> \! lsbooks   pg_shark.sql  scripts  source         update_rc.trace    update_rc.trace.old  模板  图片  下载  桌面Desktop  projects      soft  Untitled.rcnb  update_rc.trace.2  公共           视频  文档  音乐

注意\! 命令之间有空格

显示表更多信息

SHARKSQL> \dS+ mytest                                                  Table "public.mytest"+--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+| Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description |+--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+| id     | integer               |           | not null |         | plain    |             |              |             || name   | character varying(30) |           |          |         | extended |             |              |             |+--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------+Indexes:    "mytest_pkey" PRIMARY KEY, btree (id)Access method: heap

\dS+ 注意S大写

查看权限:

SHARKSQL> \du                              List of roles+-----------+------------------------------------------------------------+| Role name |                         Attributes                         |+-----------+------------------------------------------------------------+| shark     | Superuser, Create role, Create DB, Replication, Bypass RLS || shark?     |                                                            |+-----------+------------------------------------------------------------+

显示元命令的SQL

SHARKSQL> \set ECHO_HIDDEN on

SHARKSQL> \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",  CASE d.datlocprovider WHEN 'b' THEN 'builtin' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",  d.datcollate as "Collate",  d.datctype as "Ctype",  d.datlocale as "Locale",  d.daticurules as "ICU Rules",  CASE WHEN pg_catalog.array_length(d.datacl, 1) = 0 THEN '(none)' ELSE pg_catalog.array_to_string(d.datacl, E'\n') END AS "Access privileges"FROM pg_catalog.pg_database dORDER BY 1;/************************/
                                                  List of databases+-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------+|   Name    | Owner | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules | Access privileges |+-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------+| postgres  | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |                   || template0 | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/shark         +||           |       |          |                 |            |            |        |           | shark=CTc/shark   || template1 | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/shark         +||           |       |          |                 |            |            |        |           | shark=CTc/shark   || testdb    | shark | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =Tc/shark        +||           |       |          |                 |            |            |        |           | shark=CTc/shark   |+-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------+(4 rows)
SHARKSQL>