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



  • 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?



  • 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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2