Problem with insert

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

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