PostgreSQL常用SQL

Tags:
Categories:

PostgreSQL常用SQL

元命令

查看元命令执行的具体SQL:

psql -E xxxx
命令 描述
\? 查看元命令的帮助
\l 列出所有数据库
\d 数据库中的所有表,视图,序列
\dt 数据库中的所有表
\d tb_name 查看表结构
\du 列出所有用户
\dn 列出库下所有schema
\! 执行shell命令
\c 查看当前数据库和用户
\c db_name 连接到指定数据库
\c database user_name 切换角色
\conninfo 显示连接信息
\timing on/off 显示/隐藏执行时长
\password user_name 修改用户密码
\encoding 查看字符集

SQL命令

4)查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

5)查看表大小
select pg_size_pretty(pg_relation_size(table_name));

6)按占空间大小，顺序查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

2）创建序列：
create sequence seq_user_camera_version increment by 1 minvalue 1 no maxvalue start with 1;

3）建表，并用上面的序列作为主键自增序列
CREATE TABLE public.user_camera_version (
    id int4 NOT NULL DEFAULT nextval('seq_user_camera_version'::regclass),
    user_id int4 NULL,
    user_type varchar(1) NULL,
    hardware_version varchar(100) NULL,
    software_version varchar(100) NULL,
    modify_date timestamp NULL,
    CONSTRAINT user_camera_version_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
) ;

## 查看参数文件
show config_file;
show hba_file;
show ident_file;

    ## 用户和权限相关
1）创建用户
create user u2 with login CREATEROLE CREATEDB password 'u2';
create user u2  password 'u2';

2）修改数据库owner
ALTER DATABASE name OWNER TO new_owner;


3）设置用户对某个数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE database_name to username;

4）授予用户CONNECT到数据库的权限
GRANT CONNECT ON DATABASE database_name TO username;


5） 授予public模式中所有表的所有权限给用户。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

6）授予public模式中所有序列的所有权限给用户:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;


7) 导出CSV
   psql --host=192.168.4.135  --port=55332 --user=airflow --dbname=wavepoint -c "\copy public.dim_ip_bussines_types_dict to 'dim_ip_bussines_types_dict-广东电信-20230327.csv' with csv header ENCODING 'UTF8' NULL AS 'null'"

8) 导入CSV
   psql --host=192.168.4.135 --port=55332 --user=airflow --dbname=wavepoint -P pager=off -v ON_ERROR_STOP=true -c "\copy public.dim_ip_bussines_types_dict(ip,province,city,district,business_type,address_type,purpose,source,type,status,create_time,lst_upt_time,idc_type,name,address_ownership) from 'dim_ip_bussines_types_dict-广东电信-20230327.csv'  with csv header ENCODING 'UTF8'  NULL AS 'null'"