PostgreSQL 常用命令

PostgreSQL 常用命令

Linux 下安装

1
2
3
apt update

apt install postgresql

安装完之后执行 psql,有报错信息

1
2
root@982-dev:/mnt/d# psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

原因是没有 root 用户,此时postgres安装成功后会有一个默认用户 postgres,可以切换用户,执行以下脚本

1
2
root@982-dev:/mnt/d# su postgres
postgres@982-dev:/mnt/d$

然后再执行psql命令

1
2
3
postgres@982-dev:/mnt/d$ psql
psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1))
Type "help" for help.

此时已经安装成功并登录了数据库

数据库操作命令

  • 查看数据库 \l

\l 命令类似 mysql数据库中的 show databases 命令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)
  • 创建数据库 CREATE DATABASE 数据库名;
1
2
postgres=# create database game;
CREATE DATABASE
  • 切换数据库 \c 数据库名;

功能类似 mysql 的 use 数据库名;

1
2
postgres=# \c game;
You are now connected to database "game" as user "postgres".
  • 删除数据库 DROP DATABASE 数据库名;

  • 查看表 \dt

1
2
game=# \dt
Did not find any relations.

数据表操作命令

  • 创建表 CREATE TABLE 表名(字段名 数据类型, ...);
1
2
3
4
5
6
7
8
9
create table t_user
(
    id serial not null primary key, -- 自增可以用 serial
    name varchar(32) not null default '', 
    phone varchar(32) not null default '',
    updated_at date not null default current_date,
    created_at date not null default current_date,
    deleted smallint not null default 0
);
  • 添加注释
1
comment on column t_user.id is '自增主键';
  • 查看表结构
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
game=# \d t_user;
                                         Table "public.t_user"
   Column   |              Type              | Collation | Nullable |              Default
------------+--------------------------------+-----------+----------+-----------------------------------
 id         | integer                        |           | not null | nextval('user1_id_seq'::regclass)
 name       | character varying(32)          |           | not null | ''::character varying
 phone      | character varying(32)          |           | not null | ''::character varying
 updated_at | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 created_at | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 deleted    | smallint                       |           | not null | 0
Indexes:
    "user1_pkey" PRIMARY KEY, btree (id)
  • 删除表 DROP TABLE 表名;
1
2
game=# drop table t_user;
DROP TABLE
皖ICP备20014602号
Built with Hugo
Theme Stack designed by Jimmy