自用SQL 相关POSTGRESQL1.插入数据如果id已经存在做更新操作2.order是一个需要增加的数可以插入查到数

POSTGRESQL

CREATE TABLE "table" (
    id bigserial not null unique,
   "order" int ,
    PRIMARY KEY (id)
);
comment on table "table" is 'テーブル';　//给表注释
comment on column "table".id is 'id'; //字段注释

1.插入数据如果id已经存在做更新操作

  insert into "user" (user_id,create_time)
    values (#{userId}, now()) 
    ON CONFLICT (user_id)
    DO UPDATE SET 
    update_time = now()

2.order是一个需要增加的数可以插入查到数据库中order最大值加1

  insert into "table" 
      (id,order) select 
      #{id},
     COALESCE(max("order"+1),1 ) from table ;

3.查询所有表的注释名

  select pg_catalog.obj_description(oid) 
      from pg_catalog.pg_class c 
      where c.relname=#{table} //table是表名

4.查询表里所有字段和字段的注释

   SELECT col_description(a.attrelid,a.attnum) as comment,
          a.attname as "column"
          FROM pg_class as c,pg_attribute as a where c.relname = #{table}
          and a.attrelid = c.oid and a.attnum>0 //comment是字段的注释 column是字段

5.修改表中其中一个字段为可以为空

	alter table "table" alter "order" drop not null;