PostgreSQL 常用语句

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

--数据库中单个表的大小（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));

--查出所有表（包含索引）并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20

--查出表大小按大小排序并分离data与index
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


-- 找到符合要求的表2数据，根据name列的值，找到符合要求的表1数据，并更新表1中content列的值
update tb1 set content="呵呵" where tb1.name in (select tb2.name from tb2.alias like '%站点%')


-- 关联表，然后赋值
update tb2 set tb2.fid = t.id from (select tb1.fid id from tb2
     left join tb1 on tb2.name = tb1.name) t;


-- 提取列中一部分内容
update flevel4 set tmp_name = substr(log_name, 0, strpos(log_name, '区')+1) where log_name like '%区%';


-- 添加外键约束
alter table datalist
	add constraint datalist_level3_id_fk
		foreign key (level3_id) references level3 (id);


-- 修改数据库编码
update pg_database set encoding = pg_char_to_encoding('GBK') where datname = 'nyc';


-- 查看数据库编码
select * from pg_database where datname='nyc';


-- 纵向合并2个表
select * from table1 union all select * from table2


-- 删除表
drop table 表名;


-- 更新值
update 表名 set "Name" = "PQMC" where fid > 0; -- 列名Name的值等于列名PQMC的值
update 表名 set "Name" = 'PQMC' where fid > 0; -- 列名Name的值为'PQMC'


-- 获取表的所有列名
select column_name from information_schema.columns where table_name=表名;


-- 删除列
alter table 表名 drop column 列名;


-- 修改表名
alter table 表名 rename to 新表名;


-- 修改列类型，并设置默认值
alter table 表名 alter column "DJMC" set data type varchar using '9999'; 


-- 添加列
alter table "CQ" add column "DJMC" varchar;


-- 修改列名
alter table "CQ" rename "sjmc" to "SJMC"; 


-- 获取连接服务下的所有表名（表名开头不包含pg_和sql_）
select tablename from pg_tables
    where tablename not like 'pg_%' and tablename not like 'sql_%'


-- 获取连接服务的所有数据库名
select datname from pg_database;


-- 表连接
select s.id, s.name, c.title from student as s 
     left join class as c on s.class_id = c.id; --通过student表的class_id列和class表的id列进行两个表连接


-- 为表添加外键(外键关联的一定是另一个表的主键)
alter table student      -- 添加外键的表名
    add constraint fk_student_lesson -- 外键的名称
        foreign key(lesson_id) references lesson(id); --student表的lesson_id列的外键为lesson表的主键列id


-- 创建带外键的表
create table emp(
      id int primary key,
      name varchar(30),
      salary nemueric(9,2),
      dept_id int,
      constraint fk_emp_dept foreign key(dep_id) references dept(id) --emp表的dept_id列的外键为dept表的主键列id
);


-- 为表添加主键
alter table student      -- 添加主键的表名
    add constraint pk_student_id -- 主键的名称
        primary key(id);        -- 主键的列名


-- 创建带有主键的表
create table emp(
      id int primary key, -- 列级约束主键，不能指定主键名称
      name varchar(30),
      salary numeric(9,2)
);
create table emp(
      id int,
      name varchar(30),
      salary numeric(9,2),
      constraint pk_emp primary key(id) -- 表级约束的主键，可以指定主键的名称
);


-- 创建序列
create sequence teacher_id_seq start with 1; --从1开始的序列


-- 为表的列设置自增序列
alter table teacher alter column id set DEFAULT nextval('teacher_id_seq'::regclass); --teacher表的id列为自增序列


-- 更新自增序列，指定起始值
select setval('datalist_id_seq', 7); --起始值为7
select setval('teacher_id_seq', max(id)) from teacher; --起始值从当前最大值开始


-- 查询自增序列
select pg_get_serial_sequence('表名','列名');
select pg_get_serial_sequence('dt_teacher', 'id');


--修改seq的名字
alter sequence 序列名 rename to 新的序列名;
alter table dt313
    alter column 列名[与序列名绑定的] set default nextval('public.新的序列名'::regclass);


--删除主键的seq
alter table 表名 alter column 列名 drop default;
drop sequence 序列名;


--添加主键的seq
create sequence 序列名;

alter table 表名
    alter column 列名 set default nextval('public.序列名'::regclass);

alter sequence 序列名 owned by 表名.列名;

select setval('序列名', max(列名)) from 表名; --设置seq序号值从 绑定列 的最大值开始