From: | "hjenkins" <hjenkins(at)uvic(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | combining semi-duplicate rows |
Date: | 2007-12-18 19:44:26 |
Message-ID: | 2960.142.104.193.193.1198007066.squirrel@wm3.uvic.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have a table of rows which partially duplicate one another and need to
be merged and moved into a table with a primary key.
As an additional complication, some of the duplicates contain different
information, ex.:
schema1.datatable: key1 None None 3 4
schema2.datatable: key1 1 2 7 None
desired result:
schema1.datatable: key1 1 2 3 4
I looked for a specific function that would do this sort of merge and
failed. So I tried:
UPDATE schema1.datatable SET schema1.datatable.field1 =
schema2.datatable.field1 FROM schema2.datatable
WHERE schema2.datatable.keyfield = schema1.datatable.keyfield
AND schema1.datatable.field1 = None;
Which is suboptimal because I'd need a command for each field, but it
would be a start. However, the schema names are not recognised. I get
error messages to the effect that "cross-database references are not
implemented" or "relation "schema1/2" does not exist.Even the much simpler
SELECT DISTINCT schema2.datatable INTO schema1.datatable;
...gives me these messages. Qualifying right up to the database level
produces "improper qualified name (too many dotted names)".
I'm pretty sure that this isn't a capitalization/quoting problem as
described in the FAQ. Is it not possible to use these functions between
schemas? Or am I misusing the functions in a more basic way?
The problem is somewhat similar to this one:
http://archives.postgresql.org/pgsql-sql/2007-02/msg00055.php
Namely,
Regards,
H.Jenkins
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Harrison | 2007-12-18 19:52:54 | multiple version installation in the same machine ???? |
Previous Message | Weber, Geoffrey M. | 2007-12-18 19:17:54 | Re: Problem with index not being chosen inside PL/PgSQL function... |