pgsql语句一些常见用法【增删除改】【DateFrame数据导入数据库】

1 增

​​http://t.zoukankan.com/star521-p-13473616.html​​

1. 将查询结果插入数据库

insert into table_name select * from table_name;

1. ​​为​​数据库表的字段添加描述

comment on column sys_user.user_name is '用户名';

1. python中将DataFrame数据导入pgsql 

import psycopg2
from sqlalchemy import create_engine
import sqlalchemy
account = pd.read_csv('../input/account.csv')
dtype_dict = { "together_type"        : sqlalchemy.types.VARCHAR
                , "number"   : sqlalchemy.types.INT
                , "opendate"   :  sqlalchemy.types.DATE
}

merge_data = pd.read_csv('../output/dim_calendar.csv',encoding='utf-8',quoting=1,dtype=str)

conn =create_engine(
    'postgresql+psycopg2://'+'user'+':'+'password'+'@'+'host'+':****'+'/'+'dbname')

pd.io.sql.to_sql(merge_data
                 ,'dim_test'
                 , conn,index= False
                 , schema='dim'
                 ,if_exists='append' # append：添加，replace：代替
                 , dtype=dtype_dict)
conn.dispose()

​​python3+pandas+dataframe导出导入postgresql数据迁移+保证字段数据类型正确_mb607022e25a607的技术博客_51CTO博客​​

2 删

删除主键

ALTER TABLE dest DROP CONSTRAINT dest_pkey;

原文链接：https://blog.csdn.net/qq_36646146/article/details/82144447

删除重复数据

delete from ods.ods_sensor_sensorsdata_event2
       where ctid not in (select min(ctid)
       from ods.ods_sensor_sensorsdata_event2 group by id);

参考链接：​​PostgreSQL删除重复数据 - David_Tang - 博客园​​

3 改

1. 更改分布键

ALTER TABLE dest set distributed by(columname);

PS：若有主键，先删除主键关联，再更改

查询主键名，一般主键名都是表名 + _pkey 如表dest 主键名就是 dest_pkey

SELECT  a.conname FROM  pg_constraint as a, pg_class as b

WHERE a.conrelid = b.oid AND a.contype = 'p'  AND b.relname = 'dest';

1. 修改某一个值的内容

UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

4 时间函数

自然周	DATE	新增	natrual_week	DATE_TRUNC('WEEK',[日期])
月份	DATE	新增	natrual_month	DATE_TRUNC('MONTH',[日期])
季度	DATE	新增	quarter	DATE_TRUNC('QUARTER',[日期])
年份	DATE	新增	natrual_year	DATE_TRUNC('YEAR',[日期])
半年度	DATE	新增	semi_annual	TO_DATE(CONCAT([日历年_数值],IF([日历月_数值]<=6,"-01","-07"),"-01"))

5 性能查询

1. 查看greenplum库各个节点数据的分布情况

select gp_segment_id,count(*)
  from public.tmp_sales_compared_yearly_2
 group by gp_segment_id;

1. ​​PostgreSQL EXPLAIN ANALYZE使用和检查SQL性能问题_helenbi的博客-CSDN博客_analyze explain​​

6 其他函数

coalesce

遇到非空的举返回

date(now())-coalesce（最后销售日期，最早门店入库日期）-1

json排序

问题：

解决方案：

解决后的效果

Any like

WHERE name like any (array['%刘%','%张%','%李%','%罗%','%程%','%武%','%哈%'])