数据表

查看表

• 使用psql:
  • 列出所有表：\dt
  • 查看表结构: \d <table_name>
• 查询information_schema

SELECT column_name, data_type, column_default, is_nullable
FROM information_schema.columns
WHERE table_name = 'sessions';
-- AND table_schema = 'your_schema_name'; -- 如果不在默认 schema (public) 中

创建表

示例

CREATE TABLE IF NOT EXISTS public.ngxlog
(
    id numeric(24,0) NOT NULL DEFAULT nextval('ngxlog_id_seq'::regclass),
    connection character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT '0'::character varying,
    version character varying(15) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    tenant character varying(15) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    tenantc character varying(15) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    remote_addr character varying(16) COLLATE pg_catalog."default" NOT NULL DEFAULT '0.0.0.0'::character varying,
    remote_user character varying(16) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    request_method character varying(8) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    request_uri character varying(200) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    request_length character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT '0'::character varying,
    request_addr character varying(50) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    server_protocol character varying(10) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    status character varying(6) COLLATE pg_catalog."default" NOT NULL DEFAULT '0'::character varying,
    body_bytes_sent character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT '0'::character varying,
    http_referer character varying(50) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    http_user_agent character varying(50) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    http_x_forwarded_for character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    upstream_addr character varying(30) COLLATE pg_catalog."default" NOT NULL DEFAULT '-'::character varying,
    upstream_response_time character varying(10) COLLATE pg_catalog."default" NOT NULL DEFAULT '0'::character varying,
    request_time character varying(10) COLLATE pg_catalog."default" NOT NULL DEFAULT '0'::character varying,
    ts character varying(20) COLLATE pg_catalog."default" NOT NULL DEFAULT '2001-01-01 00:00:00'::character varying,
    CONSTRAINT ngxlog_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.ngxlog
    OWNER to herodotus;

建表时使用一个字段做主键

create table tb1(id int primary key, note varchar(20));

使用约束子句指定主键由两个及以上的字段组成（复合主键）

create table tb2(id1 int,id2 int,note varchar(20), constraint pk_tb2 primary key(id1,id2));

使用check约束。例如指定age字段的值不能大于18

create table tb3(id int primary key,name varchar(20),age int, constraint ck_tb3_age check(age <18));

以其它表为模板

create table tb4 (like tb1);
-- 方式2
create table tb4 as select * from tb1 with no data;

-- 复制包含约束
create table tb4 (like tb1 including constraint);
-- 复制包含所有属性
create table tb4 (like tb1 including all);

修改数据表

-- 增加字段
alter table tb1 add column c2 varchar(40);
-- 删除字段
alter table tb1 drop column c2;
-- 修改字段类型
alter table tb1 alter column request_uri type character varying(600);	
-- 设置默认值
alter table tb1 alter column age set default 18;
-- 删除默认值
alter table tb1 alter column age drop default;
-- 重命名字段
alter table tb1 rename column c1 to c2;
-- 重命名表名
alter table tb1 rename to tb2;

删除数据表

drop table ngxlog;

清空表

truncate table ngxlog;

truncate table作用类似不加条件的delete from table。truncate相当于重新定义一个新表，将旧表直接丢弃；delete是把数据一条条删除

临时表

postgres支持两种临时表，一种是会话级的临时表，另一种是事务级的临时表。会话级的临时表中数据可以保存在整个会话的生命周期中；而在事务级的临时表中，数据只存在于这个事务的生命周期中。

如果两个不同的会话中创建一个同名的临时表，实际上是两个不同的表。

创建临时表

默认创建的会话级临时表

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

事务级临时表

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

-- 使用
begin;
insert into tmp_tb2 values (1,'qwer');
select * from tmp_tb2;
-- 这时应显示刚插入的记录
end;

select * from tmp_tb2;
-- 因为事务结束，这时看临时表中已经没有数据

on commit的三种参数：

• on commit preserve rows，默认参数，数据会一直存在于整个会话周期中
• on commit delete rows，数据只存在于事务生命周期中，事务提交后数据就消失了
• on commit drop，数据只存在于事务生命周期中，事务提交后数据和临时表都会被删除。

UNLOGGED表

UNLOGGED表自Postgres 9.1版本引入，主要是通过禁止产生WAL日志来提升写性能。因为不产生WAL日志，增删改性能会提升，但数据安全性得不到保证。

create unlogged table tb01_unlogged(id int primary key, c1 text);

约束

约束目前有以下几类：

• 检查约束

create table tb1 (
	name varchar(40),
    age int check (age >=0 and age <=150),
    sex boolean
);
-- 还可以给约束单独命名，这样报错的时候错误信息更清晰
create table tb1 (
	name varchar(40),
    age int constraint check_tb1_age check (age >=0 and age <=150),
    sex boolean
);

-- 引用多个字段
create table books (
	book_no int,
    name text,
    price numeric check (price > 0), -- 检查价格大于0
    discounted_price numeric check (discounted_price > 0), -- 检查优惠价格
    check (price > discounted_price) -- 优惠价格应低于原价
);

• 非空约束，声明字段不为NULL
• 唯一约束
• 主键
• 外键约束。约束本表中一个或多个字段的数值必须存在于另一个表的一个或多个字段中。这种约束也可以称为两个相关表之间的参照完整性约束。

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) -- 外键约束class_no的取值必须出现在表class中。
);

