1. table新增字段和注释
ALTER TABLE T_DEVICE_MONITORING_NETWORK ADD COLUMN area_temp char(50);
ALTER TABLE t_device_monitoring_network ADD COLUMN area_humidity char(50);
COMMENT ON COLUMN t_device_monitoring_network.area_temp IS '区域温度';
COMMENT ON COLUMN t_device_monitoring_network.area_humidity IS '区域湿度';
2. table 删除字段
ALTER TABLE t_device_monitoring_network DROP COLUMN area_temp;
3. 修改字段类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE varchar(50);
//从varchar 改为 int 时
ALTER TABLE table_name ALTER COLUMN column_name TYPE int4 USING column_name::integer ;
补充：char 和varchar 存取时的去别
存数据时的区别
char是固定长度，长度范围是0—255，存储时，如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中
varchar是可变长度，长度范围是0-65535，存储时，如果字符数没有达到定义的位数，不会再后面空格
取数据时的区别
char取数据时，会把后面的空格全部去掉
而varchar在取数据时，尾部空格会保留
4、删除NOT NULL约束
alter table m_order_detail_lottery alter COLUMN info_id drop not null;
5、为已有的字段添加默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;
6、删除默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
7、如果存在则更新，如果不存在则新增(在mysql中可以用replace into)
insert into sys_tag_info(tag_id,create_time) values(20,1517396043592)
on CONFLICT(tag_id,info_id) do update set create_time = 1517396043597
8、删除SEQUENCE(relation t_device_aisle_test_id_seq is already exists)
DROP SEQUENCE t_device_aisle_test_id_seq;
9、修改字段名称
ALTER TABLE table_name RENAME COLUMN column_name1 to column_name2;
10、char 和varchar的区别
char：定长，效率高，一般用于固定长度的表单提交数据存储 ；例如：身份证号，手机号，电话，密码等
varchar：不定长，效率偏低
char定长，如果长度不够，则从数据查出来后，会以空格补全，这点需要特别注意
11、查看表占用磁盘空间大小
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
WHERE table_name = 't_device_monitoring_network'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
12、查看数据库占用磁盘空间大小
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20
13、去重
- 查询数据库中重复记录，重复记录是根据单个字段（code ）来判断
SELECT * from t_device_error_code WHERE
code in ( SELECT code from t_device_error_code GROUP BY code HAVING count(code) > 1)
and ctid not in ( SELECT min(ctid) from t_device_error_code GROUP BY code HAVING COUNT(code) >1 )
ORDER by code
删除数据库中重复记录
delete from t_device_error_code WHERE
code in ( SELECT code from t_device_error_code GROUP BY code HAVING count(code) > 1)
and ctid not in ( SELECT min(ctid) from t_device_error_code GROUP BY code HAVING COUNT(code) >1 )
- 多个字段（factory_dev_no,position）
查询
SELECT * from t_device_param_config WHERE
id in
( SELECT id from t_device_param_config where type = 2)
and
ctid not in
( SELECT MAX(ctid) from t_device_param_config where type = 2 GROUP BY factory_dev_no,position ORDER BY factory_dev_no )
删除
DELETE from t_device_param_config WHERE
id in
( SELECT id from t_device_param_config where type = 2)
and
ctid not in
( SELECT MAX(ctid) from t_device_param_config where type = 2 GROUP BY factory_dev_no,position ORDER BY factory_dev_no )
14、时间戳转换
SELECT TO_TIMESTAMP(create_time/1000) as time,*
FROM t_push_message_device_history where status=1 and send_time=1
ORDER BY id DESC limit 1000
15、修改postgresql自增id的默认值
通常表中id是自增的，我们初始化数据时如果需要保持原有的id不变，那么我们的insert语句中就会有id值，初始化完成以后，再通过ORM框架保持数据时，可能会报id重复，这是因为insert插入时并没有改变默认的id值，所以再保存时id自增就会重复，
所以可以在初始化完成以后，重置id的默认值
alter SEQUENCE t_device_parts_template_id_seq RESTART with 20
