postgresql：使用sql语句查询数据库各种信息

目录

shcema

表信息

表字段

查询主键

其他一些内置表

shcema

查询

select  * from information_schema.schemata  WHERE schema_name = ''

创建

create SCHEMA "schemaName";

表信息

SELECT * from information_schema.tables where "table_schema" = 'template' and "table_name" in( 'test1');

表字段

SELECT * from information_schema.columns where "table_schema" = 'template' and "table_name" in( 'test1');

-- 关联查询表

SELECT T
	.TABLE_NAME,
	C.COLUMN_NAME,
	C.ordinal_position,
	column_default,
CASE WHEN C.is_nullable = 'YES' THEN
	TRUE ELSE FALSE 
	END,
	C.udt_name AS data_type,
	C.character_maximum_length AS data_length,
	C.numeric_precision_radix AS numeric_length,
	C.numeric_scale AS numeric_scale,
	C.datetime_precision,
	C.is_identity 
FROM
	information_schema.
	COLUMNS C LEFT JOIN information_schema.tables T ON C.table_catalog = T.table_catalog 
	AND C.table_schema = T.table_schema 
	AND C.TABLE_NAME = T.TABLE_NAME 
WHERE
	C.table_schema = 'template' 
	AND C."table_name" = 'adjust_test111';

查询主键

SELECT
	pg_constraint.conname AS pk_name,
	pg_attribute.attname AS colname,
	pg_type.typname AS typename 
FROM
	pg_constraint
	INNER JOIN pg_namespace ON pg_constraint.connamespace = pg_namespace.oid
	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 ]
	INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid 
WHERE pg_namespace.nspname = '08ACFA5A-A985-4B0F-A984-DE96DD5686BF' 
	AND pg_class.relname = 'workpaper_table_description' 
	AND pg_constraint.contype = 'p';

查询sequence

-- 当前[若这个报is not yet defined in this session 则执行下nextval 这个函数的sql]
SELECT currval('seq_adjustment_table_column_id');
-- 下一个
SELECT nextval('seq_adjustment_table_column_id');
-- 赋值
SELECT  setval('seq_adjustment_table_column_id',100);
select setval('you_table_id_seq', (select max(you_table_id) from you_table_name));

其他一些内置表

select * from pg_namespace;
select * from pg_constraint;
select * from pg_class;
select * from pg_attribute;
select * from pg_classpg_type

常用语法

1、新增字段；
ALTER TABLE table_name ADD column_name datatype; 
2、删除字段：
ALTER TABLE table_name DROP  column_name;
3、更改字段的数据类型
ALTER TABLE table_name ALTER  column_name TYPE datatype; 
4、表的重命名
ALTER TABLE table_name RENAME TO new_name;          
5、更改字段的名字
ALTER TABLE table_name RENAME column_name to new_column_name; 
6、字段的not null设置
ALTER TABLE table_name ALTER column_name {SET|DROP} NOT NULL;       
7、修改字段长度；
ALTER TABLE your_table_name alter COLUMN your_column_name type character varying(3000);
通过上面的一句话就可以把你的表中相应的字段的长度修改为3000.
8、修改字段的默认值
为已有的字段添加默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;
删除默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
9、强制修改列的类型
当列的类型为字符类型，当我们想修改为数值类型时，是无法成功的，这个时候我们可以通过以下方法进行修改
ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING (column_name::integer);
如果字段里有空白字符，使用以下方法：
ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING (trim(column_name)::integer);
在类型转换前先去空白
10、给字段添加注释
comment on column table_name.column_name is 'The user ID';
11、给表添加注释：
comment on table table_name is 'Our session logs';