begin;

set search_path to public;

create table if not exists cnarea (
  id integer generated by default as identity primary key,
  level smallint not null check (level >= 0),
  parent_code varchar(16) not null default '0',
  area_code varchar(16) not null default '0',
  zip_code varchar(6) not null default '000000',
  city_code char(6) not null default '',
  name varchar(50) not null default '',
  short_name varchar(50) not null default '',
  merger_name varchar(50) not null default '',
  pinyin varchar(30) not null default '',
  lng numeric(10, 6) not null default 0.000000,
  lat numeric(10, 6) not null default 0.000000,
  short_code varchar(8)
);

create unique index if not exists uk_cnarea_code on cnarea(area_code);
create index if not exists idx_cnarea_parent_code on cnarea(parent_code);

comment on table cnarea is '中国行政地区表';
comment on column cnarea.id is '主键';
comment on column cnarea.level is '层级';
comment on column cnarea.parent_code is '父级行政代码';
comment on column cnarea.area_code is '行政代码';
comment on column cnarea.zip_code is '邮政编码';
comment on column cnarea.city_code is '区号';
comment on column cnarea.name is '名称';
comment on column cnarea.short_name is '简称';
comment on column cnarea.merger_name is '组合名';
comment on column cnarea.pinyin is '拼音';
comment on column cnarea.lng is '经度';
comment on column cnarea.lat is '纬度';
comment on column cnarea.short_code is '简短code';

commit;