postgresql 相关

1.查询相关

1.按天分组

将timestamp转为date类型，再group by

• 表结构

created_at         | timestamp with time zone |           |          | now()

• 需求

created_at字段按天分组

将table_name换成自己的表名

with tmp as(select created_at::DATE from table_name) select tmp.created_at,count(*) from tmp group by created;

2.导入导出

有时候查询出来的条件较为复杂借助excel查看分析更为直观，将查询结果导出文件成为必要需求。

copy (select * from table_name) to '/tmp/xxx.csv' with csv header; --导出文件为csv格式需要加上 with csv header 

此命令可能会报错

must be superuser to COPY to or from a file

改为即可

copy (select * from table_name) to '/tmp/xxx.csv' with csv header; --导出文件为csv格式需要加上 with csv header 

踩坑

1.表迁移自增主键未同步

在做数据库迁移的时候，采用\copy 将表导出后再导入，发现再新增数据的时候主键冲突了。主键采用的时候自增模式。

create table table_name(id serial;primary key);

• 解决方案

1. 查看原表id最大值(主键自增到哪了)

select max(id) from table_name;

2. 将导出的新表手动更新主键id到此值

 id                 | integer                  |           | not null | nextval('invite_new_record_id_seq'::regclass)
setval('invite_new_record_id_seq', 最大值+1)