1.创建表

--判断表是否存在，不存在创建表
DROP TABLE IF EXISTS eform_ceshi_text;
CREATE TABLE eform_ceshi_text (
	ID VARCHAR ( 50 ) NOT NULL,
	createTime TIMESTAMP ( 6 ) NOT NULL,
	modifiedTime TIMESTAMP ( 6 ) NOT NULL,
	createId VARCHAR ( 50 ) NOT NULL,
	updateId VARCHAR ( 50 ) NOT NULL
	aaa int4
);

2.添加、删除主键

--添加主键
ALTER TABLE eform_ceshi_text ADD CONSTRAINT eform_text_pkey PRIMARY KEY (id);
--删除主键
ALTER TABLE eform_ceshi_text DROP CONSTRAINT "eform_text_pkey";

3.批量添加、删除字段

--批量添加字段
ALTER TABLE eform_ceshi_text ADD COLUMN name character varying not null, ADD COLUMN age int4 not null, ADD COLUMN birth character varying not null, ADD COLUMN test character varying not null;
--删除表字段
alter table eform_ceshi_text drop  test;

4.创建、删除索引

--创建索引
create index ix_age on eform_ceshi_text(age);
--删除索引
DROP INDEX ix_age;

5.添加、查询表注释

--添加表注释
comment on table eform_ceshi_text is '测试表';
--添加表字段注释(comment on column 【表名.字段名】 is 【注释】;)
COMMENT ON COLUMN eform_ceshi_text.age IS '年龄';
--查询字段注释，objsubid为表中字段的序号，从左侧从1开始
select description from pg_description join pg_class on pg_description.objoid = pg_class.oid	where relname = '年龄';
--查询表名、表注释
select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;

6.修改字段类型、字段名、表名

--修改字段类型(如：ID 字段 原类型为 character varying(50) 新类型为integer)
--alter table 【表名】 alter column  【字段名】 type 【字段类型】   using 【字段新类型】;
alter table eform_ceshi_text alter column birth type integer using to_number(birth,'9');
alter table eform_ceshi_text alter COLUMN name type varchar(20);
ALTER TABLE eform_ceshi_text alter COLUMN createId type VARCHAR(100);
--修改字段名
alter table eform_ceshi_text  rename name to  uname;
--修改表名
alter table eform_ceshi_text rename to eform_ceshi_text2;

7.查询视图、表、存储过程

-----查询视图、表、存储过程------------------------
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'VIEW'; --视图
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE';--表
SELECT ROUTINE_NAME FROM information_schema.Routines WHERE ROUTINE_TYPE = 'PROCEDURE';--存储过程

8.查询数据库表字段、类型、长度

--查询数据库表字段、类型、长度
select column_name as Name,udt_name as Type,CHARACTER_MAXIMUM_LENGTH as Length,1 as Exist from information_schema.columns where table_schema='public' and table_name= 'eform_ceshi_text';

9.查询表是否存在

--查询表是否存在
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE' and table_name= 'eform_ceshi_text';
select count(*) from pg_class where relname = 'eform_ceshi_text';

10.查询视图列、视图是否存在

--查询视图列
select column_name as Name,udt_name as Type,CHARACTER_MAXIMUM_LENGTH as Length from information_schema.columns where table_schema='public' and table_name='sys_grouplist';
--查询视图是否存在
select table_name from information_schema.tables where table_schema = 'public' and table_type = 'VIEW' and table_name = 'sys_grouplist';

11.查询存储过程是否存在、存储过程参数

--查询存储过程是否存在
SELECT ROUTINE_NAME FROM information_schema.Routines WHERE ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME = 'transfer'
--查询存储过程参数
SELECT CASE 
     WHEN pg_proc.proretset 
     THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL) 
     ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END, 
    pg_proc.proargtypes, 
    pg_proc.proargnames, 
    pg_proc.prosrc, 
    pg_proc.proallargtypes, 
    pg_proc.proargmodes, 
    pg_language.lanname 
FROM pg_catalog.pg_proc 
    JOIN pg_catalog.pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid) 
    JOIN pg_catalog.pg_language ON (pg_proc.prolang = pg_language.oid) 
where pg_catalog.pg_proc.proname ='transfer' or  pg_catalog.pg_proc.proname ='transfer2' ;

12.查询表字段是否存在、表是否存在指定索引查询视图

--根据表字段是否存在
SELECT column_name as columnName,udt_name as dataType FROM information_schema.columns WHERE TABLE_NAME ='eform_ceshi_text' and column_name = 'createid'; 
--查询表是否存在指定索引
select 1 from pg_class t,pg_class i where t.relkind = 'r' and t.relname = 'eform_ceshi_text' and i.relname = 'ix_age';

13.if判断语句

--执行IF判断语句格式
	do $$ 
	DECLARE  
		var1 text;  
		var2 int4;
		[...]  
	BEGIN
	--IF语句
	IF NOT EXISTS(select 1 from pg_class t,pg_class i where t.relkind = 'r' and t.relname = 'eform_ceshi_text' and i.relname = 'ix_age') THEN
	create index ix_age on eform_ceshi_text(age);
	END IF;
	END;$$  

14.方法/函数用法

--方法/函数用法：
	CREATE [OR REPLACE] FUNCTION function_name (arguments)   
	RETURNS return_datatype AS $variable_name$  
	  DECLARE  
		var1 text;  
		var2 int4;
		[...]  
	  BEGIN  
		< function_body >  
		[...]  
		RETURN { variable_name | value }  
	  END; LANGUAGE plpgsql;]

	-- 调用方法
	select function_name (arguments) ;