PostgreSQL教程

登录数据库

psql -U group_user -h 127.0.0.1 -p 5432 -d groupdb -W

查看所有用户

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

创建新用户

初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。
下面，我们使用postgres用户，来生成一个新用户
1、切换到postgres用户

su - postgres

2、使用psql命令登录PostgreSQL控制台。

psql

这时相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。如果一切正常，系统提示符会变为"postgres=#"，表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。
3、创建数据库用户test，并设置密码。

CREATE USER test WITH PASSWORD 'test';

修改用户密码

登录数据库后，在输入框输入以下命令：

ALTER USER postgres WITH PASSWORD 'xxx';

将 xxx 换成你想修改的密码即可。

这里修改的是用户名为 postgres 的用户，数据库默认的用户也是这个。如果你想修改其他用户，将 postgres 改成对应的用户名， xxx 换成你想修改的密码就可以了。

创建新数据库

首先使用系统用户postgres以同名数据库用户的身份，登录数据库
1、创建数据库testdb，并指定所有者test

CREATE DATABASE testdb OWNER test;

2、将testdb数据库的所有权限都赋予test，否则test只能登录控制台，没有任何数据库操作权限。

GRANT ALL PRIVILEGES ON DATABASE testdb to test;

选择数据库

1、使用 \l 用于查看已经存在的数据库：

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
 testdb    | test     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test             +
           |          |          |             |             | test=CTc/test
(4 rows)

2、接下来我们可以使用 \c + 数据库名 来进入数据库：

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=#

创建表

1、查看当前数据库下的表

testdb=# \d
No relations found.

2、创建表

CREATE TABLE public.staff (
	id SERIAL PRIMARY key,
	staff_name text NOT NULL,
	staff_id text unique NOT NULL,
	team text NOT NULL,
	area text NOT NULL,
	dept text NOT NULL
);

testdb=# CREATE TABLE public.staff (
testdb(# id SERIAL PRIMARY key,
testdb(# staff_name text NOT NULL,
testdb(# staff_id text unique NOT NULL,
testdb(# team text NOT NULL,
testdb(# area text NOT NULL,
testdb(# dept text NOT NULL
testdb(# );
NOTICE:  CREATE TABLE will create implicit sequence "staff_id_seq" for serial column "staff.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "staff_pkey" for table "staff"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "staff_staff_id_key" for table "staff"
CREATE TABLE

3、查看是否创建成功

testdb=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | staff        | table    | postgres
 public | staff_id_seq | sequence | postgres
(2 rows)

修改表名

alter table 表名 rename to 新表名

新增列

语法如下：

alter table table_name add column column_name field_type; //field_type可以为serial

修改字段名

1、查看当前表的字段

testdb=# \d indicator 
       Table "public.indicator"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 ind_id         | integer | not null
 dim_id         | integer | 
 indicator_name | text    | not null
Indexes:
    "pk_indicator" PRIMARY KEY, btree (ind_id)
Foreign-key constraints:
    "fk_indicato_dim_indic_dimensio" FOREIGN KEY (ind_id) REFERENCES dimension(dim_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "model_indicator_r" CONSTRAINT "fk_model_in_model_ind_indicato" FOREIGN KEY (ind_id) REFERENCES indicator(ind_id) ON UPDATE CASCADE ON DELETE CASCADE

2、修改字段名称

ALTER TABLE indicator RENAME indicator_name TO ind_name;

3、查看是否创建成功

testdb=# \d indicator 
    Table "public.indicator"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 ind_id   | integer | not null
 dim_id   | integer | 
 ind_name | text    | not null
Indexes:
    "pk_indicator" PRIMARY KEY, btree (ind_id)
Foreign-key constraints:
    "fk_indicato_dim_indic_dimensio" FOREIGN KEY (ind_id) REFERENCES dimension(dim_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "model_indicator_r" CONSTRAINT "fk_model_in_model_ind_indicato" FOREIGN KEY (ind_id) REFERENCES indicator(ind_id) ON UPDATE CASCADE ON DELETE CASCADE

修改字段类型

ALTER TABLE table_name ALTER COLUMN column_name TYPE timestamptz(0) USING column_name::timestamptz;

修改字段长度

···shell
alter table tbl_exam alter column question type character varing(1024);
alter table tbl_exam alter column question type numeric(18,4);
···

删除列

语法如下：

alter table table_name drop column column_name;

增加主键

语法如下：

alter table table_name add primary key (column_name);

添加唯一约束

alter table goods add constraint unique_goods_sid unique (sid);

删除约束

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;

问题及解决

远程操作报错permission denied for relation staff

问题描述

[2020/04/13 17:20:53.734] [ERROR] [/media/E/Go_workspace/go_online/lib/net/http/blademaster/server.go:71] err=pq: permission denied for relation staff ts=0.049837968 traceid= ip=xx.xx.xx.xx msg=-500 params= mid=<nil> user=no_user ret=-500 stack=pq: permission denied for relation staff timeout_quota=599.99998984 method=POST path=/x/admin/staff/add

问题原因
对表无操作全写
解决方案

testdb=# grant all privileges on table staff to test;
GRANT

远程操作报错permission denied for sequence staff_id_seq

问题描述

[2020/04/13 17:34:06.536] [ERROR] [/media/E/Go_workspace/go_online/lib/net/http/blademaster/server.go:71] ip=xx.xx.xx.xx path=/x/admin/staff/add params= timeout_quota=599.999988828 mid=<nil> user=no_user ret=-500 ts=0.013815531 method=POST msg=-500 traceid= stack=pq: permission denied for sequence staff_id_seq err=pq: permission denied for sequence staff_id_seq

问题原因
报这个错误的原因是对自增序列没有给对应角色授权，但是对表授予了该角色操作权限导致。
解决方案

testdb=# GRANT USAGE, SELECT ON SEQUENCE staff_id_seq TO test;
GRANT

可以使用以下方式将权限授予模式中的所有序列：

testdb=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO test;
GRANT

客户端命令行：表名、列名、用户名会被自动转化为小写

解决方案
，设计数据库时，表名、列名、用户名全部用小写，这样可以尽量减少问题的出现，实在需要用大写字母，可以通过在命令行中添加双引号。

postgreSQL里的保留字：user、group等建议不要用于数据库名或表名

批量插入报错duplicate key value violates unique constraint…DETAIL: Key (team_id)=(12) already exists.（自增键值重复冲突）

问题原因
序列(sequence)的当前值太小，导致在插入数据表时, 产生主键冲突.。
解决方案
把seq的值改大

select max(id) from tablename;
SELECT nextval('tablename_id_seq');
select setval('tablename_id_seq',(select max(id) from tablename)+1);