postgresql常见的DDL和DML操作

常用DDL操作者

1.创建数据库，模式，表

-- 创建数据库,默认模板是template1
create database db_name  [owner to use_name];

-- [为某个用户]创建模式
create schema sm_name [authorization user_name];

-- 创建表
create table tb_name(col1_name [primary key],data_type,col2_name,data_type,col3_name,data_type,...);

2.修改表结构

-- 增加字段
alter table tb_name add colum col1_name data_type;

-- 增加约束
alter table tb_name  add check(expression);

-- 删除字段
alter table tb_name drop colum col1_name;

-- 删除约束
alter table tb_name drop constraint constraint_name;

-- 删除非空/默认约束条件
alter table tb_name alter column col_name drop null / default;

-- 修改默认值(不会影响现有的行，影响之后插入的数据行)
alter table tb_name alter column col_name set default data;

-- 修改字段类型
alter table tb_name alter column col_name type col_type;

-- 重命名字段名
alter table tb_name rename column col_name to new_col_name;

3.删除数据库，模式，表

-- 删除数据库(如果还有用户连接在这个数据库上，将无法删除该数据库)
drop database db_name;

-- 删除某模式，删除之前要将所有依赖清理掉，否则无法删除
drop schema schema_name;

-- 删除表
drop table  table_name;

-- 清空表数据
truncate table table_name;

4.索引相关的修改

-- 创建普通索引索引
create index index_name on tb_name(col);

-- 创建唯一索引
create unique index index_name on tb_name(col);

-- 创建表达式索引
create index index_name on tb_name(expression); 

-- 创建部分索引
create index index_name on tb_name(col) where col ....; 

-- 删除索引
drop index index_name;

常用DML操作

1.查询当前路径或当前用户

-- 查询当前搜索路径
show search_path;

-- 设置当前会话为指定模式的搜索路径
set search_path=test;
set search_path="$user",public,test;

-- 查看当前用户
select current_user;

-- 查看当前所有连接用户
select * from current_user;

2.查看数据库或表等大小

-- 查看数据库相关信息大小
select pg_database_size(oid);
select pg_database_size(name);

-- 查看表索引大小
select pg_indexes_size(regclass); -- oid或者表名

-- 查看表的大小
select pg_table_size(regclass);

3.查看表结构

-- 查看某个表结构
SELECT a.attnum,
       a.attname AS field,
       t.typname AS type,
       a.attlen AS length,
       a.atttypmod AS lengthvar,
       a.attnotnull AS notnull,
       b.description AS comment
  FROM pg_class c,
       pg_attribute a
       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
       pg_type t
 WHERE c.relname = 'table_name'  -- 表名
       and a.attnum > 0
       and a.attrelid = c.oid
       and a.atttypid = t.oid
 ORDER BY a.attnum

4.其他查询

-- 控制查询,分页查询
select * from tb_name limit n;

-- 查询返回行，跳过m行
select * from tb_name limit n offset m;