Обсуждение: 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