Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA32402 for ; Sat, 29 May 1999 10:55:32 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA18076; Sat, 29 May 1999 10:54:40 -0400 (EDT) To: "John J. Boris, Sr." cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? In-reply-to: Your message of Fri, 28 May 1999 20:34:33 -0400 <4.1.19990528202544.00a652c0@bcs-main.onlinesvc.com> Date: Sat, 29 May 1999 10:54:39 -0400 Message-ID: <18074.927989679@sss.pgh.pa.us> From: Tom Lane "John J. Boris, Sr." writes: > I was wondering if PostgreSQL had a function that allowed you to update a > table from a delimited file that would update matching records and insert > new records. Under Unify you could do an update with a switch (I think it > was -n) that would take input from a pipe delimited file (in the order the > fields of the table) and since the table had which field was unique it > would key on that and if it didn't find the key it would add the > record. Not directly, but I think you could solve it with a temporary table and two or three SQL statements: CREATE TABLE temp (just like the master) COPY temp FROM delimited file UPDATE master SET field1 = temp.field1, etc WHERE master.key = temp.key INSERT INTO master SELECT * FROM temp WHERE NOT EXISTS (SELECT * FROM master WHERE key = temp.key) There's probably a cleaner/more efficient way to do the last step... but as long as you have an index on the key field it shouldn't be too bad. regards, tom lane