postgresql 获取表的字段及创建表、添加、删除、编辑字段语句

1.新建表

eg:

CREATE TABLE "public"."yw_privsuser_wyhcpz" (
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "flbm" varchar(255) COLLATE "pg_catalog"."default",
  "zyqcode" varchar(255) COLLATE "pg_catalog"."default",
  "pid" varchar(255) COLLATE "pg_catalog"."default",
  "zyqname" varchar(255) COLLATE "pg_catalog"."default",
  "flbmzw" varchar(255) COLLATE "pg_catalog"."default",
  "cjsj" timestamp(6) DEFAULT now(),
  "bz" varchar(255) COLLATE "pg_catalog"."default",
  CONSTRAINT "yw_privsuser_wyhcpz_pkey" PRIMARY KEY ("id")
)
;

2.重命名表

alter table totablename_old rename to tablename_new;

3.复制表

select * into table1_back from table1 where 1<>1;

4.获取库中所有的表

查询所有新建到public下的表

SELECT
	pt.tablename,
	CAST ( obj_description ( pc.relfilenode, 'pg_class' ) AS VARCHAR ) AS TABLE_COMMENT 
FROM
	pg_tables pt
	LEFT JOIN pg_class pc ON pc.relname = pt.tablename 
WHERE
	pt.schemaname = 'public'

5.获取表字段

5.1 获取表字段

SELECT
 A.attname AS field_name,-- 字段名
 t.typname as typename,    --字段类型
 NULLIF(information_schema._pg_char_max_length(A.atttypid, A.atttypmod), -1) AS maxlen,  -- 字符串最大长度
 col_description ( A.attrelid, A.attnum ) AS COMMENT,   -- 字段备注
 format_type ( A.atttypid, A.atttypmod ) AS TYPE,
 A.attnotnull AS NOTNULL ,  -- 是否非空
 A.atthasdef ,  --是否存在默认值
 A.atttypmod
FROM
	pg_class AS C,
	pg_attribute AS A ,
	pg_type as T
WHERE
	C.relname = '表名' 
	AND A.attrelid = C.oid 
	AND A.atttypid= T.oid
	AND A.attnum > 0
	AND NOT A.attisdropped

运行结果

5.2 获取表主键

SELECT
    pg_constraint.conname AS pk_name,
    pg_attribute.attname AS colname,
    pg_type.typname AS typename
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = pg_constraint.conkey[1]  --   如果是联合主键 And pg_attribute.attnum in (pg_constraint.conkey[1],pg_constraint.conkey[2],pg_constraint.conkey[3])
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
    pg_class.relname = '表名'
AND pg_constraint.contype = 'p'

6.修改表字段

6.1 添加字段

	ALTER TABLE data_u11001000504 ADD COLUMN "filedtest1" INTEGER  default 1;

字段类型可以为：INTEGER、int4 int8、 varchar(11)、float

6.2 编辑字段

（1）修改字段备注

	comment on column data_u11001000567."字段名" is '6666'

（2）修改字段类型

	ALTER TABLE data_u11001000504 ALTER COLUMN "varch" TYPE int4 USING(varch::int4); 

（3）设置字段为非空

	ALTER TABLE data_u11001000504 ALTER  COLUMN "varch" set NOT NULL

（4）取消设置字段非空

	ALTER TABLE data_u11001000504 ALTER  COLUMN "varch" DROP NOT NULL;

（5）重命名字段

	ALTER TABLE data_u11001000504 RENAME COLUMN "fieldold" TO "fieldnew";

（6）设置字段默认值

	ALTER TABLE data_u11001000504 ALTER  column shzt set default '0';

6.3 删除字段

（1）删除字段（会删除数据）

	ALTER TABLE data_u11001000504  DROP COLUMN "varch";  

备注：字段都加上"" ,不然数值类型会失败。

6.4 创建序列并设置自增

创建自增序列：

CREATE SEQUENCE xzqh_wjgl_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

设置自增

alter table xzqh_wjgl alter column id set default nextval('xzqh_wjgl_id_seq')

6.5 获取序列当前值及下个值

// 获取下个序列值
select nextval('xzqh_wjgl_id_seq');
// 获取当前序列值
select currval('xzqh_wjgl_id_seq');

6.6 设置序列当前值

SELECT setval('"xzqh_wjgl_id_seq"', 3670, true);

7.删除表

DROP TABLE table_name;

8.创建及删除索引

CREATE INDEX "xzq_statistic_bm_index" ON "public"."xzq_statistic" USING btree (
  "bm"
);
DROP INDEX "public"."xzq_abvcounty_cid_btree_copy1_copy1_copy1_copy1_copy1";

9.继承关系创建及查询父表数据所在的字表

前提：父表所存在的字段，子表如果存在，则类型需一致。

alter table child_table inherit data_jbxx_father;

通过主表查询数据所在的字表：

select tableoid,* from data_jbxx_pc limit 1;
select * from pg_class where oid='1992996';

10.创建pg只读用户

CREATE USER smtb WITH ENCRYPTED PASSWORD 'tb2021'; -- 创建用户
alter user smtb set default_transaction_read_only=on; -- 设置事务只读
GRANT CONNECT ON DATABASE qgzhdc_sm_tbk_new to smtb;  -- 设置数据库

GRANT USAGE ON SCHEMA public to smtb;  -- 设置public模式
GRANT SELECT ON ALL TABLES IN SCHEMA public TO smtb;  -- 查询设置

11.数据库（迁移）备份与恢复

导出： pg_dump mydb > db.sql 或

pg_dump "host=172.16.x.x port=5432 user=postgres password=postgres dbname=qgzhdc-temp" |gzip  >test.gz

常用参数：

-a	–data-only	只转储数据，而不转储模式（数据定义）。表数据、大对象和序列值都会被转储。
-s	–schema-only	只转储对象定义（模式），而非数据
-t	–table=table	只转储匹配table的表（或视图、序列、外部表）。通过写多个-t开关可以选择多个表
–column-inserts	–attribute-inserts	将数据转储为带有显式列名的INSERT命令（INSERT INTO table (column, …) VALUES …）。这将使得恢复过程非常慢，这主要用于使转储能够被载入到非PostgreSQL数据库中。不过，由于这个选项为每一行都产生一个单独的命令，重载一行时的一个错误只会导致那一行被丢失而不是整个表内容丢失。

导入 ： psql -f ./db.sql -h 172.16.106.5 -p 5432 -U postgres mydb 或

 gunzip -c test.gz | psql "host=172.16.x.x port=5432 user=postgres password=postgres dbname=qgzhdc-temp"

详细参数 参见 : http://postgres.cn/docs/11/app-pgdump.html.

导出时使用 pg_dump -Fc dbname > filename
自定义格式的转储不是psql的脚本，只能通过pg_restore恢复，例如：
pg_restore -d dbname filename