postgresql : hstore key as a reserved keyword : any way around?

Question

Using Postgresql 9.4

I have a table with a hstore field named 'references' :

Trying to do : select * from table where (references -> 'key' = 'value') results in

ERROR: syntax error at or near "references"

As it works as expected with another fieldname, I suspect it's because it's a reserved word... but I don't feel like renaming this heavily used field in my application.

So, is there any syntax to work around this problem ?

(Look at this.)[http://stackoverflow.com/questions/7651417/escaping-keyword-like-column-names-in-postgres] — David Thomas, Jul 06 '16 at 17:39

score 0 · Answer 1 · answered Jul 06 '16 at 17:42

0

You need to enclose column names that are keywords in double quotes.

answered Jul 06 '16 at 17:42

David Thomas

778
5
10

Yep, I only tried with single quotes around the column name, but it appears that it does not mean the same thing... – marcpmichel Jul 07 '16 at 11:54
1

Single quotes create a literal text string. Double quotes create an identifier (column or table name). See quote_literal and quote_identifer functions. – David Thomas Jul 07 '16 at 16:27

score 0 · Accepted Answer · answered Jul 06 '16 at 17:45

0

So, thanks to David, the solution is :

select * from table where ( "references"->'key' = 'value' );

where the meaning of quotes and double-quotes is important.

answered Jul 06 '16 at 17:45

marcpmichel

33
3

postgresql : hstore key as a reserved keyword : any way around?

2 Answers2