Assign ids from a table to records of another table in PostgreSQL

by

The question:

Starting from a table datos, in a PostgreSQL database, that holds 9+ M data of bird species presence in Spain, I created a new table extracting all different locations from that table using

create table localidades as 
select distinct locality, stateProvince, decimalLatitude, decimalLongitude
from datos;

After that, I created an id column for it using

ALTER TABLE localidades ADD COLUMN id SERIAL PRIMARY KEY;

I’ve also created indexes for all these fields

CREATE INDEX idx_lugar ON datos (locality);
create index idx_prov on datos(stateprovince);
create index idx_lat on datos(decimalLatitude);
create index idx_lon on datos(decimalLongitude);
create index idx_lat1 on localidades(decimalLatitude);
create index idx_lon1 on localidades(decimalLongitude);
CREATE INDEX idx_lugar1 ON localidades (locality);
create index idx_prov1 on localidades(stateprovince);

Now I want to assing the corresponding localidades id to all registers in datos. I’ve tried the following code without success (it runs endlessly)

update datos set cod_loc = l.id 
from datos d, localidades l
where l.locality = d.locality 
    and l.stateprovince = d.stateprovince 
    and l.decimalLatitude=d.decimalLatitude 
    and l.decimalLongitude=d.decimalLongitude;

How should it be done?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You inadvertently created a cross join by specifying datos twice. Try

UPDATE datos AS d SET cod_loc = l.id 
FROM localidades AS l
WHERE l.locality = d.locality 
  AND l.stateprovince = d.stateprovince 
  AND l.decimalLatitude = d.decimalLatitude 
  AND l.decimalLongitude = d.decimalLongitude;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment