Search Postgresql Archives

Insert or Replace or \copy (bulkload)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm seeing an obstacle in my aim to migrate from mysql to PG mainly from
the manner in which PG handles duplicate entries either from primary
keys or unique entries.

Data is taken from perl DBI into (right now) CSV based files to be used
via psql's \copy command to insert into the table.

In MySql, I was using mysqlimport --replace which essentially provided
the means to load data into the DB, while at the same time, would
provide the necessary logic to replace the entire row if there was a
duplicate instead of dying.

Under PG, I've yet to found something similar to this functionality and
searches via google has uncovered that this is one thing which _still_
has not found its way into PG. (anyone knows why? Standards?)

Anyway, I found a workaround, but, to me, even though it provides a
means to an end, it still looks like it'll end up as a maintenance
nightmare each time a table has any additional columns added.

Solution is taken from this site:

http://www.pointwriter.com/blog/index.php?/archives/6-REPLACE-in-PostgreSQL.html

[..snip..]
For the sake of this example we'll use a very simple table called 'map'
with a two fields: 'key' and 'value'. Not surprisingly, key is the
primary key.

Let's further assume that every insert into this table should actually
be a replace. Then all we need is this simple statement and we're done:

CREATE RULE "replace_map" AS
  ON INSERT TO "map_example"
  WHERE
    EXISTS(SELECT 1 FROM map_example WHERE key=NEW.key)
  DO INSTEAD
     (UPDATE map_example SET value=NEW.value WHERE key=NEW.key)

[...snip...]

Populate with some test data
==========================
XMMS=> insert into map_example(key,value,value2) values (1,1,1);
INSERT 0 1
XMMS=> insert into map_example(key,value,value2) values (2,2,2);
INSERT 0 1
XMMS=> select * from map_example
;
 key | value | value2
-----+-------+--------
   1 |     1 |      1
   2 |     2 |      2
(2 rows)

Try to insert some duplicate data
+=================================
XMMS=>insert into map_example(key,value,value2) values (2,20,20);
INSERT 0 0
XMMS=> select * from map_example
;
 key | value | value2
-----+-------+--------
   1 |     1 |      1
   2 |    20 | ====>2 <===== NOT Updated
(2 rows)

Add the new rule once we added the new columns
==============================================
XMMS=>CREATE OR REPLACE RULE "replace_map" AS
  ON INSERT TO "map_example"
  WHERE
    EXISTS(SELECT 1 FROM map_example WHERE key=NEW.key)
  DO INSTEAD
     (UPDATE map_example SET value=NEW.value,value2=NEW.value2 WHERE
key=NEW.key)
;


XMMS=> insert into map_example(key,value,value2) values (2,20,20);
INSERT 0 0
XMMS=> select * from map_example
;
 key | value | value2
-----+-------+--------
   1 |     1 |      1
   2 |    20 |     20
(2 rows)


Can anyone tell me if this won't turn out to be a maintenance nightmare?
So, the pertinent question is, is there a better mousetrap available?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux