pgsql使用命令

强制类型转换

cast(STATE as varchar)

查询表结构

SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = 'tb_b_ys_input_state'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;

创建备份表

CREATE TABLE par_service_sql_desc_iss_bak AS SELECT * FROM par_service_sql_desc_iss

添加表字段：ALTER TABLE 表名 ADD 字段名 字段类型(字段长度);

 示例：ALTER TABLE "user" ADD gid1ype numeric(50);

给字段添加注释：COMMENT ON COLUMN "表名"."字段名" IS '注释内容';

 示例：COMMENT ON COLUMN "user"."gid1ype" IS '类型 1为普通 2为高级';

修改表名：alter table table_name(表名) rename to new_table_name(新表名);

 示例：alter table "user" rename to "ts_user";

修改字段名称：ALTER TABLE "表名" alter COLUMN 字段名 type 字段类型(长度) ;

 示例：ALTER TABLE "ts_user" alter COLUMN gid1ype type VARCHAR(50) ;