0

I have two tables:

"TABLE1"

"Id" integer,
"unit" varchar(255)
...

"TABLE2"

"Id" integer,
"Description" varchar(250)
...

TABLE1 is a descendant of TABLE2 - in Postgres parlance, it "inherits" from TABLE2.

TABLE2 has rows with a matching "Id" for every row in TABLE1. It also has many other descendants and there are many more rows in TABLE2 than in TABLE1.

For reasons not pertinent here, I need to copy the text from TABLE1."unit" into TABLE2."Description" for all rows where the Id column is the same.

The following query succeeds, but doesn't update any rows:

UPDATE  ONLY public."TABLE2" as t2
SET    ("Description") = (t1."unit"::regclass)
FROM   "TABLE1" as t1
WHERE  t1."Id" = t2."Id";

I tried the query without ONLY, but it failed with a "name" error.

Can someone please tell me what I'm doing wrong?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
gbesch
  • 1
  • 1
  • 1
  • 1
    You are mixing double-quoted identifiers and unquoted ones, but this has relevance in Postgres. What is a `"name" error`? Please provide the verbatim error message. ***[Edit](http://stackoverflow.com/posts/32380510/edit)*** your question to provide a *complete and verbatim* table definition for `"TABLE1"` and `"TABLE2"` (what you get in psql with `\d tbl`) to give us a fighting chance. Add any triggers (and the used trigger function), rules or indexes there may be. And *always* your version of Postgres. – Erwin Brandstetter Sep 03 '15 at 22:31

2 Answers2

1
UPDATE ONLY public."TABLE2" t2
SET    "Description" = t1."unit" -- ::regclass  -- ???
FROM   public."TABLE1" t1
WHERE  t1."Id" = t2."Id";

Are you sure that "TABLE1" inherits from "TABLE2"? The names would indicate the opposite.

Why would you cast to regclass when the target is varchar as well? Do you understand the data type regclass?

A cast to regclass fails with an exception if the string is not a valid table (or related) object. In Postgres 9.4 or later you might use the function to_regclass() instead that returns NULL for illegal identifiers:

I removed the cast.

Why do you use varchar(255) for "Table1".unit and varchar(250) for "Table2"."Description"? Why use varchar(255) to begin with? It typically indicates that the Postgres data type varchar has not been understood:

Why would you schema-qualify public."TABLE2", but not "TABLE1" in your query?
Why do you use those tedious double-quoted mixed-case identifiers to begin with?

The error code P0001 you mentioned in your comment is a PL/pgSQL error. The documentation:

Class P0 — PL/pgSQL Error
P0000   plpgsql_error
P0001   raise_exception

There might be one or more trigger functions on "TABLE2", which you forgot to mention.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
-1

This should do the trick: 

UPDATE  t2
SET    "Description" = t1."unit"
FROM   "TABLE1" as t1
JOIN   "TABLE2" AS t2 ON t1."Id" = t2."Id";

If there is a chance that the unit field will reach 255, you may get a truncation error on update to table 2.

In this case you can review your field to find out the offending row:

SELECT t1."unit", LEN(t1."unit")
FROM   "TABLE1" as t1
WHERE LEN(t1."unit") > 250

AND/OR Update the field, taking the left 250 characters

UPDATE  t2
SET    "Description" = LEFT(t1."unit", 250)
FROM   "TABLE1" as t1
JOIN   "TABLE2" AS t2 ON t1."Id" = t2."Id";
rink.attendant.6
  • 44,500
  • 61
  • 101
  • 156
Adam
  • 1
  • 1
  • Thanks for the quick answer! Unfortunately, I still don't have a working solution -- the query ran for over an hour, then crapped out with an error: ERROR: CM_FORBIDDEN_OPERATION ********** Error ********** ERROR: CM_FORBIDDEN_OPERATION SQL state: P0001 – gbesch Sep 03 '15 at 17:17
  • You completely ignore the mentioned *inheritance*. What's more, the suggested `UPDATE` statements are plain invalid in Postgres. [Please consult the manual for basics before you write more misleading answers.](http://www.postgresql.org/docs/current/interactive/sql-update.html) – Erwin Brandstetter Sep 03 '15 at 22:24