创建表

# 单主键
create table test01 (
  id int primary key,
  note varchar(20)
);

------------------------------------------------

# 复合主键(pk_test02是复合主键的名字)
create table test02 (
  id1 int,
  id2 int,
  note varchar(20),
  constraint pk_test02 primary key(id1, id2)
);

------------------------------------------------

# 约束唯一键
create table test03 (
  id1 int,
  id2 int, 
  id3 int,
  note varchar(20),
  constraint pk_test03 primary key(id1, id2),
  constraint uk_test03_id3 unique(id3) 
);

------------------------------------------------

# check约束
# 约束age年龄不能大于18岁
create table child (
  name varchar(20),
  age int,
  note text,
  constraint ck_child_age check(age<18)
);

------------------------------------------------

# 以其他表为模板创建新表(不会复制列约束, 需要加including关键字)
create table baby (like child);

------------------------------------------------

# 表字段默认值
create table student (
  no int,
  name varchar(20),
  age int default 15
);

create table blog (
  id int,
  title text,
  created_date timestamp default now()
);

update student set age=default where no=2;

------------------------------------------------

TOAST技术

就是特别大的字段的值，会被切割压缩存到另一张TOAST表中，可以使用
alter table set storage设置或者修改这个规则

alter table blog alter content set storage  external;

临时表

会话级临时表：数据保存在整个会话的生命周期中
事务级临时表：数据只存在这个事务的生命周期中

(会话session：也就是客户端与PostgreSQL 服务器的一次连接实例，从登录开始到断开连接结束‌)

创建会话级临时表

create temporary table tmp_t1 (
  id int primary key,
  note text
);

创建事务级临时表

create temporary table tmp_t2 (
  id int primary key,
  note text
) on commit delete rows;

/**
  on commit子句的3种形式：

    1、on commit preserve rows: 不带"on commit",默认数据存在整个会话周期
    2、on commit delete rows: 数据只存在事务周期中，事务提交后数据就消失了
    3、on commit drop: 数据存在事务周期中，事务提及后临时表消失

 */

unlogged表 (半临时表)

没有wal日志，写性能高了，但是数据库异常宕机，数据会丢失

create unlogged table unlogged01 (
  id int primary key,
  t text
);

约束

分类：

• 检查约束
• 非空约束
• 唯一约束
• 主键
• 外键约束

检查约束

create table persons (
  name varchar(40),
  age int check (age >= 0 and age <= 150), -- 限制年龄在0～150之间
  sex boolean
);

# 约束命名为check_age
create table persons (
  name varchar(40),
  age int constraint check_age check (age >= 0 and age <= 150),
  sex boolean
);

# 多个字段check
create table books (
  book_no integer,
  name text,
  price numeric check (price > 0),  # 原价 - 字段约束
  discounted_price numeric check (discounted_price > 0), # 打折价格 - 字段约束
  check (price > discounted_price) # 原价 > 打折价格 - 表约束
);

非空约束

create table books (
  book_no integer not null, -- 声明一个字段不能是null
  name text,
  price numeric
);

# 结合检查约束
create table books (
  book_no integer not null,
  name text,
  price numeric not null check (price > 0) 
);

唯一约束

1个或1组字段相较于其他行是唯一的

create table books (
  book_no integer unique,  # 字段约束唯一
  name text,
  price numeric 
);

create table books (
  book_no integer,
  name text,
  price numeric,
  unique(book_no) # 表约束唯一
);

主键

主键和唯一约束的区别是：主键不能为null

create table books (
  book_no integer primary key,
  name text,
  price numeric,
  unique(book_no)
);

外键约束(参照完整性约束)

约束本表中一个或多个字段的数值必须出现在另一个表的一个或多个字段中

create table class (
  class_no int primary key,
  class_name varchar(40)
)

create table student (
  student_no int primary key,
  student_name varchar(40),
  age int,
  class_no int references class(class_no)
)

修改表

# 增加字段
alter table class add column class_teacher varchar(40);

# 增加字段并且增加约束
alter table class add column class_teacher varchar(40) check (class_teacher <> '');

# 删除字段
alter table class drop column class_teacher;

