pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes PostgreSQL have an UPDATE Function like UNIFY?
5+ messages / 4 participants
[nested] [flat]
* Does PostgreSQL have an UPDATE Function like UNIFY?
@ 1999-05-29 00:34 John J. Boris, Sr. <john.boris@onlinesvc.com>
1999-05-29 12:29 ` Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? D'Arcy J.M. Cain <darcy@druid.net>
1999-05-29 14:54 ` Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? Tom Lane <tgl@sss.pgh.pa.us>
1999-05-30 13:13 ` Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-30 15:31 ` Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY? John J. Boris, Sr. <john.boris@onlinesvc.com>
0 siblings, 4 replies; 5+ messages in thread
From: John J. Boris, Sr. @ 1999-05-29 00:34 UTC (permalink / raw)
To: pgsql-sql
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.
I have a table that has approximately 2500 records. The data for this table
(and other tables in the db) comes out of a system I manage that will
sometime in the future be transferred fully to this. So the data is updated
(dumped from the COBOL Indexed file) and then loaded into the table (about
once a week). I can delete the records and then add them but I think that
might be a bit time consuming.
Will I have to write a C program or Perl script to do this? I am hoping
not. One can only dream. Thanks in advance.
John J. Boris, Sr. Boris Computer Services/ONLine Services
email:john.boris@onlinesvc.com.com
bbs: The Bleeding Edge 609-858-9221
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?
1999-05-29 00:34 Does PostgreSQL have an UPDATE Function like UNIFY? John J. Boris, Sr. <john.boris@onlinesvc.com>
@ 1999-05-29 12:29 ` D'Arcy J.M. Cain <darcy@druid.net>
3 siblings, 0 replies; 5+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-05-29 12:29 UTC (permalink / raw)
To: john.boris@onlinesvc.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?
1999-05-29 00:34 Does PostgreSQL have an UPDATE Function like UNIFY? John J. Boris, Sr. <john.boris@onlinesvc.com>
@ 1999-05-29 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-05-29 14:54 UTC (permalink / raw)
To: John J. Boris, Sr. <john.boris@onlinesvc.com>; +Cc: pgsql-sql
"John J. Boris, Sr." <john.boris@onlinesvc.com> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?
1999-05-29 00:34 Does PostgreSQL have an UPDATE Function like UNIFY? John J. Boris, Sr. <john.boris@onlinesvc.com>
@ 1999-05-30 13:13 ` Herouth Maoz <herouth@oumail.openu.ac.il>
3 siblings, 0 replies; 5+ messages in thread
From: Herouth Maoz @ 1999-05-30 13:13 UTC (permalink / raw)
To: John J. Boris, Sr. <john.boris@onlinesvc.com>; pgsql-sql
At 03:34 +0300 on 29/05/1999, John J. Boris, Sr. wrote:
> I can delete the records and then add them but I think that
> might be a bit time consuming.
Frankly, I don't think it will be more time consuming. Supposing a COPY
command had to do it itself, it would have to look up each record in the
table, and replace it. So it would probably mark the old one as deleted and
insert the new record. It shouldn't be much faster than:
1) Creating a temp table with the same schema as the main one
2) copying the records into the temp table
3) deleting from the main all the records which exist in the temp
table (DELETE FROM main WHERE main.field = temp.field);
4) Inserting all records from the temp table using INSERT INTO... SELECT.
5) Dropping the temp table
6) Vacuuming
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Does PostgreSQL have an UPDATE Function like UNIFY?
1999-05-29 00:34 Does PostgreSQL have an UPDATE Function like UNIFY? John J. Boris, Sr. <john.boris@onlinesvc.com>
@ 1999-05-30 15:31 ` John J. Boris, Sr. <john.boris@onlinesvc.com>
3 siblings, 0 replies; 5+ messages in thread
From: John J. Boris, Sr. @ 1999-05-30 15:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-05-30 15:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-29 00:34 Does PostgreSQL have an UPDATE Function like UNIFY? John J. Boris, Sr. <john.boris@onlinesvc.com>
1999-05-29 12:29 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-05-29 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-30 13:13 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-30 15:31 ` John J. Boris, Sr. <john.boris@onlinesvc.com>
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