CODEWITHSUNDEEP
postgresql
John Sly
John Sly

Reputation: 769

How to specify potentially reserved words as strings in Postgres query?

I've been using MySQL for close to 10 years, but have recently jumped into a project that's exposed me to Postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off.

My latest one just has me stuck. I'm sure it's a simple work-around, but I haven't been able to find it. I need to update a set of records and the column name is, "view" - which is more than likely the cause of this issue, and my own dump fault. But I can't figure out how to get around it.

Here's my query:

UPDATE rv_template_fields SET view = "display_type_1"
WHERE rv_template_fields.view = "display_type_2"

It's causing the error:

ERROR: column "display_type_1" does not exist

It's clearly jumping past the column named "view". I'm not sure how to specify that as a string and not a reserved word.

Upvotes: 1

Views: 797

Answers (2)

Bohemian
Bohemian

Reputation: 425003

It has nothing to do with view: In postgres, double quotes (") are like backticks in mysql - if you code "display_type_1", you're telling postgres to use the identifier display_type_1.

Use single quotes for string literals:

UPDATE rv_template_fields SET view = 'display_type_1' 
WHERE rv_template_fields.view = 'display_type_2'

Use double quotes when you have a (poorly named) identifier that's reserved word, like select * from "join" if your table name is literally join etc.

Upvotes: 2

Renzo
Renzo

Reputation: 27424

For string literals, you should you single quote instead of double quote:

UPDATE rv_template_fields SET view = 'display_type_1' WHERE rv_template_fields.view = 'display_type_2'

Double quotes are for quoting identifiers of fields and relations, like, for instance view, so that you could write also:

UPDATE rv_template_fields SET "view" = 'display_type_1' WHERE "view" = 'display_type_2'

Upvotes: 2

Related Questions