postgre sql 学习

postgre sql 基础语法

1、创建模式

create schema demo_schema;

2、将新的模式加到搜索路径来

SET search_path TO myschema,public;

3、 修改字段名

alter table db_cxpg.t_pg_zb rename column n_sfyx to c_sfyx;

4、修改字段类型

alter table db_cxpg.t_pg_zb alter c_sfyx type varchar(50) set DEFAULT 1;

5、 修改默认值

ALTER TABLE db_cxpg.t_pg_zb ALTER COLUMN c_sfyx SET DEFAULT 1;  

6、查看表结构

   SELECT    col.table_schema ,    col.table_name ,    col.ordinal_position,    col.column_name ,    col.data_type ,    col.character_maximum_length,    col.numeric_precision,    col.numeric_scale,    col.is_nullable,    col.column_default ,    des.descriptionFROM    information_schema.columns col LEFT JOIN pg_description des        ON col.table_name::regclass = des.objoid    AND col.ordinal_position = des.objsubidWHERE    table_schema = schema_name    AND table_name = table_nameORDER BY    ordinal_position;