Postgresql 常用SQL

发表于 | 分类于 | 浏览量:

DDL

  1. 修改表名

    1
    ALTER TABLE table1 RENAME TO table2;

  2. 新增字段

    1
    ALTER TABLE table1 ADD COLUMN f_uid VARCHAR;

  3. 删除字段

    1
    ALTER TABLE table1 DROP COLUMN f_uid;

DML

  1. 关联更新

    1
    2
    3
    4
    5
    UPDATE table1
    SET content_id = b.f_id
    FROM table1 a
      INNER JOIN table2 b ON a.content_id = b.f_content_id
    WHERE table1.id = a.id;

  2. 更新Jsonb字段

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT '{"diffuse": [0.5, 0.5, 0.5, 1], "opacity": 1, "emissive": [0, 0, 0, 1]}'::json AS f_phong;
    SELECT * FROM table_name WHERE f_phong ->> 'diffuse' = '[0.5, 0.5, 0.5, 1]';

    -- PostgreSQL 9.5
    UPDATE table_name SET f_phong = (jsonb_set(to_jsonb(f_phong), '{diffuse}', '[0, 0, 0, 1]', false))::json WHERE f_phong->>'diffuse' = '[0.5, 0.5, 0.5, 1]';

    -- PostgreSQL 9.5 && PostgreSQL 9.4
    UPDATE table_name
    SET f_phong = (replace(f_phong :: TEXT, '"diffuse": [0.5, 0.5, 0.5, 1]', '"diffuse": [0, 0, 0, 1]')) :: JSON
    WHERE f_phong ->> 'diffuse' = '[0.5, 0.5, 0.5, 1]';

DQL

待更新

DCL

待更新