db.sql

create table product(
	id char(5) not null primary key,
	name varchar(20) not null,
	price int not null,
	images varchar(100)[],
	specs JSON null
);

/** 枚举类型的ordertype **/
CREATE TYPE ORDERTYPE AS ENUM ('RETAIL', 'WHOLESALE');

create table order_main(
    id serial primary key,
    order_date timestamp not null,
    consignee varchar(20) not null,
    phone varchar(20) not null,
    province varchar(50) null,
    city varchar(50) null,
    district varchar(50) null,
    address varchar(50) null,
    order_type ORDERTYPE,
    status int default 0
);

create table order_detail(
    id serial primary key,
    order_id int not null,
    product_id varchar(10) not null,
    product_name varchar(100) not null,
    num int not null,
    price int not null
);

insert into product(id, name, price, specs, images) values('E0001', 'iPhone X手机模型', 10000, '{"颜色":"黑色", "材质":"塑料"}', '{"a1.jpg", "a2.jpg", "a3.jpg"}');
insert into product(id, name, price, specs, images) values('B0002', '双肩电脑包', 25000, '{"产地":"中国", "电脑夹层":"有"}', null);

insert into order_main(order_date, consignee, phone, province, city, district, address, order_type, status)
    values('2017/4/28 12:13:14', '郝尤乾', '13300001111', '上海', '上海', '徐汇', '漕宝路22222号', 'RETAIL', 0);
insert into order_detail(order_id, product_id, product_name, num, price)
    values(1, 'E0001', 'iPhone X手机模型', 1, 10000);
insert into order_detail(order_id, product_id, product_name, num, price)
    values(1, 'B0002', '双肩电脑包', 1, 25000);