Part 1 : CREATE TABLE DECLARE VARIABLE & Alter table IN POSTGRES

CREATE TABLE public.aic_user_mapping

(

  id integer NOT NULL DEFAULT nextval('aic_user_mapping_id_seq'::regclass),

  user_id integer NOT NULL,

  state character varying(100),

  district character varying(100),

  active integer DEFAULT 1,

  start_date timestamp without time zone,

  end_date timestamp without time zone,

  last_updated_time timestamp without time zone DEFAULT now(),

  CONSTRAINT aic_user_mapping_pkey PRIMARY KEY (id),

  CONSTRAINT aic_user_mapping_ukey UNIQUE (user_id, state, district)

)

WITH (

  OIDS=FALSE

);

ALTER TABLE public.aic_user_mapping
  OWNER TO postgres;

CREATE TABLE public."CCE_Admin"
(
  "Username" character varying,
  "State" character varying,
  "District" character varying,
  "Crop" character varying,
  "S.no" integer NOT NULL DEFAULT 1
)

CREATE TABLE public.aqminstallation
(
  gid integer NOT NULL DEFAULT nextval('aqminstallation_gid_seq'::regclass),
  serverdate timestamp without time zone DEFAULT now(),
  odk_source character varying(20),
  user_id character varying(20),
  surveyor_name character varying(100),
  surveyor_mobile character varying(20),
  surveydate character varying(100),
  uniqueid character varying(100),
  starval character varying(100),
  state character varying(100),
  city character varying(100),
  area character varying(100),
  location character varying(100),
  landmark character varying(100),
  hostname character varying(100),
  hostmobile character varying(100),
  hostaddress character varying(100),
  pincode character varying(100),
  gps character varying(500),
  installationdate character varying(100),
  sitebarcode character varying(100),
  dataloggermake character varying(100),
  dataloggermodel character varying(100),
  dataloggerimei character varying(100),
  firmwareversion character varying(100),
  solarpanelmake character varying(100),
  simcardno character varying(100),
  simcardprovider character varying(100),
  aqmsetphoto character varying(500),
  installationformphoto character varying(500),
  sitesurveyformphoto character varying(500),
  northdirphoto character varying(500),
  southdirphoto character varying(500),
  eastdirphoto character varying(500),
  westdirphoto character varying(500),
  passbookphoto character varying(500),
  idcardphoto character varying(500),
  q1heightrooftop character varying(100),
  q2heightbuilding character varying(100),
  q3opendirection character varying(100),
  q4obstacle character varying(100),
  q5obstacledetails character varying(100),
  q6obstacledistance character varying(100),
  q7tempsensor character varying(100),
  q8windsensor character varying(100),
  q9solarpanel character varying(100),
  q10datalogger character varying(100),
  q11solarpanelvoltage character varying(100),
  q12batteryvoltage character varying(100),
  q13logcard character varying(100),
  q14hostagreement character varying(100),
  comment character varying(500),
  instanceid character varying(100),
  imei character varying(100),
  sim character varying(100),
  phoneno character varying(100),
  start1 character varying(100),
  end1 character varying(100),
  state_gis character varying(100),
  district_gis character varying(100),
  tehsil_gis character varying(100),
  geom geometry,
  is_validated integer NOT NULL DEFAULT 0,
  validated_by character varying(10),
  validated_on timestamp without time zone,
  qccomment text,
  CONSTRAINT aqminstallation_pkey PRIMARY KEY (gid)
)

IMAGE DATA :

-- Table: public.aqmsitesurvey_rawdata

-- DROP TABLE public.aqmsitesurvey_rawdata;

CREATE TABLE public.aqmsitesurvey_rawdata
(
  gid integer NOT NULL DEFAULT nextval('aqmsitesurvey_rawdata_gid_seq'::regclass),
  serverdate timestamp without time zone DEFAULT now(),
  source character varying(10) DEFAULT 'xml'::character varying,
  xmlfilepath text,
  xmlfilename text,
  filedate timestamp without time zone,
  sync_flag integer,
  odk_source character varying(20),
  user_id character varying(20),
  surveyor_name character varying(100),
  surveyor_mobile character varying(20),
  surveydate character varying(100),
  uniqueid character varying(100),
  starval character varying(100),
  state character varying(100),
  city character varying(100),
  location character varying(100),
  landmark character varying(100),
  hostname character varying(100),
  hostmobile character varying(100),
  hostaddress character varying(100),
  gps character varying(500),
  q1site character varying(100),
  q2source character varying(100),
  q3distance character varying(100),
  q4opendir character varying(100),
  q5obstacles character varying(100),
  q12network character varying(100),
  qplatform text,
  q6poleheight character varying(100),
  q7poledistance character varying(100),
  q8buildheight character varying(100),
  q9roofheight character varying(100),
  comment character varying(500),
  image0 character varying(500),
  image1 character varying(500),
  image2 character varying(500),
  image3 character varying(500),
  image4 character varying(500),
  altitude character varying(100),
  instanceid character varying(100),
  imei character varying(100),
  sim character varying(100),
  phoneno character varying(100),
  start1 character varying(100),
  end1 character varying(100),
  CONSTRAINT aqmsitesurvey_rawdata_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.aqmsitesurvey_rawdata
  OWNER TO postgres;
 

CREATE TABLE public.arms_crops
(
  id integer NOT NULL DEFAULT nextval('arms_crops_id_seq'::regclass),
  state character varying(100),
  district character varying(100),
  crop character varying(100),
  state_key character varying(100),
  district_key character varying(100),
  year integer,
  season character varying(10),
  client character varying(100),
  CONSTRAINT id_arms_crops_pk PRIMARY KEY (id),
  CONSTRAINT arms_crops_unique_key UNIQUE (state, district, crop, year, season)
)

CREATE TABLE public.awsmisform
(
  gid integer NOT NULL DEFAULT nextval('awsmisform_gid_seq'::regclass),
  serverdate timestamp without time zone DEFAULT now(),
  dltype character varying(100),
  atrh character varying(100),
  raingauge character varying(100),
  eqp_chk_solarpanelvoltage character varying(100),
  simrequirement character varying(100),
  comments character varying(500),
  instanceid character varying(100),
  imei character varying(100),
  tehsil_gis character varying(100),
  geom geometry,
  is_validated integer NOT NULL DEFAULT 0,
  validated_by character varying(10),
  validated_on timestamp without time zone,
  qccomment text,
  CONSTRAINT awsmisform_pkey PRIMARY KEY (gid)
)

CREATE TABLE public.cce_experimental_yield_range
(
  id integer NOT NULL DEFAULT nextval('cce_experimental_yield_range_id_seq'::regclass),
  crop_id integer,
  state character varying(100),
  minval numeric,
  maxval numeric,
  CONSTRAINT cce_experimental_yield_rangepkey PRIMARY KEY (id)
)

CREATE TABLE public.farmerdiary_soil_report
(
  skygreen_id integer,
  farmername character varying(255),
  ph double precision,
  potassium double precision,
  manganese double precision,
  report_date date
)