Postgresql driver problem

Data Abstract
1

The following SQL works fine if I run it directly:
select sitename ,
coalesce(sitetown,‘’) as sitetown,
coalesce(sitepostcode,‘’) as sitepostcode,
cast (0 as integer) as status,
cast (‘’ at text) as siteindex
from e_sites
WHERE {Where}
group by sitename, sitetown, site postcode

However, DA produces the following error (I have tried this in the latest beta as well as latest release) at runtime and when validating in SM:

Validation failed: PostgreSQL Error Code: (1)

ERROR: syntax error at or near “text”
LINE 5: cast (‘’ at text) as site index

Incidentally, the only reason I’m casting those two fields is because I want to use them in a dynamic where from the client and it won’t let me if I don’t have them in the clause. Is there another way around this?

2

Hello

Which exactly PostgreSQL driver and PostgreSQL server do you use?

3

Server is 9.1.22
Driver is whatever FireDAC uses (in XE8)!

4

Is

cast (‘’ at text) as siteindex

a copy-paste error? Shouldn’t it be be AS, not AT in the clause? Ie

cast (‘’ AS text) as siteindex

5

Oh dear, that’s embarrassing. Sorry about that. I was pretty sure I’d copied it from navicat and pasted it into SM (though I think I then may have gone and edited it manually due to the other thing I logged at the same time (bugs://75905)