postgres数据库创建主分表的语法

基础：

一、存储过程结构：

         Create or replace function 过程名(参数名 参数类型,…..) returns 返回值类型 as

                   $body$

 

                            //声明变量

                            Declare

                            变量名变量类型；

                            如：

                            flag Boolean;

 

                            变量赋值方式（变量名类型 ：=值；）

                            如：

                            str  text :=值; / str  text;  str :=值；

 

                            Begin

                                     函数体；

 

                             return 变量名； //存储过程中的返回语句

 

                            End;

                   $body$

         Language plpgsql;

 

二、变量类型 ：

除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。

 

整数数据类型：

子类型	标准名	描述
Smalll integer	Smallint	一个2字节的符号型整数，可以存储-32768到32767的数字
Integer	Int	一个4字节的符号型整数，可以存储-2147483648到2147473647的数字
Serial	和integer一样，除了它的值通常是由PostgreSQL自动输入的。

 

浮点数据类型：（浮点数据也可以再细分，分为提供通用功能的浮点值和固定精度的数字）

子类型	标准名	描述
float	float(n)	支持最少精度为n，存储为最多8字节的浮点数。
float8	real	双精度（8字节）浮点数字
numeric	numeric(p,s)	拥有p个数字的实数，其中小数点后有s位。不像float，这始终是一个确切的数字，但工作效率比普通浮点数字低。
money	numeric(9,2)	PostgreSQL特有的类型，但在其他数据库里也普遍存在。Money类型从PostgreSQL 8.0开始不赞成使用，且可能在以后版本中取消。你应该使用number类型代替。

注：

存储float和real类型的数据的行为非常相似，但是numeric列的行为有点不同。Numeric类型不是存储接近的数，而是在小数后面进行后超出固定长度的部分进行四舍五入。如果我们存储太大的数据到其中，INSERT将失败。还要注意float和real也会对数字四舍五入；例如123.456789被四舍五入为123.457。

 

时间数据类型：

定义	意义
date	存储日期信息
time	存储时间信息
timestamp	存储日期和时间
interval	存储timestamp之间差别的信息
timestamptz	PostgreSQL扩展的类型，存储包含时区信息的timestamp

 

特殊数据类型：

定义	意义
box	矩形盒子
line	一组点
point	一对几何学的数字
lseg	一条线段
polygon	一条封闭的几何线
cidr或inet	一个IPv4的地址，录入192.168.0.1
macaddr	以MAC地址（以太网卡物理地址）

注：PostgreSQL也允许你使用SQL命令CREATE TYPE在数据库中建立你自己的类型。这通常不需要，而且在一定程度上，它是PostgreSQL独有的。

数组

通常，一个数组需要通过使用一个附加表实现。但是，数组的能力有时候很有用。建立数组的方法有两种：传统的PostgreSQL的方法和SQL99标准的方法。

PostgreSQL样式的数组

要将一个表的列定义为数组，你可以简单地在类型后面添加[]；不需要定义元素的个数。即使定义了个数，也不会强制要求存储的个数。

Eg：

