Re: Problem with insert

Jerome Raupach <jraupach(at)intelcom(dot)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