pgsql基本操作

\l # 查看所有数据库
\c # 切换数据库
\di # 查看索引
\dt # 查看表
\ds # 查看序列
\q # 推出
\timing on # 开机sql查询时间
\dns # 查看schema
\du # 查看用户

[postgres@pg01 ~]$ ps -ef|grep postgres
[postgres@pg01 ~]$ psql -Upostgres -d postgres
psql (10.4)
Type "help" for help.

postgres=# \l #查看所有数据库

postgres=# create database test; #创建数据库
CREATE DATABASE

postgres=# \c test; #切换数据库
You are now connected to database "test" as user "postgres".

test=# \dns #查看schema
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)
test=# create schema data;
CREATE SCHEMA
test=# \dns
  List of schemas
  Name  |  Owner   
--------+----------
 data   | postgres
 public | postgres
(2 rows)

test=# create table data.test (id int,name varchar(64)); #创建表
CREATE TABLE
test=# \dt data.* #查看表
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 data   | test | table | postgres
(1 row)

test=# create table test1 (id int,name varchar(64),unique (id)); #创建表
CREATE TABLE
test=# \dt #查看表
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

test=# \di #查看索引
                List of relations
 Schema |     Name     | Type  |  Owner   | Table 
--------+--------------+-------+----------+-------
 public | test1_id_key | index | postgres | test1
(1 row)

sql执行时间

test=# \timing on
Timing is on.
test=# select * from test1;
 id | name 
----+------
(0 rows)

Time: 0.920 ms

查看用户

test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}