PostgreSQL笔记

修改字段名

ALTER TABLE tableName RENAME oldColumnName TO newColumnName

修改字段

ALTER TABLE tableName ALTER COLUMN columnName TYPE CHARACTER varying(255) NOT NULL;

新增字段

ALTER TABLE tableName ADD columnName INTEGER DEFAULT 90 NOT NULL;
COMMENT ON COLUMN tableName.columnName IS '备注信息';

查找字符串

SELECT POSITION(concat(',' , '3124' , ',') IN concat(',' , '12,312,3124' , ','));
或
SELECT position (',' || '2' || ',' IN (',' || ('12','23','52') || ','))

tab的使用

情景：批量新增

WITH tab (name, age) AS (
    VALUES
    <foreach collection = "list" item = "item" index = "index" separator = ",">
        (#{name}, #{age})
    </foreach>
)

INSERTR INTO tableName (id,name,age)
SELECT NEXTVAL('seqName'), tab.name, tab.age
FROM tab
WHERE NOT EXISTS (
    SELECT 1 FROM tableName WHERE isvalid = 1 AND xxx = xxx...
)

灾难性SQL【update关于多个表】

UPDATE user
SET username = u.name
FROM user u
WHERE id = u.id

count(1) OVER(PARTITION BY columnName) AS xxx

排序，NULL值计算
ORDER BY columnName DESC NULLS [LAST|FIRST]