postgres error: column doesn't exist error in Postgesql 11.6

Question

I am trying to run an update command on postgresql 11.6 by below syntax

 update "YP_SUPPLIERS" set "YP_SUPPLIERS.supplierName" = "update" where "YP_SUPPLIERS.supplierID" = da68e9d0-1100-43e2-0011-db8fbe654321;

I am getting this below error

ERROR:  column "YP_SUPPLIERS.supplierID" does not exist

LINE 1: ... set "YP_SUPPLIERS.supplierName" = "update" where "YP_SUPPLI...

tired different combinations by only giving the column name , removing the quotes but nothing seems to be working.

Could any one suggest me a right way to do it.

You should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , Jul 23 '20 at 06:31

score 2 · Accepted Answer · answered Jul 23 '20 at 06:31

2

You need to quote each element separately, and the table does not need to be repeated for the target column. String constants need to be enclosed in single quotes (') in SQL. Double quotes are only for identifiers.

 update "YP_SUPPLIERS" 
     set "supplierName" = 'update' --<< single quotes for constant values
 --     ^ no table name here
 where "YP_SUPPLIERS"."supplierID" = 'da68e9d0-1100-43e2-0011-db8fbe654321';
 --    ^ schema and table name must be quoted separately

answered Jul 23 '20 at 06:31

It worked. Why is this so complicated is there any other way to do this ? – sumanth shetty Jul 23 '20 at 06:57
2

It's not complicated at all. You made it complicated when you created your tables with quoted identifiers. If you never use double quotes in SQL, you will have a lot less trouble in the long run. – Jul 23 '20 at 06:59

postgres error: column doesn't exist error in Postgesql 11.6

1 Answers1