Received: from druid.net (root@druid.net [198.96.119.226]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA19619 for ; Sat, 29 May 1999 08:29:40 -0400 (EDT) (envelope-from darcy@druid.net) Received: from localhost (2387 bytes) by druid.net via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp (sender: ) (ident using unix) id for ; Sat, 29 May 1999 08:29:13 -0400 (EDT) (Smail-3.2.0.104 1998-Nov-20 #4 built 1999-Apr-13) Message-Id: Subject: Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? In-Reply-To: <4.1.19990528202544.00a652c0@bcs-main.onlinesvc.com> from "John J. Boris, Sr." at "May 28, 1999 08:34:33 pm" To: john.boris@onlinesvc.com (John J. Boris Sr.) Date: Sat, 29 May 1999 08:29:13 -0400 (EDT) From: "D'Arcy" "J.M." Cain Cc: pgsql-sql@postgreSQL.org Reply-To: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL50 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Thus spake John J. Boris, Sr. > 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. How about a python solution? (untested) #! /usr/bin/env python from pg import DB # http://www.druid.net/pygresql/ db = DB() # or specify other than the default database for l in open('path_to_input_filename').readlines(): dict = convert_line_to_dictionary(l) # this could be automated somewhat try: db.insert(dict) # fails if duplicate except(db.update(dict) # do this if insert fails The insert and update can be tried in the reverse order if you expect more lines in the input to be updating existing records than new records. The convert_line_to_dictionary() function could be automated somewhat but I really hate depending on the order of fields. However, if I know the order of fields in the input file (easier to guarantee) then I woould do something like this. l = string.split(l, '|') dict = { 'field0': l[0], 'field1': l[1], 'field2': l[2] } There is also a module to handle comma delimited, quoted strings files. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.