# 如果删除的字段是另一个表的外键，需要增加cascade，同时把另一个表的外键都删掉
alter table class drop column class_no cascade;

# 增加约束
alter table student add check (age < 16);
alter table class add constraint unique_class_teacher unique (class_teacher);
alter table student alter column student_name set not null;

# 删除约束
alter table student drop constraint constraint_name;
# 删除非空约束
alter table student alter column student_name drop not null;

# 修改字段默认值
alter table student alter column age set default 15;
# 删除字段默认值
alter table student alter column age drop default;

# 修改字段数据类型
alter table student alter column student_name type text;

# 重命名字段
alter table class rename ro classes;

表继承

create table persons (
  name text,
  age int,
  sex boolean
);

create table students (
  class_no int
) inherits (persons);

⚠️向students插入数据，在persons中也能看到，但是在persons中插入数据，students中看不到
⚠️所有父表中的检查约束和非空约束都会被子表继承，其他类型约束(唯一、主键、外键)不会被继承
⚠️子表可以继承多个父表
⚠️使用select、update、delete访问操作父表的时候，也会同时访问和操作子表，使用alter table修改父表结构的定义时，也会同时修改子表的结构定义，但'reindex'和'vacuum'命令不会影响子表
如果只想查询父表(persons)表中的数据:select * from only persons;

分区表

表很大的时候，需要把表拆分成几个小部分，查询更快，就是分区表

# 主表
create table sales_detail (
  product_id  int  not null, -- 产品编号
  price numeric(12, 2),      -- 单价
  amount int not null,       -- 数量 
  sale_date date not null,   -- 销售日期
  buyer varchar(40),         -- 买家名称
  buyer_content text         -- 买家的联系方式
);

# 按销售日期分区，每个月一个分区 （继承的方式实现分区表）
create table sale_detail_y2014m01 (check (sale_date >= date '2014-01-01' and sale_date < date '2014-02-01')) inherits (sales_detail); 

create table sale_detail_y2014m02 (check (sale_date >= date '2014-02-01' and sale_date < date '2014-03-01')) inherits (sales_detail); 

create table sale_detail_y2014m03 (check (sale_date >= date '2014-03-01' and sale_date < date '2014-04-01')) inherits (sales_detail); 

/* ...... */

create table sale_detail_y2014m12 (check (sale_date >= date '2014-12-01' and sale_date < date '2015-01-01')) inherits (sales_detail); 

/*
  父表不存放数据，删除旧数据，只需要删除月份最早的表
 */


 # 自动把数据插入正确的的分区，创建触发器
create or replace function sale_detail_insert_trigger()
returns trigger as $$
begin
  if (new.sale_date >= date '2014-01-01' and new.sale_date < '2014-02-01') then 
    insert into sales_detail_y2014m01 values (new.*);
  elseif (new.sale_date >= date '2014-02-01' and new.sale_date < '2014-03-01') then 
    insert into sales_detail_y2014m02 values (new.*);
    /* ......省略代码 */
  elseif (new.sale_date >= date '2014-12-01' and new.sale_date < '2015-01-01') then
    insert into sales_detail_y2014m12 values (new.*);
  else 
    raise exception 'Date out of range. Fix the sale_detail_insert_trigger () function!';
  end if;
return null;
end;
$$
language plpgsql;
create trigger insert_sale_detail_trigger
  before insert on sale_detail
  for each row execute procedure sale_detail_insert_trigger ();

 

# 声明式分区
create table sales_detail (
  product_id  int  not null, -- 产品编号
  price numeric(12, 2),      -- 单价
  amount int not null,       -- 数量 
  sale_date date not null,   -- 销售日期
  buyer varchar(40),         -- 买家名称
  buyer_content text         -- 买家的联系方式
) partition by range (sale_date);   -- 声明式分区

# 创建分区表的分区
create table sales_detail_y2014m01 partition of sales_detail
  for values from ('2014-01-01') to ('2014-02-01');

create table sales_detail_y2014m02 partition of sales_detail
  for values from ('2014-02-01') to ('2014-03-01');

create table sales_detail_y2014m03 partition of sales_detail
  for values from ('2014-03-01') to ('2014-04-01');