test=> CREATE TABLE empworkday (

test(>     refcode char(5),

test(>     workdays int[]

test(> );

往数组列中插入值：

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,0,1,1,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,‘{0,1,1,1,1,0,1}’);

 

SQL99样式的数字

在SQL99标准中，必须指出元素的个数。

Eg:

test=> CREATE TABLE empworkday (

test(>     refcode char(5),

test(>     workdays int array[7]

test(> );

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,0,1,1,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,‘{0,1,1,1,1,0,1}’);

 

三、连接字符：

         Postgresql存储过程中的连接字符不再是“+”,而是使用“||”。

四、  控制结构：

1、if 条件（五种形式）

IF ... THEN

IF ... THEN ... ELSE

IF ... THEN ... ELSE IF

IF ... THEN ... ELSIF ... THEN ... ELSE

IF ... THEN ... ELSEIF ... THEN ... ELSE（注：ELSEIF 是ELSIF 的别名）

2、循环

使用LOOP，EXIT，CONTINUE，WHILE， 和FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。

1）、LOOP

[ <<label>> ]

LOOP

    statements

END LOOP [ label ];

LOOP 定义一个无条件的循环，无限循环， 直到由EXIT或者RETURN语句终止。可选的label 可以由EXIT 和CONTINUE 语句使用， 用于在嵌套循环中声明应该应用于哪一层循环。

2）、EXIT

	EXIT [ label ] [ WHEN expression ];

	如果没有给出label， 那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句。 如果给出 label， 那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。

如果声明了WHEN，循环退出只有在expression 为真的时候才发生， 否则控制会落到EXIT 后面的语句上。

EXIT 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。在和 BEGIN 块一起使用的时候，EXIT 把控制交给块结束后的下一个语句。

例如:

Loop  循环

If … then  条件判断

Exit ; 条件成立，则退出循环。

End if;

End loop;

3）、CONTINUE

CONTINUE [label ] [ WHENexpression ];

如果没有给出 label，那么就开始最内层的循环的下一次执行。也就是说，控制传递回给循环控制表达式（如果有），然后重新计算循环体。 如果出现了label，它声明即将继续执行的循环的标签。

如果声明了 WHEN，那么循环的下一次执行只有在expression 为真的情况下才进行。否则，控制传递给CONTINUE 后面的语句。

CONTINUE 可以用于所有类型的循环； 它并不仅仅限于无条件循环。

例如:

LOOP

    一些计算

    EXIT WHEN count > 100;

    CONTINUE WHEN count < 50;

    一些在count 数值在 [50 .. 100] 里面时候的计算

END LOOP;

4）、WHILE

	[ <<label>> ]

	WHILE expression LOOP

    	statements

	END LOOP [ label ];

只要条件表达式为真，WHILE语句就会不停在一系列语句上进行循环. 条件是在每次进入循环体的时候检查的.

例如:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP

    -- 可以在这里做些计算

END LOOP;

WHILE NOT BOOLEAN_expression LOOP

    -- 可以在这里做些计算

END LOOP;

5）、FOR（整数变种）

	[ <<label>> ]

	FOR name IN [ REVERSE ] expression .. expression LOOP

    	statements

	END LOOP [ labal ];

这种形式的FOR对一定范围的整数数值进行迭代的循环。变量name 会自动定义为integer类型并且只在循环里存在。给出范围上下界的两个表达式在进入循环的时候计算一次。 迭代步进值总是为 1，但如果声明了REVERSE就是 -1。

一些整数FOR循环的例子∶

FOR i IN 1..10 LOOP 表示1循环到10

  这里可以放一些表达式

    RAISE NOTICE 'i IS %', i;

END LOOP;

FOR i IN REVERSE 10..1 LOOP

    这里可以放一些表达式

END LOOP;

如果下界大于上界（或者是在 REVERSE 情况下是小于），那么循环体将完全不被执行。而且不会抛出任何错误。

 

3、异常捕获

        EXCEPTION

WHEN 错误码（如：STRING_DATA_RIGHT_TRUNCATION：字串数据右边被截断） THEN

        /**后台打印错误信息*/

        RAISE NOTICE '错吴信息';

 

五、示例代码：

/**

批量插入一批数据，经纬度字段值要满足中国地理位置上的经纬度范围；

注：时间不能指定为同一时间，否则会扫描全表，导致性能低下。下列脚本未考虑时间的分段，采用的一个时间点。

*/

create orreplace function intobatch() returns integer as

$body$

declare

    skyid integer;

    lot float;

         lat float;

         sex varchar;

         level integer;

         ctime int :=1325404914;

         num integer :=0;

         total integer :=0;

    begin

                   lot='73.6666666';

                   lat='3.8666666';

                   FOR skyid IN 404499817 ..404953416 loop

                             if(lot > 135.0416666) then

             lot=73.6666666;

                             end if;

                        if(lat > 53.5500000) then

             lat=3.8666666;

                             end if;

                             if(skyid%2 <> 0) then

                                                        sex='1';

                                                        level=0;

                             else

                                                        sex='2';

                                                        level=1;

                             end if;

 

                            INSERT INTO user_last_location(user_id,app_id,lonlat,sex,accurate_level,lonlat_point,create_time)

 

VALUES(skyid,2934,ST_GeomFromText('POINT('||lot||' '||lat||')',4326),sex,level,POINT(lot,lat),to_timestamp(ctime));

                           

                            lot=lot+0.1;

                            lat=lat+0.1;

                            skyid=skyid+1;

                           

        end loop;        

                   return skyid;  

    end

$body$

languageplpgsql;

 

SELECT *from intobatch();

分表：

因为对postgres数据库的sql语法不是很熟悉，在创建表的时候，遇到一些困难。现将基本语法示例备忘在博客里。

主表建表语句
CREATE TABLE tbl_master
(
  id serial NOT NULL,
  name character varying(255),
  create_time timestamp with time zone DEFAULT now(),
  jsessionid character varying(255),
  ip character varying(255),
  age integer,
  secondname character varying(255),
  href character varying(255),
  classid bigint,
  workid bigint,
  famliyid bigint,
  CONSTRAINT tbl_master_key PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

分表建表语句
CREATE TABLE tbl_slave
(
-- 继承 from table tbl_master:  id integer NOT NULL DEFAULT nextval('tbl_master_id_seq'::regclass),
-- 继承 from table tbl_master:  name character varying(255),
-- 继承 from table tbl_master:  create_time timestamp with time zone DEFAULT now(),
-- 继承 from table tbl_master:  jsessionid character varying(255),
-- 继承 from table tbl_master:  ip character varying(255),
-- 继承 from table tbl_master:  age integer,
-- 继承 from table tbl_master:  secondname character varying(255),
-- 继承 from table tbl_master:  href character varying(255),
-- 继承 from table tbl_master:  classid bigint,
-- 继承 from table tbl_master:  workid bigint,
-- 继承 from table tbl_master:  famliyid bigint
)
INHERITS (tbl_master)
WITH (
  OIDS=FALSE
);

这样建表，才能使向分表插入记录时一同插入主表。

数据库维护：

一、恢复磁盘空间：

    在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作。
    VACUUM命令存在两种形式，VACUUM和VACUUM FULL，它们之间的区别见如下表格：

	无VACUUM	VACUUM	VACUUM FULL
删除大量数据之后	只是将删除数据的状态置为已删除，该空间不能记录被重新使用。	如果删除的记录位于表的末端，其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据，该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态，那么在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑使用新增的磁盘页面。	不论被删除的数据是否处于数据表的末端，这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时，将分配新的磁盘页面以供使用。
执行效率		由于只是状态置为操作，因此效率较高。	在当前版本的PostgreSQL(v9.1)中，该命令会为指定的表或索引重新生成一个数据文件，并将原有文件中可用的数据导入到新文件中，之后再删除原来的数据文件。因此在导入过程中，要求当前磁盘有更多的空间可用于此操作。由此可见，该命令的执行效率相对较低。
被删除的数据所占用的物理空间是否被重新规划给操作系统。	不会	不会	会
在执行VACUUM命令时，是否可以并发执行针对该表的其他操作。		由于该操作是共享锁，因此可以与其他操作并行进行。	由于该操作需要在指定的表上应用排它锁，因此在执行该操作期间，任何基于该表的操作都将被挂起，知道该操作完成。
推荐使用方式	在进行数据清空是，可以使用truncate操作，因为该操作将会物理的清空数据表，并将其所占用的空间直接归还于操作系统。	为了保证数据表的磁盘页面数量能够保持在一个相对稳定值，可以定期执行该操作，如每天或每周中数据操作相对较少的时段。	考虑到该操作的开销，以及对其他错误的排斥，推荐的方式是，定期监控数据量变化较大的表，只有确认其磁盘页面占有量接近临界值时，才考虑执行一次该操作。即便如此，也需要注意尽量选择数据操作较少的时段来完成该操作。
执行后其它操作的效率	对于查询而言，由于存在大量的磁盘页面碎片，因此效率会逐步降低。	相比于不执行任何VACUUM操作，其效率更高，但是插入的效率会有所降低。	在执行完该操作后，所有基于该表的操作效率都会得到极大的提升。

 

二、更新规划器统计：

    PostgreSQL查询规划器在选择最优路径时，需要参照相关数据表的统计信息用以为查询生成最合理的规划。这些统计是通过ANALYZE命令获得的，你可以直接调用该命令，或者把它当做VACUUM命令里的一个可选步骤来调用，如VACUUM ANAYLYZE table_name，该命令将会先执行VACUUM再执行ANALYZE。与回收空间(VACUUM)一样，对数据更新频繁的表保持一定频度的ANALYZE，从而使该表的统计信息始终处于相对较新的状态，这样对于基于该表的查询优化将是极为有利的。然而对于更新并不频繁的数据表，则不需要执行该操作。
    我们可以为特定的表，甚至是表中特定的字段运行ANALYZE命令，这样我们就可以根据实际情况，只对更新比较频繁的部分信息执行ANALYZE操作，这样不仅可以节省统计信息所占用的空间，也可以提高本次ANALYZE操作的执行效率。这里需要额外说明的是，ANALYZE是一项相当快的操作，即使是在数据量较大的表上也是如此，因为它使用了统计学上的随机采样的方法进行行采样，而不是把每一行数据都读取进来并进行分析。因此，可以考虑定期对整个数据库执行该命令。
    事实上，我们甚至可以通过下面的命令来调整指定字段的抽样率，如：
    ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
    注意：该值的取值范围是0--1000，其中值越低采样比例就越低，分析结果的准确性也就越低，但是ANALYZE命令执行的速度却更快。如果将该值设置为-1，那么该字段的采样比率将恢复到系统当前默认的采样值，我们可以通过下面的命令获取当前系统的缺省采样值。
    postgres=# show default_statistics_target;
     default_statistics_target
    ---------------------------
     100
    (1 row)
    从上面的结果可以看出，该数据库的缺省采样值为100(10%)。

三、VACUUM和ANALYZE的示例：
    

    #1. 创建测试数据表。
    postgres=# CREATE TABLE testtable (i integer);
    CREATE TABLE
    #2. 为测试表创建索引。
    postgres=# CREATE INDEX testtable_idx ON testtable(i);
    CREATE INDEX
    #3. 创建批量插入测试数据的函数。
    postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$
    DECLARE
        min integer;
        max integer;
    BEGIN
        SELECT COUNT(*) INTO min from testtable;
        max := min + 10000;
        FOR i IN min..max LOOP
            INSERT INTO testtable VALUES(i);
        END LOOP;
        RETURN 0;
    END;
    $$ LANGUAGE plpgsql;
    CREATE FUNCTION
    #4. 批量插入数据到测试表(执行四次)
    postgres=# SELECT test_insert();
     test_insert
    -------------
               0
    (1 row)
    #5. 确认四次批量插入都成功。
    postgres=# SELECT COUNT(*) FROM testtable;
     count
    -------
     40004
    (1 row)
    #6. 分析测试表，以便有关该表的统计信息被更新到PostgreSQL的系统表。
    postgres=# ANALYZE testtable;
    ANALYZE
    #7. 查看测试表和索引当前占用的页面数量(通常每个页面为8k)。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname       | relfilenode    | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |       90
    #8. 批量删除数据。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 30003
    #9. 执行vacuum和analyze，以便更新系统表，同时为该表和索引记录高水标记。
    #10. 这里需要额外说明的是，上面删除的数据均位于数据表的前部，如果删除的是末尾部分，
    #      如where i > 10000，那么在执行VACUUM ANALYZE的时候，数据表将会被物理的缩小。
    postgres=# VACUUM ANALYZE testtable;
    ANALYZE
    #11. 查看测试表和索引在删除后，再通过VACUUM ANALYZE更新系统统计信息后的结果(保持不变)。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode     | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |       90
    (2 rows)
    #12. 再重新批量插入两次，之后在分析该表以更新其统计信息。
    postgres=# SELECT test_insert(); --执行两次。
     test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #13. 此时可以看到数据表中的页面数量仍然为之前的高水标记数量，索引页面数量的增加
    #      是和其内部实现方式有关，但是在后面的插入中，索引所占的页面数量就不会继续增加。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname       | relfilenode    | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |      173
    (2 rows)
    postgres=# SELECT test_insert();
     test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #14. 可以看到索引的页面数量确实没有继续增加。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode    | relpages
    ---------------+-------------+----------
     testtable        |       17601   |      157
     testtable_idx  |       17604   |      173
    (2 rows)
    #15. 重新批量删除数据。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 19996
    #16. 从后面的查询可以看出，在执行VACUUM FULL命令之后，测试表和索引所占用的页面数量
    #      确实降低了，说明它们占用的物理空间已经缩小了。
    postgres=# VACUUM FULL testtable;
    VACUUM
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode     | relpages
    ---------------+-------------+----------
     testtable        |       17602   |      118
     testtable_idx  |       17605   |       68
    (2 rows)

四、定期重建索引：

    在PostgreSQL中，为数据更新频繁的数据表定期重建索引(REINDEX INDEX)是非常有必要的。对于B-Tree索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。
    对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。见如下示例：
    #1. 此时已经在该表中插入了大约6万条数据，下面的SQL语句将查询该索引所占用的磁盘空间。    
    postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';
        relname     | size
    ----------------+------
     testtable_idx | 1240K
    (1 row)
    #2. 删除数据表中大多数的数据。
    postgres=# DELETE FROM testtable WHERE i > 20000;
    DELETE 50006
    #3. 分析一个该表，以便于后面的SQL语句继续查看该索引占用的空间。
    postgres=# ANALYZE testtable;
    ANALYZE
    #4. 从该查询结果可以看出，该索引所占用的空间并未减少，而是和之前的完全一样。
    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
     size
    ------
     1240K
    (1 row)
    #5. 重建索引。
    postgres=# REINDEX INDEX testtable_idx;
    REINDEX
    #6. 查看重建后的索引实际占用的空间，从结果中可以看出索引的尺寸已经减少。
    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
     size
    ------
     368K
    (1 row)
    #7. 最后一点需要记住的是，在索引重建后一定要分析数据表。
    postgres=# ANALYZE testtable;
    ANALYZE

 五、观察磁盘使用情况：

    1. 查看数据表所占用的磁盘页面数量。
    #relpages只能被VACUUM、ANALYZE和几个DDL命令更新，如CREATE INDEX。通常一个页面的长度为8K字节。
    postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';
     relfilenode | relpages
    -------------+----------
           16412 |       79
    (1 row)
    
    2. 查看指定数据表的索引名称和索引占用的磁盘页面数量。
    postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i
        WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
        ORDER BY c2.relname;
        relname    | relpages
    ---------------+----------
     testtable_idx |       46
    (1 row)