missing from clause error- complex query

Maptech-Inc. · #1 (**permalink**) 01-09-12, 12:48

The following is a query that ran fine on a previous version of postgre but since updating postgre (server crash) the query will not execute properly. I am running version 8.4.9 of postgre. Any help would be greatly appreciated. Thanks in advance.

Code:

ERROR:  missing FROM-clause entry for table "pwatparm4"

update monuzsn set JAN = pwatparm4.uzsn * hspfmonthly.JAN,
FEB = pwatparm4.uzsn * hspfmonthly.FEB, 
MAR = pwatparm4.uzsn * hspfmonthly.MAR, 
APR = pwatparm4.uzsn * hspfmonthly.APR, 
MAY = pwatparm4.uzsn * hspfmonthly.MAY, 
JUN = pwatparm4.uzsn * hspfmonthly.JUN, 
JUL = pwatparm4.uzsn * hspfmonthly.JUL,
AUG = pwatparm4.uzsn * hspfmonthly.AUG, 
SEP = pwatparm4.uzsn * hspfmonthly.SEP, 
OCT = pwatparm4.uzsn * hspfmonthly.OCT, 
NOV = pwatparm4.uzsn * hspfmonthly.NOV, 
DEC = pwatparm4.uzsn * hspfmonthly.DEC 
where hspfmonthly.projectid = 42 and 
hspfmonthly.landuseid = landuses.landuseid and 
pwatparm4.segstart = monuzsn.segstart and 
monuzsn.segstart = subluparams.sublu and 
subluparams.luname = map_hspf_lu.hspf_lu and 
map_hspf_lu.project_luid = landuses.luid and 
hspfmonthly.paramname = 'uzsn' and 
subluparams.paramtype = 'parameter' and 
subluparams.projectid = 42

CREATE TABLE hspfmonthly
(
  distroid serial NOT NULL,
  distrodomain character varying(32),
  paramname character varying(32),
  subshedid integer,
  projectid integer,
  landuseid integer,
  distroname character varying(256),
  jan double precision,
  feb double precision,
  mar double precision,
  apr double precision,
  may double precision,
  jun double precision,
  jul double precision,
  aug double precision,
  sep double precision,
  oct double precision,
  nov double precision,
  "dec" double precision
)
WITH (
  OIDS=TRUE
);

CREATE TABLE monuzsn
(
  segstart integer,
  segend integer,
  jan double precision,
  feb double precision,
  mar double precision,
  apr double precision,
  may double precision,
  jun double precision,
  jul double precision,
  aug double precision,
  sep double precision,
  oct double precision,
  nov double precision,
  "dec" double precision
)
WITH (
  OIDS=TRUE
);

CREATE TABLE pwatparm4
(
  segstart integer,
  segend integer,
  cepsc double precision,
  uzsn double precision,
  nsur double precision,
  intfw double precision,
  irc double precision,
  lzetp double precision
)
WITH (
  OIDS=TRUE
);

CREATE TABLE landuses
(
  luid serial NOT NULL,
  landuseid integer,
  projectid integer,
  landusetype integer,
  landuse character varying(128),
  pct_impervious double precision DEFAULT 0.0,
  lzetp double precision,
  cepsc double precision,
  wsqop double precision,
  ioqc double precision,
  aoqc double precision,
  sqolim double precision
)
WITH (
  OIDS=TRUE
);

CREATE TABLE subluparams
(
  projectid integer,
  sublu integer,
  luname character varying(64),
  luarea double precision,
  forest double precision DEFAULT 1.0,
  lzsn double precision DEFAULT 7.0,
  infilt double precision DEFAULT 0.1,
  lsur double precision DEFAULT 200.0,
  slsur double precision DEFAULT 0.03,
  kvary double precision DEFAULT 0.0,
  agwrc double precision DEFAULT 0.98,
  petmax double precision DEFAULT 40.0,
  petmin double precision DEFAULT 35.0,
  infexp double precision DEFAULT 2.0,
  infild double precision DEFAULT 2.0,
  deepfr double precision DEFAULT 0.01,
  basetp double precision DEFAULT 0.01,
  agwetp double precision DEFAULT 0.0,
  cepsc double precision,
  uzsn double precision DEFAULT 0.5,
  nsur double precision DEFAULT 0.1,
  intfw double precision DEFAULT 1.00,
  irc double precision DEFAULT 0.5,
  lzetp double precision,
  ceps double precision DEFAULT 0.01,
  surs double precision DEFAULT 0.01,
  uzs double precision DEFAULT 0.3,
  ifws double precision DEFAULT 0.01,
  lzs double precision DEFAULT 1.5,
  agws double precision DEFAULT 0.01,
  gwvs double precision DEFAULT 0.01,
  subshedid bigint,
  paramtype character varying(12) DEFAULT 'parameter'::character varying,
  ripbuffer boolean DEFAULT false,
  sqo double precision DEFAULT 1.0,
  sqolim double precision DEFAULT 1.0,
  retsc double precision DEFAULT 0.1
)
WITH (
  OIDS=TRUE
);

