I'm very new to sql so I apologize in advance for my very dummy question...

I'm looking for a way to insert into a table A some values stored in another table B only for the features of table A that intersects features of table B.

I tried this:

insert into schema.table_A(columnKJ1)
select "columnPO4" from schema.table_B
WHERE table_A.geom.STintersects(table_B.geom) = 1

but I get this error message: cross-database references are not implemented

What is the right way to do that please ?

  • TIP: Write it as a stand-alone query first. Once it's returning the records you want to insert, simply put insert into <tablename>(<columns>) before the select. Right now your select by itself doesn't work: it's trying to reference a table in your insert which isn't allowed. Your query that works stand-alone will of course need to reference table_A. – Colin 't Hart Mar 8 at 11:41
  • Also your STintersects syntax looks like that for SQL Server which won't work on Postgres! – Colin 't Hart Mar 8 at 11:46

You're not allowed to reference table_A in the insert inside the select. The query needs to work stand-alone.

The following should work (untested):

insert into schema.table_A(columnKJ1)
select "columnPO4"
from schema.table_B
join schema.table_A on st_intersects(table_a.geom, table_B.geom);

Not the answer you're looking for? Browse other questions tagged or ask your own question.