Thread: Adding missing FROM-clause entry in subquery

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

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

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