欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-21 21:03:52
...

目录

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';

相关标签: postgresql