1.如何新建表字段为自增。
serial 表示自增
如上代码为新建表的id为自增列。
CREATE TABLE patac.p_department
(
id serial PRIMARY KEY,
code bigint,
name text
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE patac.p_department
OWNER to "a6966fe1-2858-4b56-8ea9-abde4effba7a";
COMMENT ON TABLE patac.p_department
IS '人员分组信息表。';
2.操作时间的函数:
获取当前时间的语句:
now()::timestamp(0)without time zone + '8 hour'
3.新增字段,且设置默认值
ALTER TABLE patac.device_info
ADD COLUMN d_active bigint default 1;
4.字符串转为时间
select to_timestamp("startTime",'yyyy-MM-dd hh24:mi:ss') from patac.device_alarm where status=1
and to_timestamp("startTime",'yyyy-MM-dd hh24:mi:ss') > to_timestamp('2018-09-03 10:00:00','yyyy-MM-dd hh24:mi:ss')
5.将查询结果转换为一行:
select name,string_agg(tag,',') from book2 group by name
6.使用Navicat Premium查询postgresql中的数据:
db.common_Modbus_Handler.find({"agentId":"00000001-0000-0000-0000-C400AD011582","n":"Connection"})
7.时间操作
extract(year from resolutiondate) 获取timestamp中的年份
extract(month from resolutiondate) 获取timestamp中的年份
8.将字符串转为表格形式
select unnest(string_to_array(ta,',')) as that_date from (SELECT '2019-02-12,2019-02-13,2019-02-14,2019-02-15,2019-02-16,2019-02-17,2019-02-18,2019-02-19,2019-02-20,2019-02-21,2019-02-22' as ta) as a
9.用一个表的数据更新另外一个表的数据