PostgreSQL常用命令&常见错误

sunny05296

已于 2024-07-15 20:32:49 修改

阅读量1.8w

点赞数 3

分类专栏：数据库文章标签： postgresql 数据库

于 2020-09-08 12:59:25 首次发布

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

版权

数据库专栏收录该内容

137 篇文章 5 订阅

订阅专栏

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

官方说明：https://www.postgresql.org/docs/12/sql-createschema.html

语法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:
user_name
| CURRENT_USER
| SESSION_USER

创建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;

创建时也可以直接指定授权给指定用户：
drop schema public02;
create schema public02 authorization test;

查看schema owner：
testdb01=# \dn;
或者
testdb01=# select * from information_schema.schemata;
testdb01=# select * from information_schema.schemata where schema_name='public02';

testdb01=# select exists(select 1 from information_schema.schemata where schema_name='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。

数据库oid和表oid查询：

\dS[+] 查看table, view, sequence列表（包括系统的和用户的）

查询database的oid（从 pg_catalog.pg_database 中查询）：
pg_catalog.pg_database 系统表中记录了数据库的相关详细信息包括oid
\dS+ pg_database

postgres=> \c - test
postgres=> \c testdb01
testdb01=> select oid,datname,datdba,encoding,datcollate,datistemplate,dattablespace from pg_catalog.pg_database;
oid | datname | datdba | encoding | datcollate | datistemplate | dattablespace
-------+-----------+--------+----------+-------------+---------------+---------------
14187 | postgres | 10 | 6 | en_US.UTF-8 | f | 1663
1 | template1 | 10 | 6 | en_US.UTF-8 | t | 1663
14186 | template0 | 10 | 6 | en_US.UTF-8 | t | 1663
16437 | testdb01 | 16436 | 6 | en_US.UTF-8 | f | 1663
27237 | testdb02 | 16436 | 6 | en_US.UTF-8 | f | 1663

database的oid对应了$PGDATA/base目录下的相应的目录：
-bash-4.2$ cd $PGDATA/base
-bash-4.2$ ls -l
total 76
drwx------. 2 postgres postgres 8192 Dec 6 2021 1
drwx------. 2 postgres postgres 8192 Dec 6 2021 14186
drwx------. 2 postgres postgres 8192 Feb 16 19:51 14187
drwx------. 2 postgres postgres 12288 Feb 17 12:33 16437
drwx------. 2 postgres postgres 8192 Feb 17 13:16 27237

testdb01(oid=16437)目录下存储了所有表对应的数据文件：
-bash-4.2$ ls -l $PGDATA/base/16437/ |more
total 2173548
-rw-------. 1 postgres postgres 16384 Feb 6 09:25 112
-rw-------. 1 postgres postgres 8192 Mar 16 2022 113
-rw-------. 1 postgres postgres 114688 Feb 17 13:18 1247
-rw-------. 1 postgres postgres 24576 Feb 6 09:25 1247_fsm
-rw-------. 1 postgres postgres 8192 Mar 16 2022 1247_vm
-rw-------. 1 postgres postgres 671744 Feb 17 12:35 1249
-rw-------. 1 postgres postgres 24576 Nov 18 2022 1249_fsm
-rw-------. 1 postgres postgres 8192 Nov 18 2022 1249_vm
-rw-------. 1 postgres postgres 1515520 Feb 17 09:15 1255
-rw-------. 1 postgres postgres 24576 Feb 6 09:25 1255_fsm
......
-rw-------. 1 postgres postgres 8192 Feb 16 19:44 27217
-rw-------. 1 postgres postgres 8192 Apr 3 10:10 27218
-rw-------. 1 postgres postgres 16384 Apr 3 10:10 27221
-rw-------. 1 postgres postgres 8192 Feb 17 12:36 27223
-rw-------. 1 postgres postgres 16384 Feb 17 12:36 27226
-rw-------. 1 postgres postgres 8192 Feb 17 12:40 27232
-rw-------. 1 postgres postgres 16384 Feb 17 12:40 27235
......
其中：27223 对应的是表 test02(relfilenode=oid=27223)

其中 rolowner 是表的 owner （rolename）的oid

