Postgres数据库基础操作

一、查看数据库相关信息

查看客户端版本       psql --version

查看服务器端版本    select version();

查看版本信息           show server_version;

查看数字版本信息包括小版号

SHOW server_version_num; 或 SELECT current_setting('server_version_num');

二、数据库操作

登录数据库   psql -h 192.***.***.** -d dbname -U username

1.数据库

\l      ------查看所有数据库

\c [database_name]   ----切换数据库

\c - [user_name]         ----切换用户

select pg_size_pretty(pg_database_size('dbname'));    --查看数据库大小

2.表

 \d      -----查看所有表

删除表数据

delete from table_name where day_id='20210302';    ----删除指定条件数据

truncate table table_name ;                                        ----清空表数据

修改表

alter table table_name rename to table_name_new;     -----更改表名

列操作

ALTER TABLE table_name DROP COLUMN nas_post_id_1;        -----删除列

alter table table_name add column_new 字段属性                        ------添加列

alter table table_name rename a to b;                                           ------更改字段名

添加索引

create index 索引名 on tab名（列名）       ------添加单索引

create index idx_table_name on table_name (day_id);

添加序列

create sequence table_name_seq columnby 1 minvalue 1 no maxvalue start with 1;

表及索引大小

(1).查看单表大小

SELECT pg_size_pretty(pg_table_size('table_name')) AS table_size,

       pg_size_pretty(pg_indexes_size('table_name')) AS indexes_size,

       pg_size_pretty(pg_total_relation_size('table_name')) AS total_size

(2).查询所有表大小

SELECT table_name,

       pg_size_pretty(table_size) AS table_size,

       pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size

FROM ( SELECT table_name,

              pg_table_size(table_name) AS table_size,

              pg_indexes_size(table_name) AS indexes_size,

              pg_total_relation_size(table_name) AS total_size

        FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name

               FROM information_schema.tables

             ) AS all_tables

        ORDER BY total_size DESC

) AS pretty_sizes;

三、 用户操作

\du      -----查看所有用户

四、数据操作

1.更新数据

UPDATE table_name SET test_id = 'aa' WHERE day_id= '20210302' ;    ----更新table_name 表中20210302 的 test_id 字段为 aa。

2.数据导出到本地

\o /home/postgres/test_20210302.txt

select * from table_name where day_id= '20210302';

\o

3.数据文件导入表

\COPY table_name FROM '/home/postgres/test.txt'   -----默认表分隔符为tab

五、用户、模式

1. 创建用户

CREATE USER user_name WITH PASSWORD '********';           -----新建用户

GRANT ALL PRIVILEGES ON DATABASE wire TO user_name ;    -----用户赋权

select * from INFORMATION_SCHEMA.role_table_grants；  -----查询用户权限

2.删除用户

DROP USER user_name ;

3. 创建模式

--新建模式

create schema sch_name;

--模式授权

grant all on schema public to sch_name;   

grant all on all tables in schema public to sch_name;

--删除用户某个模式权限

REVOKE all on all tables in schema public from sch_name;

4.模式设置

SHOW search_path;   -----当前模式查询

SET search_path TO “$user”, public, my_schema;   ----会话级别设置：

数据库级设置（跨会话）：

ALTER database smartsys SET search_path TO “$USER”, public, my_schema;

ALTER database netdep SET search_path TO “$USER”,public,my_schema;

##恢复： set search_path = "$user", public;

六、其他

 select * from pg_stat_activity;   -----查看连接数

 锁死

 SELECT * FROM pg_stat_activity WHERE datname='databasename' ;  ---- 查询是否有死锁pid,query,waiting ） waiting --t表示正在执行，f表示已经执行完成

七、 函数

 create function function_name (parameters_list)  ---创建函数

 drop function function_name (parameters_list);   ----删除函数

 \df    ---查看自建函数