I'm using Postgresql 9.1.15
It didn't succeed, which is why the message is confusing. The table was not created. It runs for about 10 minutes and then this error comes up.
I tried reindexing the tables but that didn't help.
This is the query:
create table lu_addresses as
SELECT
l.start_number,
s.street,
s.locality,
b.postcode,
case when o.organisation != '' then o.organisation || ', ' else '' end as organisation,
b.the_geom,
b.status,
FROM
buildings AS b,
streets AS s,
land AS l full outer join orgnisation AS o on (
l.id =
o.id)
I ended up cutting the query right down to basics and found the culprit to be the the_geom field which is a geometry datatype.
I had 8GB of memory and my postgres config set to:
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
After running pgtune, specifying I had 7GB memory, I changed the config to:
shared_buffers = 1920MB
effective_cache_size = 5632MB
work_mem = 48MB
maintenance_work_mem = 480MB
I've rerun the query and now I no longer get the error.