PostgreSQL常用操作命令

ゝ一纸荒年。 2022-12-05 00:42 217阅读 0赞

PostgreSQL常用操作命令

查看版本信息  
show server\_version;  
psql --version  
su - postgres  
$psql -version  
SELECT version();  
SHOW server\_version;  
SHOW server\_version\_num;

创建数据库新用户  
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 schema 授权）  
\\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;

查看表的数据  
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;

查看表结构  
\\d test01  
\\d geom\_test01

查看表的数据  
select \* from test01;

显示所有schema  
\\dn

显示所有用户  
\\du

显示表、视图的权限分配详情  
\\dp

显示当前默认模式  
show search\_path

更改默认模式  
set search\_path to myschema

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

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);  
执行成功