1

So I have two different schemas namely bikeshare and spatial having two tables bs and docks respectively. Also they have two common fields on basis of which i want to apply join

This query works perfectly.

   SELECT * FROM bikeshare.bs
   INNER JOIN spatial.docks
   ON bikeshare.bs.start_dock_code = spatial.docks.id;

Now what i want to do is to update a column neighbourhood_code in table bs which is already in the second table docks by name asumi_nr

Here what I have done so far

UPDATE bikeshare.bs SET 
neighbourhood_code = spatial.docks.asumi_nr 
FROM spatial.docks
WHERE bikeshare.bs.start_dock_code = spatial.docks.id;

P.S: Now the problem is This query does not give any error and it keeps on saying waiting for query to complete (left it for more than 30 min but no result). at least it should give me some error so that i may explore what i am doing wrong.

Thanking in advance for your help

EDIT: So i figured it out by reinstalling the database and the query worked fine. but still dont know what was the actual reason but if anyone else gets stuck in such situation a novice solution is to reinstall database (maybe it will work for you as well)

SpatialAnalyst
  • 37
  • 5
  • Looks fine to me. How many rows are there in your tables? What indices do you have set up on these tables? – Bergi Aug 09 '20 at 12:54
  • only 551550 rows. applying all other queries it works fine but it gets stuck on this particular query. even left it for more then half an hour that maybe it would work but nothing. can you suggest any work around? and the gids in both tables are primary keys – SpatialAnalyst Aug 09 '20 at 12:56
  • Can you show us the `EXPLAIN` of the query maybe? – Bergi Aug 09 '20 at 13:08
  • Why would it throw an error if there is no error? Sounds like it is just doing a lot of work. – jjanes Aug 09 '20 at 13:18
  • I am bit new to postgres and databases, may be you mean the Explain statement on query... It says some of this stuff (i will explore more but at the moment its alien to me, what its saying) "Update on bs (cost=3.55..21526.17 rows=218806 width=116)" " -> Hash Join (cost=3.55..21526.17 rows=218806 width=116)" " Hash Cond: (bs.start_dock_code = docks.id)" " -> Seq Scan on bs (cost=0.00..16409.21 rows=634221 width=102)" " -> Hash (cost=2.69..2.69 rows=69 width=18)" " -> Seq Scan on docks (cost=0.00..2.69 rows=69 width=18)" – SpatialAnalyst Aug 09 '20 at 13:22

0 Answers0