Skip site navigation (1) Skip section navigation (2)

Re: Postgres restore help

From: Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>
To: Piyush Lenka <lenka(dot)piyush(at)gmail(dot)com>
Cc: Alessandro Gagliardi <alessandro(at)path(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Postgres restore help
Date: 2012-02-28 20:13:11
Message-ID: CAD8_UcZA9YpW6sHbth+vVt9GVYiLzd76cxE6+s-3G0ztE9H0BA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>>
>
>

In response to

Responses

pgsql-novice by date

Next:From: Alessandro GagliardiDate: 2012-02-28 21:34:54
Subject: Re: Postgres restore help
Previous:From: Alessandro GagliardiDate: 2012-02-28 18:30:19
Subject: Re: Postgres restore help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group