CREATE TABLE map_hspf_lu
(
  mapid serial NOT NULL,
  projectid integer,
  project_luid integer,
  hspf_lu character varying(128)
)
WITH (
  OIDS=TRUE
);

shammat · #2 (**permalink**) 01-09-12, 13:29

Well, there is no FROM clause in your UPDATE statement. I don't know which version you used before but I don't believe this worked in any recent (=up-to-date) version of Postgres without listing all tables that you want to join in the FROM clause.

Btw: it's PostgreSQL, Postgres or simply pg. Never postgre

Maptech-Inc. · #3 (**permalink**) 01-09-12, 15:47

Thanks! That fixed the problem! I sort of inherited this project after one of our servers crashed, apparently the software was written more than 7 years ago and neither the database nor PHP has been updated since then. So, as you can imagine, I've ran into several compatibility issues. I had no prior pg experience so this has been quite fun for me. I have included the working query in case someone happens upon this thread with the same problem I had.

Code:

update monuzsn set JAN = pwatparm4.uzsn * hspfmonthly.JAN,
FEB = pwatparm4.uzsn * hspfmonthly.FEB, 
MAR = pwatparm4.uzsn * hspfmonthly.MAR, 
APR = pwatparm4.uzsn * hspfmonthly.APR, 
MAY = pwatparm4.uzsn * hspfmonthly.MAY, 
JUN = pwatparm4.uzsn * hspfmonthly.JUN, 
JUL = pwatparm4.uzsn * hspfmonthly.JUL,
AUG = pwatparm4.uzsn * hspfmonthly.AUG, 
SEP = pwatparm4.uzsn * hspfmonthly.SEP, 
OCT = pwatparm4.uzsn * hspfmonthly.OCT, 
NOV = pwatparm4.uzsn * hspfmonthly.NOV, 
DEC = pwatparm4.uzsn * hspfmonthly.DEC 
from hspfmonthly, landuses, pwatparm4, map_hspf_lu, subluparams
where hspfmonthly.projectid = 42 and 
hspfmonthly.landuseid = landuses.landuseid and 
pwatparm4.segstart = monuzsn.segstart and 
monuzsn.segstart = subluparams.sublu and 
subluparams.luname = map_hspf_lu.hspf_lu and 
map_hspf_lu.project_luid = landuses.luid and 
hspfmonthly.paramname = 'uzsn' and 
subluparams.paramtype = 'parameter' and 
subluparams.projectid = 42

Sorry for my ignorance when it comes to a correct abbreviation for PostgreSQL.

shammat · #4 (**permalink**) 01-09-12, 16:00

Quote:

Originally Posted by Maptech-Inc.

Thanks! That fixed the problem! I sort of inherited this project after one of our servers crashed, apparently the software was written more than 7 years ago and neither the database nor PHP has been updated since then. So, as you can imagine, I've ran into several compatibility issues.

I guess in your case it was the change of defaults for the "add_missing_from" parameter:

PostgreSQL: Documentation: Manuals: Release 8.1
PostgreSQL: Documentation: Manuals: Version and Platform Compatibility

A lot of stuff was changed since then.

Btw: why did you use 8.4? 9.1 is the current version and I would recommend to use at least 9.0

Quote:

Sorry for my ignorance when it comes to a correct abbreviation for PostgreSQL.

No problem

Maptech-Inc. · #5 (**permalink**) 01-09-12, 16:41

Well, this has sort of been a side project that I've been working on sparsely since August or September, correcting problems as they arose. So I guess that was probably the newest version when I first installed pg. I'm guessing an update to 9.1 from 8.4 wouldn't cause any further compatibility issues, would it? If not, I'll update. Thanks again for your help.

shammat · #6 (**permalink**) 01-09-12, 17:14

Quote:

Originally Posted by Maptech-Inc.

Well, this has sort of been a side project that I've been working on sparsely since August or September, correcting problems as they arose. So I guess that was probably the newest version when I first installed pg. I'm guessing an update to 9.1 from 8.4 wouldn't cause any further compatibility issues, would it?

Well there are some things that might affect you, you need to go through the release notes.

http://www.postgresql.org/docs/curre...html#AEN108973
http://www.postgresql.org/docs/curre...html#AEN106814

You might be affected by the change in standard_conforming_strings (but it would be easy to change the value back to the old default - although I would not recommend it)

The new bytea output format might also affect you if you are using bytea columns.

Maybe the stricture rules for parameter names vs. column names in PL/pgSQL functions (see: http://www.postgresql.org/docs/curre...html#AEN109055)

Internet Services

Web Development

Digital Marketing

Consumer Tech