postgreSQL 数据库使用

1.数据库建表的时候，字段尽量小写，避免查询时查不到

下面是一些sql 的使用方法

1.使用 limit ,后面直接写要查询的条数

	select * from xxxx as a  limit 100

2.不要使用单引号，可以使用双引号或者什么都不用

	plsql   	select * from		"xxx"
	mysql       select * from `xxxx`

3.主键是int 的情况下不能设置自增，所以大多数使用uuid

	SELECT max(seq_id)+1 from xxxx

4.plsql 函数，写存储过程使用事务
INOUT 输出参数
IN 输入参数

	CREATE OR REPLACE PROCEDURE "public"."pro_xxcl_del"(IN "sysid" varchar, INOUT "rtn" int2, IN "type" varchar, IN "daler_name" varchar, IN "deler_qx" varchar)
 AS $BODY$BEGIN
    -- 开始一个事务
	START TRANSACTION;
     SET rtn = 0;
	-- 执行一些SQL操作，例如插入数据
	 IF (type = '生产生活服务设施') THEN
	INSERT INTO xxx (
	SYS_ID,DELER_NAME, DELER_QX) SELECT 
	SYS_ID,  daler_name deler_name, deler_qx deler_qx
	FROM xxx WHERE sys_id = sysid;
ELSE
-- 添加记录到 删除记录表
	INSERT INTO xxx (
	SYS_ID, DELER_NAME, DELER_QX) SELECT 
	SYS_ID,  deler_name deler_name, deler_qx deler_qx
	FROM xxx g WHERE sys_id = sysid;
END IF;


DELETE FROM xxx WHERE sys_id = sysid;
DELETE FROM xxx WHERE sys_id = sysid;
COMMIT;
SET rtn = 1;

-- 如果有错误，捕获异常并回滚
EXCEPTION WHEN others THEN
    ROLLBACK;

END$BODY$
  LANGUAGE plpgsql 

5.列转行，数值之间用逗号分割

SELECT sys_id,string_agg(sc_gg_szlc::varchar,',') sc_gg_szlc,string_agg(sc_gg_lcjg::varchar,',') sc_gg_lcjg from  xxxx GROUP BY sys_id

6.查询时时间类型转换

SELECT TO_CHAR(cc.sc_gg_gd_bg_bcrq, 'YYYY-MM-DD') bcrq  from  xxx  cc