From: John J. Boris, Sr. <john.boris@onlinesvc.com>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?
Date: Sun, 30 May 1999 11:31:29 -0400
Message-ID: <4.1.19990530113001.00a62ce0@bcs-main.onlinesvc.com> (raw)
In-Reply-To: <m10niEj-0000bIC@druid.net>
References: <4.1.19990528202544.00a652c0@bcs-main.onlinesvc.com>
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 <darcy@{druid|vex}.net> | 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: john.boris@onlinesvc.com
Subject: Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?
In-Reply-To: <4.1.19990530113001.00a62ce0@bcs-main.onlinesvc.com>
References: <4.1.19990528202544.00a652c0@bcs-main.onlinesvc.com> <4.1.19990530113001.00a62ce0@bcs-main.onlinesvc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox