bigint & bigserial

--最近在给公司开发部门培训一些pg的知识，现总结如下：
--在pg中整型与整型相除，会直接舍弃小数部分
postgres=# select 8/3;  
 ?column? 
----------
        2
--此时要声明除数为非整型，则不会舍弃小数部分
postgres=# select 8.0/3;
      ?column?      
--------------------
 2.6666666666666667
 
 postgres=# select 8::real/3;
     ?column?     
------------------
 2.66666666666667

-- 自增序列
 create table t (name varchar(20),password varchar(20));
 insert into t values('abc','1234354'),('123123','asdfasdf');
 select * from t;
 alter table t add column id serial;

postgres=# \d+ t
                                                     Table "public.t"
  Column  |         Type          |                   Modifiers                    | Storage  | Stats target | Description 
----------+-----------------------+------------------------------------------------+----------+--------------+-------------
 name     | character varying(20) |                                                | extended |              | 
 password | character varying(20) |                                                | extended |              | 
 id       | integer               | not null default nextval('t_id_seq'::regclass) | plain    |              | 
Has OIDs: no


-- 间隔序列
select generate_series(1,100);

 --间隔日期
 select generate_series('2015-1-1'::timestamp,'2015-1-4'::timestamp,interval '1 day');
 SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,'2008-03-04 12:00'::timestamp, '10 hours');
 select m,'rudy'||m, now()+ (m||' day')::interval from generate_series(1,100) m;
  



  --创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

--测试
CREATE TABLE person ( name text,current_mood mood);
INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
INSERT INTO person VALUES ('rudy', 'happy');

--注意枚举类型区分大小写
INSERT INTO person VALUES ('rudy', 'HAPPY');


--注意枚举类型的排序其为定义时的顺序
mydb=# select * from person order by current_mood;
 name  | current_mood 
-------+--------------
 Larry | sad
 Curly | ok
 rudy  | happy
 
 --执行显示的类型转换才能进行字符类型进行排序
 select * from person order by current_mood::char;
 
 
 --添加新的枚举值
 alter type mood add value 'engry';
 
 
--检举类型的修改相当于修改数据字典，不会对相应的引用造成阻塞
INSERT INTO person select 'rudy01','happy' from generate_series(1,10000000);
alter type mood add value 'engryeds';

--枚举类型的定义在如下表中
select * from pg_enum;