Received: from onlinesvc.com (lv3-196-65.dvnc.net [209.246.196.65] (may be forged)) by hub.org (8.9.3/8.9.3) with SMTP id LAA61522 for ; Sun, 30 May 1999 11:30:29 -0400 (EDT) (envelope-from john.boris@onlinesvc.com) Received: from lv3-196-68.dvnc.net ([209.246.196.68]) by onlinesvc.com (wcSMTP [445]) with SMTP id 143560058; Sun, 30 May 1999 15:41:01 GMT Message-Id: <4.1.19990530113001.00a62ce0@bcs-main.onlinesvc.com> X-Sender: john.boris@bcs-main.onlinesvc.com X-Mailer: QUALCOMM Windows Eudora Pro Version 4.1 Date: Sun, 30 May 1999 11:31:29 -0400 To: pgsql-sql@postgreSQL.org From: "John J. Boris, Sr." Subject: Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? In-Reply-To: References: <4.1.19990528202544.00a652c0@bcs-main.onlinesvc.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" D'Arcy Thanks for the quick reply. I'll look into that. I don't think I had that interface compiled when I installed PostgreSQL so I'll check. Thanks again. At 08:29 AM 5/29/99 -0400, you wrote: >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. John J. Boris, Sr. Boris Computer Services/ONLine Services email:john.boris@onlinesvc.com.com bbs: The Bleeding Edge 609-858-9221