Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?

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(at){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.