2

I'm trying to use a property that has a .(d0t) in the the propery name, but it seems like postgresql is confused and thinks that i'm trying to access a table property.

Here is the sql query i'm trying to run

select count(metadata.username), metadata.username from appointment join appointment_wrapper a on a.id=appointment_wrapper_id where property='state' and string_value='Kano' and timestamp > '2014-08-01' and timestamp < '2014-11-01' group by metadata.username;

When i run that query it says 

ERROR:  missing FROM-clause entry for table "metadata"

When i add quotes it says

ERROR:  non-integer constant in GROUP BY

Any help on how to solve this?

Thank you

diokey
  • 176
  • 1
  • 2
  • 12
  • Just to make sure, you quoted the column name in both select and group by clause? – mabi Oct 30 '14 at 16:08
  • I tried to using back quotes by it yields the same error as without quotes select count(`metadata.username`), `metadata.username` from appointment join appointment_wrapper a on a.id=appointment_wrapper_id where property='state' and string_value='Kano' and timestamp > '2014-08-01' and timestamp < '2014-11-01' group by `metadata.username`; – diokey Oct 30 '14 at 16:17
  • I think back quotes are just for MySQL and double quotes work for Postgres. See @mabi 's answer. – Alvin Thompson Oct 30 '14 at 16:25

1 Answers1

4

What you're looking for is a "quoted identifier" that you obtain by enclosing your column name in doublequotes to prevent it from being interpreted as a keyword:

SELECT count("metadata.username") from app group by "metadata.username";

Just a reminder that you really, really want to avoid the confusion that having reserved characters in your column or table names bring.

Community
  • 1
  • 1
mabi
  • 5,279
  • 2
  • 43
  • 78