Postgres Pro
Downloads
Home   >   mailing lists

Re: Help with slow table update - Mailing list pgsql-general

From Igor Neyman
Subject Re: Help with slow table update
Date
Msg-id A76B25F2823E954C9E45E32FA49D70ECCD415423@mail.corp.perceptron.com
Whole thread Raw
In response to Re: Help with slow table update  (Pawel Veselov <pawel.veselov@gmail.com>)
Responses Re: Help with slow table update  (Pawel Veselov <pawel.veselov@gmail.com>)
List pgsql-general
Tree view

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Pawel Veselov
Sent: Tuesday, April 14, 2015 8:01 PM
To: Jim Nasby
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help with slow table update

 

[skipped]

 

This is where using sets becomes really tedious, as Postgres severely lacks an upsert-like statement.

I don't think there are joins allowed in UPDATE statement, so I will need to use WITH query, right?

Also, I'm not sure how LEFT JOIN will help me isolate and insert "missed" entries...

 

Would it be OK to replace upsert part with merging into a temp table, then deleting and inserting from temp table? Is there any penalty for insert/delete comparing to update?

 

[skipped]

 

Yes, you can do UPDATE with joins (http://www.postgresql.org/docs/9.4/static/sql-update.html) like this:

 

UPDATE table1 A SET col1 = B.col2

  FROM table2 B

  WHERE A.col3 = B.col4;

 

Regards,

Igor Neyman

pgsql-general by date:

Previous
From: Adam Hooper
Date:
Subject: Re: How to keep pg_largeobject from growing endlessly
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: How to keep pg_largeobject from growing endlessly