查询当前数据库下的所有的表列表
testdb01=> \z
或
testdb01=> \dt[S+]
或
testdb01=> select tablename, schemaname, tableowner,tablespace from pg_catalog.pg_tables;
查询当前数据库下的用户表（不包含系统表）：
testdb01=> select tablename, schemaname, tableowner,tablespace from pg_catalog.pg_tables where schemaname='public';
查询当前数据库下的指定表：
testdb01=> select tablename, schemaname, tableowner,tablespace from pg_catalog.pg_tables where schemaname='public' and tablename='test02';
tablename | schemaname | tableowner | tablespace
-----------+------------+------------+------------
test02 | public | test |
(1 row)

PostgreSQL/Greenplum 表复制和临时表合并到最终目的表

1，表复制（基于已有的表创建相同结构的新表）

建表语法官方详细介绍文档连接参考：
https://docs-cn.greenplum.org/v6/ref_guide/sql_commands/CREATE_TABLE.html

创建测试表：
drop table if exists public.test83;
create table public.test83 (
   id int not null,
   info varchar(255) null,
   cnt int null,
   uptime timestamp(6) not null default current_timestamp(6),
   constraint test83_pk primary key (id)
)
-- distributed by (id)
;

复制方式的建表语法:
create table public.test83_tmp1 (like public.test83 {including | excluding} {defaults | constraints | indexes | storage | comments | all})

复制方式的建表示例（基于 test83 创建新表 test83_tmp1）：
create table public.test83_tmp1 (like public.test83);
或者
create table public.test83_tmp1 (like public.test83 including all);

注意：如果不加 including all 选项的话，创建的新表不会包含主键和索引等信息的复制创建，加了该选项就会复制主键和索引等相关信息。

基于已有的表复制数据，执行命令：
insert into public.test83_tmp1 select * from public.test83;

2，Greenplum 临时表合并到最终目的表

-- 创建测试表：
drop table if exists public.test84;
create table public.test84 (
   id int not null,
   info varchar(255) null,
   cnt int null,
   uptime timestamp(6) not null default current_timestamp(6),
   constraint test84_pk primary key (id)
)
-- distributed by (id)
;

-- 生成测试数据
insert into public.test84 (id,info,cnt,uptime) values
(1,'a',2021,'2021-01-01 13:01:01.123456'),
(2,'b',2021,'2021-12-31 15:02:02.222333'),
(3,'c',2022,'2022-01-01 13:01:01.123456');

select * from public.test84 order by uptime desc;

-- 基于源表创建目的表（表复制）
drop table if exists public.test84_dest;
create table public.test84_dest (like public.test84 including all);

-- 基于源表拷贝数据到目的表（数据复制）
insert into public.test84_dest select * from public.test84;

select * from public.test84_dest order by uptime desc;

-- 修改源表数据（update + insert）
delete from public.test84 where id<=2;
update public.test84 set info='c-02', uptime='2022-01-02 15:05:20.333444' where id=3;
insert into public.test84 (id,info,cnt,uptime) values
(4,'d',2023,'2023-01-01 13:01:01.123456'),
(5,'e',2024, '2024-01-01 13:01:01.123456');
select * from public.test84 order by uptime desc;

-- 临时表合并到目的表（但忽略 delete，即 delete 不同步、仅同步 insert + update）
begin;

-- 基于目的表创建生成临时表（仅复制表结构、不含数据）
drop table if exists public.test84_tmp1;
create table public.test84_tmp1 (like public.test84_dest including all);

-- 源表数据拷贝到临时表
insert into public.test84_tmp1 select * from public.test84;
select * from public.test84_tmp1 order by uptime desc;

-- 临时表合并方案1

-- 临时表数据合并到目的表 merge-step1: delete-from-dest for insert & update records
delete from public.test84_dest where id in (select id from public.test84_tmp1);
select * from public.test84_dest order by uptime desc;

-- 临时表数据合并到目的表 merge-step2: insert-into-dest for insert & update records
insert into public.test84_dest select * from public.test84_tmp1;
select * from public.test84_dest order by uptime desc;

end;

注意：
1）其中的临时表合并方案也可以根据实际情况采用表重命名（rename）方案，SQL如下：
-- 临时表合并方案2（rename）：
drop table if exists test84_old;
alter table public.test84 rename to test84_old;
alter table public.test84_tmp1 rename to test84;

2）用 begin/end 保证提交的SQL在同一个事务中；