postgreSQL常用sql

查看表字段注释

Select a.attnum,(select description from pg_catalog.pg_description where objoid=a.attrelid and objsubid=a.attnum) as descript,a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod) as data_type from pg_catalog.pg_attribute a where 1=1 and a.attrelid=(select oid from pg_class where relname='tbl_pd_collect_position' ) and a.attnum>0 and not a.attisdropped order by a.attnum;

real_name是表名

Select a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod) as data_type from pg_catalog.pg_attribute a where 1=1 and a.attrelid=(select oid from pg_class where relname='tbl_collect_position' ) and a.attnum>0 and not a.attisdropped order by a.attnum;

查询内容copy到csv文件

psql -h pgm-bp1buafds03kl3fj14850.pg.rds.aliyuncs.com -p 3433 -d jpoimp_poi -U postgres -c "copy(select line,station from tbl_subway_collect_raw where city='北京市') to stdout with csv header" > /jdata/station_line_mapping.csv

日期格式化

同MySQL中的date_format(time,"%Y%m%d")

select to_char(create_time,'yyyy-MM-dd hh:MM:ss') from xxx;