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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply