Linux 下安装
1
2
3
apt update
apt install postgresql
Copy 安装完之后执行 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
Copy 原因是没有 root
用户,此时postgres
安装成功后会有一个默认用户 postgres
,可以切换用户,执行以下脚本
1
2
root@982-dev:/mnt/d# su postgres
postgres@982-dev:/mnt/d$
Copy 然后再执行psql
命令
1
2
3
postgres@982-dev:/mnt/d$ psql
psql ( 14.13 ( Ubuntu 14.13-0ubuntu0.22.04.1))
Type "help" for help.
Copy 此时已经安装成功并登录了数据库
数据库操作命令
\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)
Copy
创建数据库 CREATE DATABASE 数据库名;
1
2
postgres = # create database game;
CREATE DATABASE
Copy
功能类似 mysql 的 use 数据库名;
1
2
postgres = # \c game;
You are now connected to database "game" as user "postgres" .
Copy
1
2
game = # \dt
Did not find any relations.
Copy 数据表操作命令
创建表 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
);
Copy
1
comment on column t_user . id is '自增主键' ;
Copy
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 )
Copy
1
2
game =# drop table t_user ;
DROP TABLE
Copy
Licensed under CC BY-NC-SA 4.0