PostgreSQL query -- column does not exist

Question

I am trying to update a table using a temporary table.

  Schema   |         Name         |   Type   |  Owner   
------------+----------------------+----------+----------
 pg_temp_11 | tmp_x                | table    | postgres
 public     | entities             | table    | postgres

However I am getting this error:

UPDATE entities SET "Name" = "tmp_x.Name" FROM tmp_x WHERE "entities.Ent_ID" = "tmp_x.Ent_ID";
ERROR:  column "tmp_x.Name" does not exist -- the column Name exists
LINE 1: UPDATE entities SET "Name" = "tmp_x.Name" FROM tmp_x WHERE "...

What is the problem? The quotes around table columns?

incorrect syntax I think... update e set e.name = x.name from entities e inner join tmp_x x ON e.Ent_ID = x.Ent_ID — Deep
– Deep, Commented Nov 9, 2015 at 17:26
Stop using quoted identifiers. They are much more trouble than they are worth it. — user330315
– user330315, Commented Nov 9, 2015 at 17:51
@a_horse_with_no_name, what is the option when using table columns like Ent_ID, and not ent_id — anvd
– anvd, Commented Nov 9, 2015 at 17:52
Quite simple: if you don't quote it, you can write it any way you want ENT_ID or Ent_ID or ent_id — user330315
– user330315, Commented Nov 9, 2015 at 17:53

gmiley · Accepted Answer · 2015-11-09 17:28:27Z

5

You are surrounding multiple individual objects with double quotes. If you are using object delimiters (double quotes), they need to be on each item, not on the entire combination:

UPDATE entities SET "Name" = "tmp_x"."Name" FROM tmp_x WHERE "entities"."Ent_ID" = "tmp_x"."Ent_ID";

answered Nov 9, 2015 at 17:28

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL query -- column does not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related