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

Question

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.

have you tried single quotes?

Coding Orange
– Coding Orange

2015-06-22 15:28:46 +00:00
Commented Jun 22, 2015 at 15:28 — Coding Orange
– Coding Orange, Commented Jun 22, 2015 at 15:28

Renzo · Accepted Answer · 2015-06-22 15:30:39Z

2

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'

answered Jun 22, 2015 at 15:30

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

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

Comments

Bohemian · Accepted Answer · 2015-06-22 15:36:12Z

2

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.

edited Jun 22, 2015 at 15:36

answered Jun 22, 2015 at 15:29

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related