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(at)eranet(dot)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(at)path(dot)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(dot)piyush(at)gmail(dot)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
>>>
>>
>>
>