PostgreSQL常用操作命令&常见错误

PostgreSQL常用操作命令

登录数据库shell命令行
语法：psql -U User [[-d] DB]
不指定DB时默认登录用户名相同的DB
psql -U postgres -d postgres
psql -U postgres postgres
psql -U postgres
psql -U test -d testdb01
psql -U test testdb01

查看版本信息
show server_version;
show server_version_num;
psql --version
su - postgres
$ psql -version
select version();

创建数据库新用户
CREATE USER test WITH PASSWORD '1q2w3e';
注意：语句要以分号结尾，密码要用单引号括起来。

显示所有数据库(相当于show databases;)
\l

创建用户数据库
CREATE DATABASE testdb01 OWNER test;

将数据库的所有权限赋予用户
GRANT ALL PRIVILEGES ON DATABASE testdb01 TO test;

修改数据库的owner为test用户(如果创建时未指定owner、也可以通过alter命令指定）
alter database testdb01 owner to test;

将schema中all权限赋予给指定用户（管理员账号在对应DB中执行grant命令授权，授权给test用户为例）
\c testdb01
\c - postgres
grant all on schema public to test;

获取当前db中所有的表信息:
select * from pg_tables;

查看用户的所有表（用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下）
select tablename from pg_tables where schemaname='public';

切换数据库
\c testdb01

切换用户
\c - test

也可以登录时指定数据库
psql -U test testdb01

获取当前db中所有的表信息:
select * from pg_tables;

查看用户的所有表（用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下）
select tablename from pg_tables where schemaname='public';

创建表 & 插入数据
create table test01(
id integer not null,
name character(255) not null,
price decimal(8,2) not null,
primary key(id)
);

insert into test01(id,name,price) values (1,'a',11.5),(2,'b',20.3);

查看表结构
\d test01;
\d+ test01;

查看表的数据
select * from test01;

PostgreSQL查询表的实际数据存储类型：
SELECT
col.ordinal_position,
col.column_name ,
col.udt_name ,
col.character_maximum_length,
col.numeric_precision,
col.numeric_scale,
col.is_nullable,
des.description
FROM
information_schema.columns col LEFT JOIN pg_description des
ON col.table_name::regclass = des.objoid
AND col.ordinal_position = des.objsubid
WHERE
table_name = 'mytable' ;

PostGIS(geometry)时空数据类型相关
#geom_test01
CREATE TABLE geom_test01(id integer not null, name varchar(255), primary key(id));
SELECT AddGeometryColumn('geom_test01', 'zone_geom', 4326, 'POINT', 2);

INSERT INTO geom_test01(id, zone_geom, name) VALUES (1, ST_GeomFromText('POINT(-0.1250 52.500)',4326), 'test');
INSERT INTO geom_test01(id, zone_geom, name) VALUES (2, ST_GeomFromText('POINT(27.91162480 -33.01532)', 4326),'test');

SELECT * FROM geom_test01;
SELECT id, ST_AsText(zone_geom), ST_AsEwkt(zone_geom), ST_X(zone_geom), ST_Y(zone_geom) FROM geom_test01;

#geom_test02
CREATE TABLE geom_test02(
id integer not null,
zone_geom geometry(point, 4326),
name varchar(255),
primary key(id)
);

INSERT INTO geom_test02(id, zone_geom, name) values (1, st_geomfromtext('point(27.91162480 -33.01532)', 4326), 'aaa');
SELECT * FROM geom_test02;
SELECT id, ST_AsText(zone_geom), ST_AsEwkt(zone_geom), ST_X(zone_geom), ST_Y(zone_geom) FROM geom_test02;

修改密码
ALTER USER postgres with encrypted password '1q2w3e';
ALTER USER test with encrypted password '1q2w3e';

密码过期处理
远程连接提示密码过期报错：ERROR: Your password has expired, please alter password first
解决办法：
方法1）可以通过管理员postgres用户本地shell登录后修改指定用户的密码解决
psql -U postgres -d postgres
ALTER USER test with encrypted password '1q2w3e';

方法2）也可以通过过期用户本地shell登录后修改密码解决；
psql -U test -d testdb01
ALTER USER test with encrypted password '1q2w3e';

查看当前连接用户
select * from current_user;
select user;
如果在shell命令行下也可以执行 \conninfo 查看当前用户和数据库
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#

查看帮助：
postgres=# Help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=#

查看SQL命令帮助：

postgres=# \h
Available help:
ABORT ALTER TEXT SEARCH TEMPLATE CREATE PUBLICATION ......
......

postgres=# \h show
Command: SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

URL: https://www.postgresql.org/docs/12/sql-show.html

查看所有参数的设置：
postgres=# show all;

等同于
postgres=# select * from pg_settings;

所有参数配置可以通过 pg_settings 视图 select 查看

查看 pg_settings 视图结构：

如果想要模糊查询，则通过 pg_settings 视图模糊查询：

testdb01=> select name, setting, short_desc from pg_settings where name like '%version%';

查看参数设置：
postgres=# show port;
postgres=# show max_connections;
postgres=# show max_wal_size;

查看参数配置文件的位置：
postgres=# show config_file;
postgres=# show hba_file;
postgres=# show ident_file;

查看psql命令帮助：
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
......
......

查看系统所有用户角色权限
\du[S+] [PATTERN] list roles
\du
\du test*
\duS
\duS+

查看当前连接用户和数据库
\conninfo
postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

\c[onnect] 也能达到类似效果
postgres-# \c
You are now connected to database "postgres" as user "postgres".
postgres-#
postgres-# \connect
You are now connected to database "postgres" as user "postgres".

查看表结构
\d tableName

\d 命令
\d 命令可显示每个匹配关系（表，视图，索引，序列）的信息，包括对象所有的列，类型，表空间（如果不是默认的）和任何特殊属性（如NOT NULL或默认值等），以及表大小（Size）、表注释等。与唯一约束相关的索引，规则，约束，触发器也同样会显示出来。如果关系是一个视图，还会显示视图的定义。
如果\d命令什么都不带，将列出当前数据库中的所有表。

\d, \d+
\dt, \dt+
\dn, \dn+
\d pg_tables; ?#也可以通过select查询 select * from pg_tables;
\dt *.
\d public.*
\d public02.*
\d test01
\d public.test01
\dt public.*

\d 显示当前数据库中的所有的表
\d test01 显示test01表的详情
\dt 只显示匹配的表
\dt test* 只列出test开头的表
\dn 显示所有Schema
\dv 显示视图
\di 显示索引
\ds 显示序号
\df 显示函数
\du, \dg 列出数据库所有角色或用户
\dp, \z 显示所有表、视图、sequence的访问权限
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions

注意：
1）\d后面跟一个表名，表示显示这个表的结构定义，如果\d后面跟索引名则显示索引信息。
2）\d+：在显示表或索引等对象时，如果带+号可以显示比\d命令更详细的信息，除了前面介绍的那些，它还会显示任何与表列关联的注释，以及表中出现的ODI，如表大小（Size）等信息
3）\d后面支持通配符*或?

eg:
查看表结构
\d test01
\d geom_test01
\dt test*

显示SQL执行时间
可以执行 \timing on/off 命令后再执行SQL语句
testdb01=> \timing on
Timing is on.
testdb01=> select * from test01;
1 | a | 11.50
2 | b | 20.30
Time: 0.552 ms

查看schema/设置schema（如果不设置schema，查询时默认为 public）
show search_path;
set search_path to public;
set search_path to public, public02;
注意：仅当前会话生效，如果要数据库全局生效，则执行: alter database "testdb01" set search_path TO public, public02;

创建schema
创建schema public02并授权给test用户all权限
create schema public02;
grant all on schema public02 to test;
set search_path to public02;
注意：仅当前会话生效，如果要数据库全局生效，则执行: alter database "testdb01" set search_path TO public02;

创建索引
create index idx_name on table_name(column_name);

查看历史操作记录(命令行执行记录)
\s

查看字符集
\encoding

查看session信息：
select * from pg_stat_activity;

杀会话/杀进程（kill session）：
select * from pg_stat_activity;
select pg_terminate_backend(pid);

显示当前默认模式
show search_path

更改默认模式
set search_path to myschema

切换显示模式（行显示|列显示）
\x

创建视图：
CREATE OR REPLACE VIEW v_test01 AS SELECT id,name from test01;

显示视图：
\dv

pg_hba.conf 配置文件修改后重载控制文件的方法（三种方法）：

方法1. pg_ctl reload -D your_data_directory_here

例如：
先确认data目录，执行命令：
-bash-4.2$ ps aux |grep "\-D" |grep pgsql
postgres 945 0.0 2.5 1056000 202208 ? Ss 2021 181:43 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/

在重载生效配置（不能用root用户执行）
-bash-4.2$ pg_ctl reload -D /var/lib/pgsql/12/data/
server signaled

方法2. service postgresql-12 reload
postgresql-12 是服务名(ServiceName)

例如：
[root@pgmaster ~]# service postgresql-12 reload
Redirecting to /bin/systemctl reload postgresql-12.service
[root@pgmaster ~]#

方法3. select pg_reload_conf();

例如：
[root@pgmaster ~]# psql -U postgres
psql (12.8)
Type "help" for help.

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

postgres=#

PG常见错误：

> PG删除用户报错：
drop user test;

ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public

解决方案：

先回收用户权限、再删除
revoke all on schema public from test;
drop user test;

> 新建的PG用户创建表报错：permission denied for schema public

CREATE USER test WITH PASSWORD '1q2w3e';
CREATE DATABASE testdb01 OWNER test;
GRANT ALL PRIVILEGES ON DATABASE testdb01 TO test;
\c testdb01
\c - test
create table test01( id integer not null, name character(255) not null, price decimal(8,2) not null, primary key(id) );
创建表报错：
ERROR: permission denied for schema public

解决方案：
将schema(public)中all权限赋予给test用户（管理员账号在对应DB中执行grant schema 授权）
\c testdb01
\c - postgres
grant all on schema public to test;

切换用户重新创建表
\c - test
create table test01( id integer not null, name character(255) not null, price decimal(8,2) not null, primary key(id) );
insert into test01(id,name,price) values (1,'a',11.5),(2,'b',20.3);
执行成功

> 密码过期时，远程连接报错
远程连接提示密码过期报错：ERROR: Your password has expired, please alter password first
解决办法：
方法1）可以通过管理员postgres用户本地shell登录后修改指定用户的密码解决
psql -U postgres -d postgres
ALTER USER test with encrypted password '1q2w3e';

方法2）也可以通过过期用户本地shell登录后修改密码解决；
psql -U test -d testdb01
ALTER USER test with encrypted password '1q2w3e';

PostgreSQL中的序列自增

设置表字段的自增属性，PG有三种方式：serial、sequence、identity；

创建表时设置字段 serial 属性，系统会自动创建 sequence （系统自动命名、序列名包含表名）并关联到表对应的字段，drop表时也会自动删除对应的序列；

另外也可以单独创建sequence、再alter table 设置对应字段将序列值赋值给主键：

CREATE SEQUENCE table_xxx_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

alter table_xxx alter column id set default nextval(table_xxx_seq'); -- 设置id字段自增

但这种方式删除表时不会自动删除sequence；

-- test
create table test52_serial (id serial primary key, info varchar null, cnt int null);
-- CREATE TABLE test52_serial (id int4 NOT NULL DEFAULT nextval('test52_serial_id_serial_seq'::regclass), info varchar NULL, cnt int4 NULL, CONSTRAINT test52_serial_pk PRIMARY KEY (id)); -- 等效

select * from test52_serial;
insert into test52_serial(info,cnt) values('aa',11), ('bb',22); -- 不显示插入自增值由系统自动维护
select * from test52_serial;
select * from pg_class where relkind='S' and relname like '%test52_serial%'; -- list all sequence like test
insert into test52_serial(id,info,cnt) values(3,'cc',33); -- 显示插入自增值
insert into test52_serial(info,cnt) values('dd',44); -- 再插入会报错，自增值已存在的冲突报错
select setval('test52_serial_id_seq', (select coalesce(max(id),1) from test52_serial)::BIGINT); -- 重置最大值，set sequenct to max(id), if null reset to 1
insert into test52_serial(info,cnt) values('dd',44); -- 再重新隐士插入，能够成功

delete from test52_serial where id=4;
update test52_serial set info='ssss' where id=3;
select * from test52_serial;

update test52_serial set id=100,info='ssssnnnn' where id=3;

delete from test52_serial;
select * from pg_class where relkind='S' and relname like '%test52_serial%'; -- list all sequence like test
alter sequence test52_serial_id_seq restart with 1; -- 重置 reset sequence after truncate or delete all;

identity

identity 本质是为了兼容标准sql中的语法而新加的，修复了一些 serial 的缺陷（例如：无法通过 alter table 方式实现增加/删除 serial 字段）。

identity 定义成 By Default 时，例如：id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY; 则也允许显式插入；

identity 定义成 Always 时，例如：id int8 NOT NULL GENERATED ALWAYS AS IDENTITY; 则申明用户不能显式赋值插入，否则会报错。但如果再加上 overriding system value，例如：insert into t1 (id) overriding system value values (1); 也可以实现显式不插入。

因此，identity 是 serial 的增强版，更适合作为自增列使用。

sequence，serial，identity 三者的共同的缺点是：在显式插入之后，无法将自增值更新为表中的最大id。

原文链接：https://blog.csdn.net/sunny05296/article/details/108466453