postgresql

postgresql中判断表是否存在
select count(1) from pg_tables where upper(schemaname) =upper('pxx') and upper(tablename) = 'xx';
或者
SELECT COUNT(1) FROM PG_CLASS WHERE upper(RELNAME) = upper(#{tableName})

postgresql中使用字符串拼接
如果不支持 CONCAT('%', #{id,jdbcType=VARCHAR}, '%') 可以换成like '%'||#{id}||'%'
concat_ws

判断列是否存在
select count(1) from information_schema.columns where upper(table_name) = upper(#{tablename}) and upper(column_name) = upper(#{columnname})

查询列的长度
SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE upper(TABLE_NAME) = upper('t_g_t') AND upper(COLUMN_NAME) = upper('id');

创建sequence
CREATE SEQUENCE PXX.XXX
INCREMENT 1
MINVALUE 6000
MAXVALUE 999999
START 6000
CACHE 1;

判断sequence是否存在
select count(1) from PG_CLASS WHERE upper(relname)= upper('xxx');
或者加上前面数据库名字的判断：
select count(1) from pg_class pc, pg_namespace pns where pc.relnamespace=pns.oid and pns.nspname= LOWER('PXX') and pc.relname = LOWER(#{sequenceName});

日期格式转换
SELECT TO_CHAR(TO_TIMESTAMP(CREATETIME, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMM') FROM PXX.xxx WHERE ID = #{Id}
CREATE TABLE PXX.T_EVENT_${month} (LIKE PXX.T_EVENT_TEMPL INCLUDING INDEXES);
如果CREATETIME包含毫秒，这里会报错，可以截取
(TO_CHAR(TO_TIMESTAMP(substring(T.CREATETIME,1,19), 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMM'))

判断语句
where extendParameter is not null

删除SEQUENCE
drop SEQUENCE PXX.TXX_SEQ

修改列
ALTER TABLEPXX.TXX ADD COLUMN xxx VARCHAR(64), ADD COLUMN xx2 INTEGER DEFAULT 0;

查询列是否存在
tableName： "T_XX"
columnName："xx2"
SELECT COUNT(1) AS ct1 FROM INFORMATION_SCHEMA.COLUMNS WHERE upper(TABLE_NAME) = upper(#{tableName})
AND upper(COLUMN_NAME) = upper(#{columnName})

查询列长度
SELECT CHARACTER_MAXIMUM_LENGTH AS ct1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE upper(TABLE_NAME) = upper(#{tableName}) AND upper(COLUMN_NAME) = upper(#{columnName})

修改列属性
ALTER TABLE PXX.TXX alter COLUMN name type VARCHAR(768), alter COLUMN u type VARCHAR(768),alter column user type VARCHAR(768);
ALTER TABLE PXX.TXX alter COLUMN NAME type VARCHAR(768), alter COLUMN USER type VARCHAR(768),alter column DETAIL_CN type VARCHAR(3072);

新增列
alter table pms.t_g_t add column extendparameter text;
offset和limit
select * from PXX.TXX limit 2 offset 2;
假如是 1 2 3 4 5 那么这段取出来的就是，偏移掉2个之后的3 4 5 在limit 2 个。就是 3 4

批量插入
insert into pxx.tt(id) values('18'),('19');
代码中可以用foeach：
insert into PXX.tt (id.name)
VALUES
<foreach collection="list" index="index" item="item" separator=",">
        (
                #{item.id},
                #{item.name},
        )
</foreach>

修改列名字
ALTER TABLE PXX.TXX RENAME COLUMN ID TO IDDD;

带判断条件的语句
DO
$do$
BEGIN
        IF (SELECT COUNT(*) AS ct1 FROM INFORMATION_SCHEMA.COLUMNS WHERE         upper(TABLE_NAME) = 'T_T_T' AND upper(COLUMN_NAME) = 'ID' ) = 1
        THEN
        ALTER TABLE PMS.T_T_T RENAME COLUMN ID TO TID;
        END IF;
        END;
$do$;

创建索引
<update id="createEventTemplateTable">
        CREATE TABLE PXX.T_EVENT_TEMPL(
        ID VARCHAR(64) NOT NULL,
        NAME VARCHAR(256),
        UNAME VARCHAR(256),
        TIME VARCHAR(32),
        DETAIL VARCHAR(1024),
        DETAIL_CN VARCHAR(1024)
)
</update>
<update id="createIdxId">
        CREATE INDEX TEMPL_ID_IDX ON PXX.T_EVENT_TEMPL(ID);
</update>
<update id="createIdxTime">
        CREATE INDEX TEMPL_TIME_IDX ON PXX.T_EVENT_TEMPL(TIME);
</update>
<update id="createEventTable">
        CREATE TABLE PXX.T_EVENT_TEMPL_${month} (LIKE PXX.T_EVENT_TEMPL         INCLUDING INDEXES);
</update>