Re: Postgres restore help

Argh! I can't believe I forgot the "LEFT". That's embarrassing. Anyway, I was wondering if a WHERE NOT EXISTS clause would be better. I'm still new to those, so didn't want to try to offer that as a solution, but I gather it can be more efficient than a JOIN in some cases.

On Tue, Feb 28, 2012 at 12:13 PM, Bartosz Dmytrak <bdmytrak@eranet.pl> wrote:

This is almost perfect :)

but won't work in that case in the way You expect, because of join type. This should be LEFT JOIN to select all from temp_table (left) and matching from main_table (right), then WHERE clausule will  filter rows not existing in main_table. Without USING, which is shorthand (http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-FROM) it should look like this:

INSERT INTO main_table 

SELECT temp_table.* 

FROM temp_table 

      LEFT JOIN main_table ON (temp_table.pk = main_table.pk) /* instead of "ON (...)" you can use Alessandro's shorthand: "USING (pk)"*/

WHERE main_table.pk IS NULL;

Regards,

Bartek

2012/2/28 Alessandro Gagliardi <alessandro@path.com>

I would recommend restoring the table to a temporary table and then using something like:

INSERT INTO main_table SELECT temp_table.* FROM temp_table JOIN main_table USING (pk) WHERE main_table.pk IS NULL

Someone else here might have a more efficient way of phrasing that insert statement (I'm still fairly new to the concept of anti-joins). But I think something like this should work for you.

-Alessandro

On Tue, Feb 28, 2012 at 7:34 AM, Piyush Lenka <lenka.piyush@gmail.com> wrote:

Hi,

I m trying to restore only data from a backup file.but i hav some primary key that already exists in the table.

I want to restore non duplicate data only.

Please Help.

Thanks And Regards 

Piyush