一、创建表

drop table if exists student;
create table student( 
  id integer NOT NULL
  , c_id integer NOT NULL
  , sex character (1)
  , name character (100)
  , primary key (id, c_id)
);

二、添加注释

comment on table student is '学生信息表';
comment on column student.id is '学号';

三、删除表

drop table student;

四、创建删除模式

create schema school;
drop schema school;

五、添加删除字段

alter table student add column age character(3);
alter table student add age character(3);  -- 简写
alter table student drop column age;
alter table student drop age;  -- 简写

六、修改字段类型

alter table student alter column age type character(20);

七、重命名表/表字段

alter table student rename to stu;
alter table student rename column c_id to cid;

八、字段添加/删除 NOT NULL 约束

alter table student alter age set NOT NULL;
alter table student alter age drop NOT NULL;

九、字段添加/删除DEFAULT

alter table student alter column age set default 18;
alter table student alter column age drop default;

十、添加/删除主键

alter table student add constraint student_pkey primary key(id, cid);
alter table student drop constraint student_pkey;

十一、添加自增

在PostgreSQL中，指定下列类型smallserial，serial，bigserial默认为自增字段。

create sequence id_seq
start with 1 -- 指定序列的起点
increment by 2 -- 指定序列的步长
minvalue 1 -- 指定序列的最小值
no maxvalue; -- 指定序列的最大值

-- 给字段添加序列
alter table student alter column id set default nextval('id_seq');

十二、删除自增

drop sequence id_seq;
-- 如果有字段引用了id_seq序列会报错：无法删除 序列 id_seq 因为有其它对象依赖它
-- 这时用级联删除即可
drop sequence id_seq CASCADE;

十三、查询表主键名

SELECT
    pg_constraint.conname AS pk_name
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
    pg_class.relname = 'student'
AND pg_constraint.contype = 'p';

十四、查名表主键名及主键字段

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 ] -- 改为2，则取第二个主键字段
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
    pg_class.relname = 'student'
AND pg_constraint.contype = 'p'

十五 、要想一次显示全部主键字段

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name,
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
 FROM 
     information_schema.table_constraints AS tc
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name = 'student';

-- constraint_type：UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY