PostgreSQL学习篇10.2 表

以其他表创建新表：
postgres=# create table old(id int,id2 int,col1 int,constraint pk_old primary key(id,id2));
CREATE TABLE
postgres=# create table new (like old);
CREATE TABLE
postgres=# d old
      Table "public.old"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
 id2    | integer | not null
 col1   | integer |
Indexes:
    "pk_old" PRIMARY KEY,btree (id,id2)

postgres=# d new
      Table "public.new"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
 id2    | integer | not null
 col1   | integer |

 没有复制主键，如果想要连约束一起复制过来，那么需要使用including选项，可用的including选项：
including defaults
including constraints
including indexes
including storage
including comments
including all


postgres=# create table new2 (like old including all);
CREATE TABLE
postgres=# d old
      Table "public.old"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
 id2    | integer | not null
 col1   | integer |
Indexes:
    "pk_old" PRIMARY KEY,id2)

postgres=# d new2
     Table "public.new2"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null
 id2    | integer | not null
 col1   | integer |
Indexes:
    "new2_pkey" PRIMARY KEY,id2)

postgres=#

熟悉的语法：

postgres=# create table new3 as select * from old with no data;
CREATE TABLE AS
postgres=# d new3
     Table "public.new3"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 id2    | integer |
 col1   | integer |

postgres=#

表的存储属性：
TOAST（The Oversized-Attribute Storage Technique）

postgres=# create table blog (id int,title text,content text);
CREATE TABLE
postgres=# d+ blog
                          Table "public.blog"
 Column  |  Type   | Modifiers | Storage  | Stats target | Description
---------+---------+-----------+----------+--------------+-------------
 id      | integer |           | plain    |              |
 title   | text    |           | extended |              |
 content | text    |           | extended |              |

postgres=# alter table blog alter content set storage external;
ALTER TABLE
postgres=# alter table blog alter title set storage main;
ALTER TABLE
postgres=# d+ blog
                          Table "public.blog"
 Column  |  Type   | Modifiers | Storage  | Stats target | Description
---------+---------+-----------+----------+--------------+-------------
 id      | integer |           | plain    |              |
 title   | text    |           | main     |              |
 content | text    |           | external |              |

postgres=#

main：允许压缩，不允许行外存储。
external：允许行外存储，不允许压缩。
extended：允许压缩和行外存储。
plain：避免压缩或行外存储。

存储参数：fillfactor和toast.fillfactor，值为10到100，如设置fillfactor为60，则数据填充一个数据库到60%时就不再填充，剩余40%留作update用。如果表更新频繁，那么需要设置一个较小的fillfactor值。

临时表：
跟oracle一样，也分为会话级临时表和事务级临时表；与oracle不一样，完事后，没的不仅仅是数据，还有表。
会话级临时表：
postgres=# create temporary table test_tmp(id int,note text);
CREATE TABLE
postgres=# d
            List of relations
  Schema   |   Name   | Type  |  Owner   
-----------+----------+-------+----------
 pg_temp_2 | test_tmp | table | postgres
 public    | blog     | table | postgres
 public    | new      | table | postgres
 public    | new2     | table | postgres
 public    | new3     | table | postgres
 public    | old      | table | postgres
 public    | t        | table | postgres
 public    | testfun  | table | postgres
 public    | testsz   | table | postgres
 public    | tt       | table | postgres
 public    | txt      | table | postgres
(11 rows)


事务级临时表：
postgres=# create temporary table test_ttmp(id int,note text) on commit delete rows;
CREATE TABLE
postgres=# d
            List of relations
  Schema   |   Name    | Type  |  Owner   
-----------+-----------+-------+----------
 pg_temp_2 | test_tmp  | table | postgres
 pg_temp_2 | test_ttmp | table | postgres
 public    | blog      | table | postgres
 public    | new       | table | postgres
 public    | new2      | table | postgres
 public    | new3      | table | postgres
 public    | old       | table | postgres
 public    | t         | table | postgres
 public    | testfun   | table | postgres
 public    | testsz    | table | postgres
 public    | tt        | table | postgres
 public    | txt       | table | postgres
(12 rows)

postgres=#

 postgres-# q
[postgres@pg ~]$ psql
psql (9.6.1)
Type "help" for help.

postgres=# d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | blog    | table | postgres
 public | new     | table | postgres
 public | new2    | table | postgres
 public | new3    | table | postgres
 public | old     | table | postgres
 public | t       | table | postgres
 public | testfun | table | postgres
 public | testsz  | table | postgres
 public | tt      | table | postgres
 public | txt     | table | postgres
(10 rows)

postgres=#    表结构都没有了。

增加非空约束：
alter table tab_name alter column col_name set not null;
删除非空约束：
alter table tab_name alter column col_name drop not null;

修改默认值：
alter table tab_name alter column col_name set default xxx;
删除默认值：
alter table tab_name alter column col_name drop default;


表继承：
很特别的技术。

postgres=# create table animal(name varchar(10),age int,sex boolean);
CREATE TABLE
postgres=# create table dog(leg int) inherits (animal);
CREATE TABLE
postgres=# d animal
           Table "public.animal"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 name   | character varying(10) |
 age    | integer               |
 sex    | boolean               |
Number of child tables: 1 (Use d+ to list them.)

postgres=# d dog
             Table "public.dog"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 name   | character varying(10) |
 age    | integer               |
 sex    | boolean               |
 leg    | integer               |
Inherits: animal

postgres=# select * from animal;
 name | age | sex
------+-----+-----
(0 rows)

postgres=# select * from dog;
 name | age | sex | leg
------+-----+-----+-----
(0 rows)

postgres=# insert into dog values('小花',11,true,4);
INSERT 0 1
postgres=# select * from animal;
 name | age | sex
------+-----+-----
 小花 |  11 | t
(1 row)

postgres=# select * from dog;
 name | age | sex | leg
------+-----+-----+-----
 小花 |  11 | t   |   4
(1 row)

子表插入数据，父表能看到。

postgres=# insert into animal values('小花2',4);
ERROR:  INSERT has more expressions than target columns
LINE 1: insert into animal values('小花2',4);
                                                  ^
postgres=# insert into animal values('小花2',true);
INSERT 0 1
postgres=# select * from animal;
 name  | age | sex
-------+-----+-----
 小花2 |  11 | t
 小花  |  11 | t
(2 rows)

postgres=# select * from dog;
 name | age | sex | leg
------+-----+-----+-----
 小花 |  11 | t   |   4
(1 row)

父表插入数据，字表却不行。
只查看父表的数据：
postgres=# select * from only animal;
 name  | age | sex
-------+-----+-----
 小花2 |  11 | t
(1 row)

所有父表的检查约束和非空约束都会自动被所有子表继承。不过其他类型的约束（唯一、主键、外键）则不会被继承。

分区表：
PG是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几个小块。
表的大小超过了数据库服务器的物理内存大小则应该使用分区表。
在使用继承实现的分区表时，一般会让父表为空，数据都存储在子表中。
确保postgre.conf里的配置参数constraint_exclusion是打开的。打开后，如果查询中where子句的过滤条件与分区的约束条件匹配，那么这个查询会智能的只查询这个分区，而不会查询其他分区。

postgres=# create table student(class int,name varchar(10),sex char(1));
CREATE TABLE
postgres=#
postgres=# create table student_class1(check (class=1)) inherits (student);
CREATE TABLE
postgres=# create table student_class2(check (class=2)) inherits (student);
CREATE TABLE
postgres=# create table student_class3(check (class=3)) inherits (student);
CREATE TABLE
postgres=#
postgres=# --创建触发器
postgres=# create or replace function student_insert_trigger()
postgres-# returns trigger as $$
postgres$# begin
postgres$# if(new.class=1) then
postgres$# insert into student_class1 values (new.*);
postgres$# elsif (new.class=2) then
postgres$# insert into student_class2 values (new.*);
postgres$# elsif (new.class=3) then
postgres$# insert into student_class3 values (new.*);
postgres$# else
postgres$# raise exception 'no class found.fix the student_insert_trigger function!';
postgres$# end if;
postgres$# return null;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
postgres=# create trigger insert_student_class_trigger
postgres-# before insert on student
postgres-# for each row execute procedure student_insert_trigger();
CREATE TRIGGER
postgres=# d student
           Table "public.student"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 class  | integer               |
 name   | character varying(10) |
 sex    | character(1)          |
Triggers:
    insert_student_class_trigger BEFORE INSERT ON student FOR EACH ROW EXECUTE PROCEDURE student_insert_trigger()
Number of child tables: 3 (Use d+ to list them.)

postgres=#

postgres=# insert into student values(1,'lm','n');
INSERT 0 0
postgres=# select * from student;
 class | name | sex
-------+------+-----
     1 | lm   | n
(1 row)

postgres=# select * from student_class1;
 class | name | sex
-------+------+-----
     1 | lm   | n
(1 row)

postgres=# select * from student_class2;
 class | name | sex
-------+------+-----
(0 rows)

postgres=# select * from student_class3;
 class | name | sex
-------+------+-----
(0 rows)

postgres=#

postgres=# show constraint_exclusion;
 constraint_exclusion
----------------------
 partition
(1 row) 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!