SQL error on PostgreSQL database on Heroku - can't run update

Question

I need to run an update query over a database I have set up on a rails site on heroku. Its a simple query however for some reason when I try to run the sql from the sql console i.e Heroku sql I get a syntax error. I don't know whats wrong with the sql it seems fine to me:

SQL> UPDATE service_types SET desc = replace(desc, ' Charge', '');
PGError: ERROR:  syntax error at or near "desc"
LINE 1: UPDATE service_types SET desc = replace(desc, ' Charge', '')...

Any ideas

John Woo · Accepted Answer · 2013-01-14 06:10:00Z

3

escape it using double quotes (the easiest way, i know there's another)

UPDATE service_types SET "desc" = replace("desc", ' Charge', '');

PostgreSQL Reserved Keywords

~~or assign an ALIAS~~

~~UPDATE service_types a SET a.desc = replace(a.desc, ' Charge', '');~~

edited Jan 14, 2013 at 6:10

answered Jan 14, 2013 at 6:02

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

user330315 Over a year ago

@JW. Renaming the column (to e.g. descr) is probably better in the long run though.

John Woo Over a year ago

@a_horse_with_no_name I definitely agrees with you. I was just assuming that he cannot alter the tables. Anyway great comment. using reserved keyword will give pain in the neck in the future. :D

Ali Over a year ago

ouch so desc is a reserved keyword here - I didnt know that - thanks for the tip! Time for some major database refactoring

user330315 Over a year ago

@Ali: It's the indicator whether you want to sort ascending or descending: select .. order by some_col desc.

Collectives™ on Stack Overflow

SQL error on PostgreSQL database on Heroku - can't run update

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related