CREATE TABLE {schema}.obis_detailcache
(
  resource_id bigint,
  taxon_id bigint,
  scientificname character varying(200),
  scientificnameauthor character varying(200),
  datelastmodified character varying(200),
  recordlastcached character varying(200),
  basisofrecord character varying(256),
  institutioncode character varying(200),
  collectioncode character varying(200),
  catalognumber character varying(200),
  collector character varying(200),
  yearcollected character varying(8),
  startyearcollected character varying(8),
  endyearcollected character varying(8),
  monthcollected character varying(4),
  startmonthcollected character varying(4),
  endmonthcollected character varying(4),
  daycollected character varying(4),
  startdaycollected character varying(4),
  enddaycollected character varying(4),
  starttimecollected timestamp without time zone,
  endtimecollected timestamp without time zone,
  latitude double precision,
  longitude double precision,
  coordinateprecision double precision,
  slatitude double precision,
  elatitude double precision,
  slongitude double precision,
  elongitude double precision,
  seprecision double precision,
  minimumdepth double precision,
  maximumdepth double precision,
  depthrange character varying(256),
  sourceofrecord text,
  citation text,
  datecollected timestamp without time zone,
  recordurl character varying(800),
  csquare text,
  depth double precision,
  depthprecision double precision,
  locality text,
  ocean character varying(100),
  country character varying(200),
  state character varying(200),
  county character varying(200),
  lifestage character varying(200),
  identifiedby character(400),
  yearidentified character varying(10),
  monthidentified character varying(8),
  dayidentified character varying(8),
  typestatus character varying(100),
  collectornumber character varying(100),
  fieldnumber character varying(100),
  julianday integer,
  startjulianday integer,
  endjulianday integer,
  timeofday double precision,
  starttimeofday double precision,
  endtimeofday double precision,
  timezone character varying(100),
  temperature double precision,
  sex character varying(50),
  preparationtype character varying(100),
  individualcount double precision,
  observedindividualcount double precision,
  observedweight double precision,
  samplesize character varying(50),
  previouscatalognumber character varying(100),
  relationshiptype character varying(100),
  relatedcatalogitem character varying(100),
  the_geom geometry,
  notes text,
  cleanlat real,
  cleanlon real,
  cleanname character varying(255),
  CONSTRAINT unq_detcache UNIQUE (institutioncode, collectioncode, catalognumber)
)
WITH (
  OIDS=FALSE
);

-- Index: {schema}.idx_dtche_bor

-- DROP INDEX {schema}.idx_dtche_bor;

CREATE INDEX idx_dtche_bor
  ON {schema}.obis_detailcache
  USING btree
  (basisofrecord);

-- Index: {schema}.idx_dtche_catnum

-- DROP INDEX {schema}.idx_dtche_catnum;

CREATE INDEX idx_dtche_catnum
  ON {schema}.obis_detailcache
  USING btree
  (catalognumber);

-- Index: {schema}.idx_dtche_collcode

-- DROP INDEX {schema}.idx_dtche_collcode;

CREATE INDEX idx_dtche_collcode
  ON {schema}.obis_detailcache
  USING btree
  (collectioncode);

-- Index: {schema}.idx_dtche_rci

-- DROP INDEX {schema}.idx_dtche_rci;

CREATE INDEX idx_dtche_rci
  ON {schema}.obis_detailcache
  USING btree
  (relatedcatalogitem);

-- Index: {schema}.idx_dtche_reltype

-- DROP INDEX {schema}.idx_dtche_reltype;

CREATE INDEX idx_dtche_reltype
  ON {schema}.obis_detailcache
  USING btree
  (relationshiptype);

-- Index: {schema}.idx_dtche_resource_id

-- DROP INDEX {schema}.idx_dtche_resource_id;

CREATE INDEX idx_dtche_resource_id
  ON {schema}.obis_detailcache
  USING btree
  (resource_id);
