Thread

1. Adding missing FROM-clause entry in subquery

   Berend Tober <btober@seaworthysys.com> — 2003-10-02T12:58:30Z

   I run the following script to export some data from my development
   database and then update or insert the records into to the quality
   assurance testing database, but I get a warning notice that I don't
   understand. Aside from that notice, the script appears to work as
   intended, i.e., updating existing report definitions and adding any new
   ones defined in the dev environment but not appearing in QAT.
   
   Here is the script:
   
   \set ON_ERROR_STOP ON
   
   COPY report TO '/home/postgres/report.sql';
   
   \c paidqat
   CREATE TEMPORARY TABLE temptable AS SELECT * FROM report;
   
   DELETE FROM temptable;
   
   COPY temptable FROM '/home/postgres/report.sql';
   
   UPDATE report SET resource = (
     SELECT resource FROM temptable s1
     WHERE s1.title = report.title)
     WHERE EXISTS(SELECT 1 FROM temptable s1
     WHERE s1.title = report.title);
   
   INSERT INTO report (
     SELECT * FROM temptable s1 EXCEPT
     SELECT * FROM report s2 WHERE s2.title = report.title);
   
   
   The warning notice refers to the last command in the script, i.e., the
   INSERT, but inserts do occur successfully as a result.
   
   Here is the run and the resulting notice message:
   
   bash-2.05a$ psql -f urep.sql -U paid paiddev
   COPY
   You are now connected to database paidqat.
   SELECT
   DELETE 6
   COPY
   UPDATE 6
   psql:urep.sql:20: NOTICE:  Adding missing FROM-clause entry in subquery
   for table "report"
   INSERT 0 1
   bash-2.05a$
   
   ~Berend Tober
   
   
   
   
   

2. Re: Adding missing FROM-clause entry in subquery

   Robert Treat <xzilla@users.sourceforge.net> — 2003-10-02T16:41:03Z

   On Thu, 2003-10-02 at 08:58, btober@seaworthysys.com wrote:
   > 
   > INSERT INTO report (
   >   SELECT * FROM temptable s1 EXCEPT
   >   SELECT * FROM report s2 WHERE s2.title = report.title);
   > 
   
   i think you're essentially doing:
   
   INSERT INTO report (
   	SELECT * FROM temptable s1 EXCEPT
   	SELECT * FROM report s2, report WHERE s2.title = report.title);
   
   which i don't think is what you expected to happen*, but seems to be
   pretty much equivalent in this case. 
   
   
   
   *given that missing-from behaves differently with inserts and updates, i
   can see how this is pretty confusing... in 7.4 theres an option to turn
   this off, though you'd need to think of another way to do what your
   doing above since it would break
   
   
   Robert Treat
   -- 
   Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
   
   
   

3. Re: Adding missing FROM-clause entry in subquery

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-10-02T17:16:55Z

   On 2 Oct 2003, Robert Treat wrote:
   
   > On Thu, 2003-10-02 at 08:58, btober@seaworthysys.com wrote:
   > > 
   > > INSERT INTO report (
   > >   SELECT * FROM temptable s1 EXCEPT
   > >   SELECT * FROM report s2 WHERE s2.title = report.title);
   > > 
   > 
   > i think you're essentially doing:
   > 
   > INSERT INTO report (
   > 	SELECT * FROM temptable s1 EXCEPT
   > 	SELECT * FROM report s2, report WHERE s2.title = report.title);
   > 
   > which i don't think is what you expected to happen*, but seems to be
   > pretty much equivalent in this case. 
   > 
   > 
   > 
   > *given that missing-from behaves differently with inserts and updates, i
   > can see how this is pretty confusing...
   
   But in the update case it's not an error because of the extension that allows
   the where clause of an update to refer to other tables (I think). Whereas in
   the insert case there is no cross over between the table named for insert and
   the select cluase generating the data to inserted, it is a complete select
   clause in it's own right. Indeed, having written that it's occured to me the
   reason behind this specific example, it's to avoid the insert of duplicate key
   error. The select in the insert statement should stand as a query in it's own
   right but if it didn't all those people looking to avoid transaction abort when
   wanting an update or insert if not there already type operation would have
   their work around.
   
   > in 7.4 theres an option to turn
   > this off, though you'd need to think of another way to do what your
   > doing above since it would break
   
   -- 
   Nigel J. Andrews