Escaping characters in column names in a PostgreSQL query?

Question

I have a query that I am using that works fine in MySQL but having problems with it working on Postgres. I'm getting a syntax error that I am sure has a simple solution but can't find anything on here about it.

It's pretty clear that the problem has to do with the digit 1 in the first case when.

SELECT *, ( (case when 1stFarmers > 0 THEN 1 ELSE 0 END) + (case when OldMcDonald > 0 THEN 1 ELSE 0 END) + (case when NewMcDonald > 0 THEN 1 ELSE 0 END) ) as num_fruits FROM (SELECT fruit, sum(farmers), sum(case when farmer = '1stFarmers' then harvest_count else 0 end) AS 1stFarmers, sum(case when farmer = 'OldMcDonald' then harvest_count else 0 end) AS Old_McDonald, sum(case when farmer = 'NewMcDonald' then harvest_count else 0 end) AS New_McDonald FROM fruits GROUP BY farmer) ft

The error I am getting back:

syntax error at or near "stFarmers" Position: 27 ( (case when 1stFarmers > 0 THEN 1 ELSE 0 END) + ^ (case when OldMcDonald > 0 THEN 1 ELSE 0 END) +

souslov · Accepted Answer · 2015-10-25 00:03:22Z

4

Use double quotes " to escape identifiers (in your case, column names) that contain numbers:

(case when "1stFarmers" > 0 THEN 1 ELSE 0 END) +

answered Oct 25, 2015 at 0:03

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

1 Comment

user330315 Over a year ago

@icm: here is the relevant link to the manual: postgresql.org/docs/current/static/… it always pays of to read it instead of blindly trying stuff ;)

Collectives™ on Stack Overflow

Escaping characters in column names in a PostgreSQL query?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related