Обсуждение: Problem with insert

The query:

INSERT INTO table_resultat( origine, service, noeud, rubrique,
nb_passage, temps, date) SELECT DISTINCT temp2.origine, temp2.service, temp2.noeud,
temp2.rubrique, temp2.nb_passage, temp2.temps, temp2.date FROM temp2
WHERE not exists     ( SELECT table_resultat.origine, table_resultat.service,
table_resultat.noeud, table_resultat.rubrique, table_resultat.date FROM
table_brut WHERE table_resultat.origine=temp2.origine AND
table_resultat.service=temp2.service AND
table_resultat.noeud=temp2.noeud AND
table_resultat.rubrique=temp2.rubrique AND
table_resultat.date=temp2.date )


produces the error :
ERROR:  replace_vars_with_subplan_refs: variable not in subplan target
list


anyone can explain me ?

Thanks. Jerome.

Jerome Raupach <jraupach@intelcom.fr> writes:
> The query:
> INSERT INTO table_resultat( origine, service, noeud, rubrique,
> nb_passage, temps, date) 
>     SELECT DISTINCT temp2.origine, temp2.service, temp2.noeud,
> temp2.rubrique, temp2.nb_passage, temp2.temps, temp2.date FROM temp2
> WHERE not exists 
>         ( SELECT table_resultat.origine, table_resultat.service,
> table_resultat.noeud, table_resultat.rubrique, table_resultat.date FROM
> table_brut WHERE table_resultat.origine=temp2.origine AND
> table_resultat.service=temp2.service AND
> table_resultat.noeud=temp2.noeud AND
> table_resultat.rubrique=temp2.rubrique AND
> table_resultat.date=temp2.date )

> produces the error :
> ERROR:  replace_vars_with_subplan_refs: variable not in subplan target
> list

That's pretty interesting.  I was not able to reproduce this failure
using stripped-down table definitions --- I tried

create table foo (f1 int);
create table bar (f1 int);
create table baz (f1 int);

insert into foo(f1)select distinct f1 from barwhere not exists (select foo.f1 from baz wherefoo.f1 = bar.f1);

So I think there must be some special feature of your tables that you
haven't shown us.  Could we see a schema dump (pg_dump -s) for these
tables?

BTW the inner select seems pretty weird --- what is the point of joining
against table_brut when you're not using it?  But that doesn't look like
it could provoke this error.
        regards, tom lane

Tom Lane wrote:
> 
> Jerome Raupach <jraupach@intelcom.fr> writes:
> > The query:
> > INSERT INTO table_resultat( origine, service, noeud, rubrique,
> > nb_passage, temps, date)
> >       SELECT DISTINCT temp2.origine, temp2.service, temp2.noeud,
> > temp2.rubrique, temp2.nb_passage, temp2.temps, temp2.date FROM temp2
> > WHERE not exists
> >               ( SELECT table_resultat.origine, table_resultat.service,
> > table_resultat.noeud, table_resultat.rubrique, table_resultat.date FROM
> > table_brut WHERE table_resultat.origine=temp2.origine AND
> > table_resultat.service=temp2.service AND
> > table_resultat.noeud=temp2.noeud AND
> > table_resultat.rubrique=temp2.rubrique AND
> > table_resultat.date=temp2.date )
> 
> > produces the error :
> > ERROR:  replace_vars_with_subplan_refs: variable not in subplan target
> > list
> 
> That's pretty interesting.  I was not able to reproduce this failure
> using stripped-down table definitions --- I tried
> 
> create table foo (f1 int);
> create table bar (f1 int);
> create table baz (f1 int);
> 
> insert into foo(f1)
>  select distinct f1 from bar
>  where not exists (select foo.f1 from baz where
>  foo.f1 = bar.f1);
> 
> So I think there must be some special feature of your tables that you
> haven't shown us.  Could we see a schema dump (pg_dump -s) for these
> tables?
> 
> BTW the inner select seems pretty weird --- what is the point of joining
> against table_brut when you're not using it?  But that doesn't look like
> it could provoke this error.
> 
>                         regards, tom lane


the error is produced if temp2 is a view. If temp2 is a table, there is
no problem.

?

Thanks. Jerome.

Jerome Raupach <jraupach@intelcom.fr> writes:
>> So I think there must be some special feature of your tables that you
>> haven't shown us.  Could we see a schema dump (pg_dump -s) for these
>> tables?

> the error is produced if temp2 is a view.

I had suspected there might be a view involved.  But if you want this
fixed, you're going to need to be more forthcoming about providing a
complete, reproducible example.  I have other things to do than guess
what your view and table definitions are...
        regards, tom lane