Обсуждение: Query a column with the same name as an operator

Hello,

I am trying to query a column from a table I migrated from my MYSQL
into POSTGRESQL but I seem to be having issues with a few column names.

the column name cast is also an operator, I think zone may also be a
problem.

MYSQL: OK
SELECT id, start_date, local_time, country, city, region, temperature,
cast, humidity, wind, weather, zone FROM w_records WHERE city =
'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;

I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
the above does not work.

Any idea how I can run a query that accept table name that is already a
Operator?

Many Thanks 

On Fri, Nov 6, 2020 at 10:00 AM Java Developer <java@wagemaker.co.uk> wrote:
>
> Hello,
>
> I am trying to query a column from a table I migrated from my MYSQL
> into POSTGRESQL but I seem to be having issues with a few column names.
>
> the column name cast is also an operator, I think zone may also be a
> problem.
>
> MYSQL: OK
> SELECT id, start_date, local_time, country, city, region, temperature,
> cast, humidity, wind, weather, zone FROM w_records WHERE city =
> 'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;
>
> I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
> the above does not work.
>
> Any idea how I can run a query that accept table name that is already a
> Operator?

Yes, cast is a keyword in SQL (not an operator). To use it as a column
name you have to quote it, like
SELECT "cast" FROM test
(and the same when you create the table, or indeed any references to the column)

zone is not, and should be fine.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

On 06-11-2020 10:08, Magnus Hagander wrote:
> On Fri, Nov 6, 2020 at 10:00 AM Java Developer <java@wagemaker.co.uk> wrote:
>> Hello,
>>
>> I am trying to query a column from a table I migrated from my MYSQL
>> into POSTGRESQL but I seem to be having issues with a few column names.
>>
>> the column name cast is also an operator, I think zone may also be a
>> problem.
>>
>> MYSQL: OK
>> SELECT id, start_date, local_time, country, city, region, temperature,
>> cast, humidity, wind, weather, zone FROM w_records WHERE city =
>> 'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;
>>
>> I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
>> the above does not work.
>>
>> Any idea how I can run a query that accept table name that is already a
>> Operator?
> Yes, cast is a keyword in SQL (not an operator). To use it as a column
> name you have to quote it, like
> SELECT "cast" FROM test
> (and the same when you create the table, or indeed any references to the column)
>
> zone is not, and should be fine.


There is a list of keywords available in the documentation here: 
https://www.postgresql.org/docs/current/sql-keywords-appendix.html

Maybe it's of help to you.


Jurrie