增加约束

-- 增加检查约束
alter table tb1 add check(age < 150);
-- 增加唯一约束
alter table tb1 add constraint unique_tb1_stu_no unique (stu_no);
-- 增加非空约束
alter table tb1 alter column stu_name set not null;

删除约束。在psql使用\d tablename可以看到约束名称。

alter table tb1 drop constraint constraint_name;
-- 删除非空约束
alter table tb1 alter column stu_name drop not null; 

表继承

表继承是pgsql特有的。假设有一张人员表 persons

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

增加一个学生表继承persons表，学生表多一个班级包字段 "class_no"

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

如果向students表增加数据，persons表也有相应记录，只是没有子表字段。如果只想要父表的数据，只需要添加ONLY关键字

select * from only persons;

但如果向persons表增加数据，students并不会看到这条数据。

一个子表可以从多个父表继承，所有父表的字段、检查约束和非空约束都会被子表继承。如果同一个字段名存在于多个父表，或者同时存在于父表和子表的定义中，那么同名字段会合并为一个字段。合并时字段的数据类型必须相同，否则会报错。

表分区

表分区就是把逻辑上的一张大表分割成物理上的几块。使用表分区有以下好处：

• 删除历史数据更快。如果是按时间分区，在删除历史数据时，直接删除历史分区即可。
• 某些场景下查询性能会得到极大提升。当热点数据分布在同一块分区或少数几个分区时，查询性能会提升很多。
• 低频使用分区数据可以通过表空间的方法，将其移动到较廉价的慢速存储介质中。

当单表数据量大小超过了服务器内存大小时就可以考虑使用表分区了。

目前有两种创建分区表的方式：

• 声明式分区。postgres 10版本后出现，通过DDL语句直接创建分区表，内部原理还是表继承。
• 继承式分区。postgres 10版本之前只能通过表继承方式创建分区表。

声明式分区

创建声明式分区的父表

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

继承式分区

创建父表，父表不直接写入数据，一般也不会在父表创建约束、索引等。

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_contact text -- 买家联系方式
);

按销售日期分区，每个月为一个分区

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

使用触发器实现向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 < date '2014-02-01') then
		insert into sales_detail_y2014m01 values (new.*);
	elsif (new.sale_date >= date '2014-02-01' and new.sale_date < date '2014-03-01') then
		insert into sales_detail_y2014m02 values (new.*);
	elsif (new.sale_date >= date '2014-03-01' and new.sale_date < date '2014-04-01') then
		insert into sales_detail_y2014m03 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();

添加新分区时，注意需要修改触发器内容。

表碎片

在pg中删除行时，这些行只是被标记为"dead"，并不是真正从物理存储上进行删除，因此空间也没有释放回收。除非进行自动清理（vacuum）或手动清理，否则数据块所占用的物理空间不会被回收。因此在物理存储空间被回收之前，会导致存储空间存在很多空洞。

这些空洞可能造成数据存储位置不连续，物理存储顺序和逻辑存储顺序不同，这就是数据碎片。

查看表体积

select pg_size_pretty(pg_relation_size('table_name'));

使用psql查看表的状态信息，输出结果中n_live_up的数目是当前表的数据量，n_dead_tup的数据量是未回收的空间。

scott# \x
scott# select * from pg_stat_user_tables where relname = 'table_name';

手动清理碎片

vacuum table_name;