PG高斯数据库常见问题

参考官方文章：点击查看

列序不存在

关系“dict_jsfz_app_yysc_id_seq”不存在 也就是列序不存在

relation dict_jsfz_app_yysc_id_seq does not exit ;

CREATE SEQUENCE 列序名

授权权限

1、来授权sysadmin的所有权限。
2、将数据库中 public schema 的所有权限授予指定用户

GRANT ALL PRIVILEGES TO [username];
GRANT ALL ON SCHEMA public TO [username];

删除表所有数据

delete from 表名;

删除字段=xxx的数据

字符串加引号

delete from 表名 where 字段 = 'xxxx' ;

查询字段=xxx的数据

select * from 表名 where 字段 = xxx ;

创建具有管理权限的用户

CREATE USER user WITH PASSWORD 'Admin@123' CREATEDB;

创建普通权限的用户

CREATE USER user WITH PASSWORD 'Gauss@123';

创建用户

CREATE USER snail WITH PASSWORD 'your_strong_password';

创建数据库 snail_job

CREATE DATABASE snail_job;

授权用户 snail 对 public 模式的所有权限

在xx库下public模式下执行，执行用户对该库有所有权限

GRANT ALL PRIVILEGES ON SCHEMA public TO snail;

删除用户

DROP USER kdtc;

修改用户密码

ALTER USER kdtc WITH PASSWORD '新密码';

查看用户拥有的表等其他对象

SELECT * FROM pg_tables WHERE tableowner = 'kdtc';

授予meiya用户对v_phone视图的只读权限

GRANT SELECT ON TABLE public.v_phone TO meiya;

查询所有用户

SELECT * FROM pg_user;

查询无效索引

execute direct on all $$select pgxc_node_str(),d.relname as tablename,e.* from pg_class d,(select c.nspname as schema,b.relname as indexname,a.indrelid from pg_index a,pg_class b,pg_namespace c where a.indexrelid=b.oid and b.relnamespace=c.oid and (a.indisvalid&a.indisusable)=false) e where e.indrelid=d.oid$$;

查询库对磁盘的占用分布式

SELECT 
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size,
    pg_database_size(datname) AS size_bytes
FROM 
    pg_catalog.pg_database
ORDER BY 
    pg_database_size(datname) DESC
LIMIT 10;

查询库对磁盘的占用单机版

SELECT 
    d.datname AS database_name,
    pg_size_pretty(pg_database_size(d.datname)) AS size,
    pg_database_size(d.datname) AS size_bytes
FROM 
    pg_database d
ORDER BY 
    pg_database_size(d.datname) DESC
LIMIT 10;