最近有个项目使用的是postgres数据库,需要用到分表。因为对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
);
这样建表,才能使向分表插入记录时一同插入主表。