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)"*/
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