pgsql使用记录
1. 问题:使用in关键字的时候会报错(类型不对):
解决:转换类型
cast(需要的字段 as 类型
)或者 需要的字段 :: 类型
eg:
SELECT
*
FROM
dev_category d
WHERE
d.name LIKE '%票%'
AND d.id ::VARCHAR NOT IN (SELECT distinct c.pid FROM dev_category c WHERE c.pid IS NOT NULL and c.pid != '')
eg: SELECT Avg( cast(em.temperature as numeric)) AS temperature ...
这里是将字符串类型的 temperature 转为数值类型进行计算,否则报错。
2. pgsql设置主键自增:
删除序列:
DROP SEQUENCE rec_manufacturer_seq;
-- 1 创建序列
create SEQUENCE dev_category_id_seq;
-- 2 将序列的起始值设置为 1
ALTER SEQUENCE dev_category_id_seq START WITH 7;
-- 3 将序列的递增步长设置为 1
ALTER SEQUENCE dev_category_id_seq INCREMENT BY 1;
-- 4 将序列与表的列进行关联
ALTER TABLE dev_device ALTER COLUMN id SET DEFAULT nextval('dev_category_id_seq');
3. 讲一个表的结果作为表的值插入:
INSERT INTO rec_manufacturer ( s_name )
SELECT C.s_name FROM rec_manufacturer_copy1 C